5. Freezing Panes
Everyone has seen spreadsheet like this before. Imagine your spreadsheet has like 100 lines of data. Unfortunately after you go past about 20 items you can't see the header at the top of the page anymore. The answer you seek is to freeze the panes.
As you can see, that item lives in the View tab in the latest version of Excel. If you were lucky enough to be graced with a standard format spreadsheet you can use the Freeze Top Row or Freeze First Column preset. More than likely, you'll need to use the Freeze Pane option. First choose the cell where you want to freeze. Keep in mind that the freezing occurs to the left and above the cell you choose.
In the example above, I've frozen the pane at B2. Everything in the first row and the first column will now stay stationary as I move down and to the left in the spreadsheet.
4. Cell Formatting
You're not stuck with just plain old numbers in Excel. You can actually make a cell or a block of cells specially formatted to your tastes. Right click on a cell and choose Format Cells. A whole world of options will open up to you.
I just formatted the Price column for Currency. Now I can see up to two decimal places AND the dollar symbol. You can do other things with the Format Cells option too but you'll have to experiment with that on your own.
3. Fill Cells Shortcut
Imagine you want to take an item in one cell and put it in about 10 more cells to the right or down. You could copy and paste the item in each separate cell but who has time for that crap. Use the Fill Cell Shortcut.
First click on the cell you'll be copying and hold the mouse button down. Then drag your mouse over all the cells you want to fill. They'll all be highlighted. Now if your dragged to the right hold the Ctrl key and press R. If you dragged down, hold the Ctrl key and press D. Viola! Filled! This is a handier trick that you'd initially think. It works with formulas too.
2. AutoSum
There are a wealth of equations you can use to do calculations on a spreadsheet, but the one that I'm asked most often is how to make things in a column add up. It's such a common task that Microsoft saved you the trouble of creating an adding equation and just gave you an AutoSum button. It's really easy to use. Just highlight all the cells you want to add up and press the AutoSum button (it's in the Formulas tab).
In the cell directly below the highlighted cells the sum total of the highlighted cells will appear. It'll even have the same formatting. You've gotta have all your ducks in a row/column though and make sure no cells with letters get in the path otherwise you'll get an error.
1. Conditional Formatting
This is easily my favorite trick. Instead of rifling through each and every number of a series, you can go through everything at once and immediately pick out the variables you're looking for. In the example below, I want to find all the names with IQs less that 50. Sure it's easy on this list but imagine a much MUCH longer one. Here's how you make it pop. Click on the column letter to highlight the entire column. Go to the Home tab and look for Conditional Formatting.
Once again, lots of options but once again there are a lot of presets to make it easy. Move your mouse over Highlight Cell Rules and then pick Less Than. Enter 50 in the box that pops up. You can keep the preset formatting or pull it down for more options. Hit ok and you're done.
There's a lot you can do with Conditional Formatting but again I'll leave it to you to experiment.
No comments:
Post a Comment