Excel and tips

Here are step-by-step guides to solve some common problems in Excel. I find them especially important because I rarely need to apply these tasks, but the instructions are all in one place when I need them.

Excel step-by-step for more basic tasks

These step-by-step instructions were originally written for Microsoft Excel 2011 for Mac, but many of the same steps apply to other versions, years later.

1. How to print Excel documents to fit on a page

Open the "file" menu on the top left
Select "page setup" from the menu
Select the "fit to" radio button
To fit width of spreadsheet to one page but print as many pages as necessary
To fit entire spreadsheet on one page

Then either print or click preview if you would like to see how the spreadsheet will fit on a page.
You can also make PDFs this way, from the "print" option under the "file" menu

2. How to repeat column headings on multiple printed pages

Open the "file" menu on the top left

Select "page setup" from the menu
Select "sheet" tab
In the "rows to repeat at top field," type in the range, such as a:1:d1 if the headings are in row1, cells a through d.

Then either print or click preview if you would like to see how the spreadsheet will fit on a page.

3. How to insert a column or a row

For a column
- Click the letter at the top of the column

Select "insert" from the menu bar
Select "cells" from the insert menu

For a row

Click the number at the left of the row, and repeat the same process as above, select "row" instead from the insert menu options

4. How to adjust column widths

Multiple options

Place your cursor over the vertical line between two columns (between the letters A & B, for example), click and hold down your mouse, and then drag it left or right.
Double click the vertical line between the two columns in the header row (between the letters A & B, for example). This will automatically make the column to the left as wide as necessary to see all data.
To adjust width of all columns at once: Click the box in the upper left-hand corner (above the "1" and to the left of the "A") to select all columns, then double click any vertical line between columns in the header row. This will make all columns in the spreadsheet as wide as necessary to see all data.

5. How to sort data alphabetically or numerically

Place the cursor in the blank box above the "1" and to the left of the "A" in the upper left corner of your spreadsheet screen, and click once. This will shade in the entire spreadsheet. This prevents you from sorting just one column by accident.
Select "data" from the menu bar at the top of the screen
Select "sort from the data menu
Look in the top right and make sure the box is checked for "my list has headers," or uncheck if your selected material has no headers
In the column area, pick the column you would like to sort by, and then "order" on the the same screen
Click OK
If you would like to sort by multiple columns (for example if you want to first sort by a state column and then by a city column) you can do so by using the plus option to add a second sorting criteria. You can also add a third, fourth, etc.

6. How to use formulas for math (add, average, median, etc.)

Place your cursor in the cell where you want the answer to appear
To average, type: =average(a2:a5) and hit return
To determine the median value, type in: =median(a2:a5) and hit return
To determine the low number in the list, type: =min(a2:a5) and hit return
To determine the high number in the list, type: =max(a2:a5) and and hit return

The result will change each time you change a number in cells a2, a3, a4 or a5. So if you want to move your result to another area of your spreadsheet, first execute a "paste special" command.

Alternate method to sum a total: Click the Sigma sign in the tool bar and Excel will guess what you want to sum. Caution: Make sure it is adding the section you want. It will automatically go up or across to the next blank cell.

More: There are dozens of formula functions, including rank, extracting the month, year and day from a date field, and counting blanks or non-blank cells. There are three options to guide you through this. Click the "formulas" ribbon. Once you do that you'll see options at the left to insert a formula.
Insert fx, gives you dozens of formula options to use.
AutoSum offers some commonly used formula to quickly insert
Formula Builder walks you through how to complete a formula. Some are complex, so this can help.

7. How to change styles (decimals, dates & more)

Select area for which you want to change the style

Put your cursor in a cell to change just that box
Click on the column letter or the row number to change the style for an entire column or row.
Or Click on the top left box above the "1" and to the left of the "A" to change the style for the entire spreadsheet.

Select "format" from menu bar
Select "cells" from format menu, and make a choice. Here are some of your options:

To change decimal format: Select "number," then select number of decimals desired, and click OK.
To change date format: Select "date" and then choose date format desired from the list, and click OK.
To display numbers as percents: Select "percentage," chose the number of decimals desired, and click OK.

8. How to freeze top row/left column to stay visible as you scroll

If you want your header row to remain visible at all times

Place your cursor in cell A2 (or all the way to the left and immediately below the row you want to remain visible)
Select "window" from the menu bar
Select "freeze panes" from the window menu

If you want both the header row and the left most column to remain visible at all times

Place your cursor in cell B2 (or just below and to the right of the information you want to remain visible)
Select "window" from the menu bar
Select "freeze panes" from the window menu

When using this command, everything will remain visible above and to the left of where you have the cursor when you execute the "freeze" command.
To unfreeze the frames, or change to section you want frozen, return to the same menu and select "unfreeze panes." It's basically an on/off switch.

9. How to split info into multiple columns such last & first names

Insert one or more columns as needed immediately to the right of the column you plan to split

For this first step, you can click the letter in the header of the column to the right of your column being split and select insert cells from the menu bar

Click the letter in the header column of the column you intend to split
Select "data" from the menu bar
Select "text to columns" from the data menu

Select "delimited" and hit next
Select the proper delimiter for your case
If there is a space between the first and last name, select space, and hit finish

If there is a comma, select comma, and then hit finish

10. How to combine multiple columns into one

Put your cursor where you want the new column to appear (inserting a new column if necessary)

Type =concatenate(
Then type the columns or information you want to create, such as:

=concatenate(a1,b1,c1)

This will combine the information in cells a1, b1 and c1 into a single cell

=concatenate(a1, " ", b1)

This will combine column a1 and b1, and add a space between the two

=concatenate(a1, " percent")

This will take one is in a1 and add the word percent after it

After you have created the proper formula in the first cell at the top of your spreadsheet, you can repeat the formula throughout your spreadsheet by following these steps

Select the top cell in your column with the formula
Move your cursor over the bottom right corner of that cell until the large, hollow plus sign changes to a small, solid plus sign
Double-click the bottom right corner that that point and the formula will automatically repeat itself going downward for as long as there is information the column to the immediate left

11. How to to change formulas to text or numbers permanently

Sometimes it is necessary to replace cell formulas and permanently save the result of the formulas in the spreadsheet. Here's how to do that:

Select a cell(s), an entire column or row, or the entire spreadsheet.
Copy the selection It's OK to select and copy an entire document -- even though some cells won't have formulas -- as long as you want to lose all formulas)
Select "edit" from the menu bar
Select "paste special" from the edit menu
Select "values" and click OK

12. How to change columns heading to rows (transpose layout)

To eliminate the formulas for a certain area of the spreadsheet:

Select only the contents of the spreadsheet you want to change
Copy the selection
Place your cursor where you want to execute the paste
Select "edit" from the menu bar
Select "paste special" from the edit menu
Select "transpose" and click OK

If you want to eliminate all formulas on your spreadsheet, select the entire spreadsheet and repeat the steps listed above.

13. How to wrap text so long strings fit to multiple lines in a cell

Select the cell(s), rows or columns you want to "wrap"

Select "format" from the menu bar
Select "cells" from the format menu
Select the "alignment" tab
Select the "wrap text" box and click OK

If necessary, adjust the width and height of the results by using the method explained under "how to adjust column widths"

14. How to use the filter to sort large amounts of data

Place your cursor somewhere in the top row
Select "data" from the menu bar
Select "filter" from the data menu
Pick "auto filter"
The result will be arrows next to each piece of information in Row 1

Or try some customized sorts by selecting "custom" after clicking the area
You can do multiple filters at once. For example, if you have columns for states, cities and population, you could first filter the list down to only Ohio, then you could filter of greater than 100,000 people in Ohio.

Or revert to the complete list again by selecting all after clicking the area

News Numbers - Rich Exner

Links