Excel. Love it or hate it. Embrace it or dread it. It’s a program I enjoy teaching and many don’t fully understand the power at your fingertips. No matter the financial program you use, nearly all can export or ODBC link their data to Excel. What should you know to help you maximize your effectiveness (and maybe make other envious too!)?
1. CTRL + Arrows
How many times have you been looking at your worksheet and scrolling right to left or up and down through hundreds or thousands of lines? There is a really simple way you can jump to the end of your sheet in any direction. Just use CTRL + Arrow (either right, left, up or down) to jump to the end direction of the data. If you are on the Mac (like me), substitute CTRL with the command key.
2. Flash Fill
Computers are supposed to make our lives easier and over time they have. The next big evolution is pattern analysis. Excel now can do just that. If you have two or more elements in your spreadsheet, Excel can detect and fill the pattern to save your data entry time.
To use, be sure it is turned on by looking in File options > Advanced. Then type in your first number 001, drop a cell and type 002. Excel will automatically detect the pattern and show the pattern. To accept just press enter and you are done! Or simply enter in the first numbers of the pattern, highlight and drag the selection down to where you want the pattern to end. Done!
3. Sum
Gone are the days of manually adding formulas like =A1+A2+A3. Using =Sum(A1
4. F4
Copy and paste. Saves time right? What if you are coping and pasting formulas? The cell references will be changed as you paste those cells. How can you “lock” it down? By pressing F4 in your original cell.
Once you do, you will notice $ signs by the letter and number. As you keep pressing F4, the $ signs will change positions or go away altogether.
There is one more use for F4. If you press F4, it will repeat your last action. Doing some special formatting? Apply it once, and press it again where you want to apply it. Big productivity boost!
5. Paste Special
Before we leave copy and paste, let’s cover one other thing. Pasting will copy everything — numbers, formula, and formatting. Sometimes, we don’t want the formatting to follow. How do you just past what you want?
Go to Edit > Paste Special. Pick what exactly you want to paste. It’s just that easy.
6. Adding Multiple Rows
How do you add rows between other ones? Two ways:
- Highlight the number of rows you want to add and right click > Insert on your mouse. It will add the number of rows you highlighted.
- Use shortcut CTRL, Shit, + to add a row. Keep pressing + and you’ll add multiple rows.
7. Undo / Redo
Playing around and went too far? Don’t worry! Simply use Ctrl Z to undo mistakes in Excel and you are right back to where you were. Went too far back? Just use Ctrl Y to do the opposite — redo. Also on top of your Excel
8. Freezing Panes
How many times have you scrolled around a large data set only to forget what column is what? Excel has the ability to freeze panes to make your navigation more useful. You can freeze the top row, first column or any number of either. Identify the columns and rows of the area you want to freeze. Then select the cell immediately to the right of those columns and beneath those rows. Go to the View tab and Freeze Panes in the Window section. Alt
9. Duplicates
Data sets may have duplicates. Instead of searching for them manually, let Excel do the heaving lifting for you. Just go to the Data tab in the Data Tools section of the Ribbon.
10. Index Match
Ready to impress your coworkers? Index Match will make you look like an Excel guru. Most have heard about VLOOKUP, but it limited to only look right to left. Index Match allows you to look at anywhere in the table.
Are there some functions and formulas you like that I didn’t cover? Let me know.
