I Heart Tech

Three Excel Tips

These are must-knows:

• Freeze Rows and Columns so they sit still. Say you have column titles in Row 1 and you want them to stay visible as you scroll down

1. Select cell A2
2. Go to Window > Freeze Panes
• Excel will freeze all rows ABOVE and to the LEFT of the cell you have selected so you can freeze as many as you need. So if you want to also freeze column A, then start at cell B2...get it? To unfreeze, go back to Windows > Freeze panes.

• Placing an 'apostrophe before a string of numbers signals Excel to the treat the data as text and it won't do funky things to it (like drop leading 0's).
• Create a manual line break in a cell with  ALT + Enter

And while I'm chatting about Excel, let me say this, and you know who you are...Excel is not a word-processor and to treat it as such is criminal. You will pay.

Excel: Alternate Row Shading

Adding colors and shading to Excel spreadsheets, especially those with a lot of data in them, can make reading them much easier. Here's how you can set up every other column or row to display shading.

1. Select the group of cells (aka the range) that you want to shade.
   
2. Go to Format > Conditional Formatting
3. In that Conditional Formatting dialog box, change the drop down to display  Formula Is and type this formula in the field:
   
   To shade rows enter this formula:
     =mod(row(),2)=0
   To shade columns enter this one:
     =mod(column(),2=0
   To shade checkerboard style enter:
    =mod(row(),2) =mod(column(),2)
4. Click the Format button and in the Format Cells dialog box, click the Patterns tab.
5. Select a color and click OK.

 

Excel'ent Sites

Resources and Links

There's so much to know about Excel; all we can do it keep learning. Here is running a list of the sites and resources I frequent when I need help. Know of a great site, please leave it as a comment and I'll add to the list.