Excel tips and tricks

By Beacon News | Published January 6, 2011 | Categories: Web Development

In another life, I was a Microsoft Office trainer and I found that quick and simple Excel tips were always a big hit in my classes, even with the Excel "experts".  So here are a few Excel time-savers-- enjoy!  BTW, I'm using Excel 2007, so though these features are (for the most-part) still available in other versions, the menu options may be different...

• Freeze panes-- To "freeze" the top row of your spreadsheet so that the titles don't scroll of the page when you move down
  1. Select the row below the row or rows that you want to keep visible when you scroll
  2. On the View tab, in the Window group, click the arrow below Freeze Panes
  3. Select "Freeze Panes"
• Open workbooks in different windows-- Sometimes I need to open more than one workbook to compare the data.  This is particularly nice when you have dual monitors.  Unlike Word though, which opens a new window for every document, Excel opens every workbook in the same instance of Excel, so you can't easily put them side by side.  Though you can use View, "Arrange All", this is not my preference, because it squishes all the workbooks onto one small monitor.  The best solution I have found is to open Excel from the Programs menu more than once, arrange each instance in a different monitor and then use the "Open" menu in each instance to load the workbooks.
• Filter-- I use filters all the time!  There's just nothing better for locating a rogue piece of data or display just a subset of a list (just items assigned to one person in a task list, for example).  Filters can be used in combination with each other, so each additional filter added is based on the current filter.  To filter a table:
  1. Make sure that the active cell is in a table column that contains alphanumeric data
  2. On the Data tab, in the Sort & Filter group, click Filter
  3. Click the arrow in the column header
  4. Select or clear one or more values in the drop down box to filter by OR use the "Custom Filter" option, which allows you to filter by things like "Begins With" or "Contains"
  5. NOTE:  A cool new Excel feature is the ability to filter by cell color or font color, so you can also filter to show just the cells that have been formatted!  Select "Filter by Color" after clicking on the arrow for this feature.
• Format painter--The format painter is available in all Office products, but I particularly like it in Excel, due to the need to keep data consistently formatted.  The Format Painter copies the current cell's formatting (fills, borders, font, etc.) to a different cell or group of cells. To use the Format Painter:
  1. Select the cell that has the formatting that you want to copy
  2. On the Home tab, in the Clipboard group, click the Format Painter icon.  NOTE:  If you will only be formatting one cell, click the icon once.  To copy the format to multiple cells, double-click the Format Painter and the tool will remain active until clicked again
  3. Click the cell(s) that you want to format
  4. To stop formatting multiple cells, press ESC or click the Format Painter icon again.
• Conditional formatting-- When conditional formatting is applied to a cell, its appearance changes based on a condition or criteria.  There are many different ways to apply conditional formatting, but I find the most flexible is using a formula to determine which cells to format:
  1. On the Home tab, in the Styles group, click the arrow next to Conditional Formatting, and then click Manage Rules
  2. Click New Rule
  3. Under Apply Rule To, select whether to apply the rule to "Just these cells" or "All cells with the same fields" or "All cells"
  4. Under "Select a Rule Type", click "Use a formula to determine which cells to format"
  5. Under "Edit the Rule Description", in the "Format values where this formula is true", enter a formula
  6. Click Format to display the "Format Cells" dialog box
  7. Select the number, font, border or fill format that you want to apply when the cell value meets the condition and then click OK.
• Automatically number rows-- To fill a column with a series of numbers, the "fill handle" (black box in the bottom right corner of the selected cell; when you point to the fill handle, the pointer changes to a black cross) can be a quick and easy tool:
  1. Select the first cell in the range that you want to fill.
  2. Type the starting value for the series. Then type a value in the next cell to establish a pattern.  For example, if you want the series 1, 2, 3, 4, 5..., type 1 and 2 in the first two cells. If you want the series 2, 4, 6, 8..., type 2 and 4.
  3. Select the cells that contain the starting values.
  4. Drag the fill handle across the range that you want to fill.   Note:  As you drag the fill handle across each cell, Excel displays a preview of the value that it will fill the cell with.
  5. To fill in increasing order, drag down or to the right. To fill in decreasing order, drag up or to the left.
  6. Note:  These numbers are not automatically updated when you add, move, or remove rows. You can manually update the sequential numbering by selecting two numbers that are in the right sequence, and then dragging the fill handle to the end of the numbered range.