10 Mac and Windows Excel Functions You Need to Know

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.

Mac Excel Command + Arrow

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!

Mac Excel Flash Fill

3. Sum

Gone are the days of manually adding formulas like =A1+A2+A3. Using =Sum(A1:A3) saves time. But what if you could do it faster? Just select the cell at the end of the row or column and press Alt + The summation will be done in a second. Have the Mac version of Excel? Use Command + Shift + T

Mac Excel Sum

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!

Mac Excel F4

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.

Mac Excel Paste Special

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.
Mac Excel Adding 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 window you will see undo and redo arrows to accomplish the same thing. Give it a try!

Mac Excel Undo and Redo

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 W F is the shortcut.

Mac Excel Freezing Panes

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.

Mac Excel Remove Duplicates

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.

Mac Excel Index Match

Are there some functions and formulas you like that I didn’t cover? Let me know.


Leave a Reply

Like what you are reading? Subscribe and get notified of new posts.

You have Successfully Subscribed!