Microsoft Excel 2011 instructions and tips


These step-by-step instructions are geared to Microsoft Excel 2011 for Mac, but many of the same steps apply to other versions. Use this link for interactions for the older Excel 97.

Also, in addition to the basic instructions on this page, find advance Excel tips with these links

Excel step-by-step basic instructions

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

  1. Open the “file” menu on the top left
  2. Select “page setup” from the menu
  3. Select the “fit to” radio button
  4. To fit width of spreadsheet to one page but print as many pages as necessary
      • Choose “1″ page wide and make “blank” the page tall window.
  5. To fit entire spreadsheet on one page
      • Choose “1″ page wide and “1″ page tall
  • 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

  1. Open the “file” menu on the top left
    1. Select “page setup” from the menu
    2. Select “sheet” tab
    3. 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.
  2. 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

  1. For a column
    • Click the letter at the top of the column
      • Select “insert” from the menu bar
      • Select “cells” from the insert menu
  2. 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

  1. 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.
  2. 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.
  3. 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

  1. 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.
  2. Select “data” from the menu bar at the top of the screen
  3. Select “sort from the data menu
  4. 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
  5. In the column area, pick the column you would like to sort by, and then “order” on the the same screen
  6. Click OK
  7. 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.)

    1. Place your cursor in the cell where you want the answer to appear
        1. To add, type: =sum(a2:a5)
            1. This command will sum everything from a2 to a5, or a2, a3, a4 and a5. If you want to add only a2 and a5, do it like this: =sum(a2,a5) or =sum(a2+a5)
        2. Hit return to produce your result.
    2. To average, type: =average(a2:a5) and hit return
    3. To determine the median value, type in: =median(a2:a5) and hit return
    4. To determine the low number in the list, type: =min(a2:a5) and hit return
    5. 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)

  1. 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.
  2. Select “format” from menu bar
  3. Select “cells” from format menu, and make a choice. Here are some of your options:
    1. To change decimal format: Select “number,” then select number of decimals desired, and click OK.
    2. To change date format: Select “date” and then choose date format desired from the list, and click OK.
    3. 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

  1. If you want your header row to remain visible at all times
    1. Place your cursor in cell A2 (or all the way to the left and immediately below the row you want to remain visible)
    2. Select “window” from the menu bar
    3. Select “freeze panes” from the window menu
  2. If you want both the header row and the left most column to remain visible at all times
    1. Place your cursor in cell B2 (or just below and to the right of the information you want to remain visible)
    2. Select “window” from the menu bar
    3. Select “freeze panes” from the window menu
  3. 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.
  4. 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

  1. 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
  2. Click the letter in the header column of the column you intend to split
  3. Select “data” from the menu bar
  4. Select “text to columns” from the data menu
    1. Select “delimited” and hit next
    2. Select the proper delimiter for your case
    3. If there is a space between the first and last name, select space, and hit finish
  5. If there is a comma, select comma, and then hit finish

10. How to combine multiple columns into one

  1. Put your cursor where you want the new column to appear (inserting a new column if necessary)
    1. Type =concatenate(
    2. 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
  2. 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:

  1. Select a cell(s), an entire column or row, or the entire spreadsheet.
  2. 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)
  3. Select “edit” from the menu bar
  4. Select “paste special” from the edit menu
  5. Select “values” and click OK

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

  1. 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
  2. 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

  1. 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
  2. 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

  1. Place your cursor somewhere in the top row
  2. Select “data” from the menu bar
  3. Select “filter” from the data menu
  4. Pick “auto filter”
  5. The result will be arrows next to each piece of information in Row 1
      • Click any arrow to get a list of choices and pick what you want to see
      • 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.
  6. Or revert to the complete list again by selecting all after clicking the area

Print Friendly