Microsoft Excel is just about as famous for being frustratingly difficult as it is for being exceedingly useful. These five tricks will make your data-crunching experience a much more relaxing one.
1. What Day Is It?
Excel’s “Today” feature is no doubt a quick way to insert the day’s date into your spreadsheet. If you are not familiar with it, entering =TODAY() into a blank cell puts the day’s date into the cell. A word of warning, though – Even though this seems like it would be a useful tool to track when you input important data into your spreadsheet, the date will automatically update whenever you open the spreadsheet. If you wish to keep a log of when important data was put into your spreadsheet, Ctrl + ; will do just that. If you wish to input the date but not the time, Ctrl + Shift + ; will do the trick. (Extra tip: Inserting =NOW() will insert the day’s date and time into the cell.)
2. Protect Your Formulas!
If you have important formulas you do not want others to see (such as how employees’ bonuses are figured) you can prevent Excel from showing that formula when the particular cell is highlighted:
- On the Excel toolbar click Edit + Go To
- Click the Special tab to open the Special menu
- Click Formulas and make sure all four of the options are checked.
- Click OK and Excel selects every cell that has a formula.
- Select Format, Cells, and choose Protection
- Place a check mark next to the Hidden option, and make sure that there is a check mark next to the Locked option. Click OK
3. Take Notes!
On the topic of formulas, it is also possible to add hidden text to any particular formula. This is incredibly useful to help remember what each number in the formula represents. For example: you have a budget formula that reads “1000+ 250- 200*2.” If you are having trouble remembering what each number represents, simply add + N() behind the formula. Excel will translate the +N() into 0 and it will not affect your formula. Then you can add in notes (in quotation marks) to jog your memory for the next time you open the spreadsheet.
=100*(1+10%)+N(“10% was the projected increase from 2011’s rates″)
What this formula is actually doing:
4. Freeze Panes
As if number crunching wasn’t already painful enough, it becomes exceedingly difficult as you work farther and farther down or to the right. There is nothing more frustrating than forgetting what each row or column represents, scrolling all the way back up to remember, and then rapidly scrolling back down to begin working again. Luckily, Excel has realized this frustration and included a “freeze pane” feature that will keep a column/or moving as you move in the worksheet. Simply click and select the row below or the column to the right of where you want to freeze. Then, on the Window menu, select Freeze Panes. Viola! You will never forget what you are inputting again.
5. Where Did That Column Go? Oh, There It Is.
There are a never-ending amount of situations within Excel when you just may not want to include information that was pertinent to you with others. You may be displaying productivity reports to the office, but you don’t want everyone privy to who exactly it was that somehow managed negative productivity. Excel allows users to hide columns in the spreadsheet before printing and while working.
To hide a column:
- Highlight the columns/rows you want to hide.
- Click Format, Column, Hide
To unhide a column:
- Select the columns that were adjacent to the ones you hid originally.
- Click Format, Column, Unhide
The process is exactly the same for rows, except that you will select Format, Rows instead of Format, Columns.