The Excel Skinny

A blog about Excel and its users

Microsoft Excel for Mac doesn’t automatically underline incorrect spelling.  This is because formulas are usually abbreviations of words, which would cause almost every cell with a formula to be underlined. There are times that spell check is very necessary though. Note: Excel will not correct grammar like Word, but it will find those misspelt words. 

When your Excel spreadsheet is going to be in a presentation or handed into a professor it’s best to do a spell check on the entire workbook just incase. 

The shortcut to spellcheck is a simple one modifier key shortcut. 

With any cell selected press fn + F7:

This will open the spell check dialog box.  From here you can review all spelling mistakes in the worksheet and correct them if needed. 

If the correction is not needed click Ignore to move on to the next incorrect word. 

Click on one of the Suggestions and then Change to change the selected word to the Suggestion

Click Add to add the word Excel marked as misspelt to the dictionary so it does not select the word as incorrect in the future. 

Ignore All will skip over every instance of the misspelt word in your worksheet.  For example, if I clicked Ignore All on the dialog box above all cells with asdf would be skipped.  

Change All will change every instance of the misspelt word to the word chosen in the Suggestions box.  

Do you Strikethrough? 

 

The strikethrough function in Microsoft Excel, and any other program for that matter, is still a mystery to me.  Back in my middle school days a little whiteout/delete button was the best practice for removing mistaken words from a sentence.  So what in the world is strikethrough used for?! 

 

My mindset changed a few days ago when I was going through a bank account at work and looking for unapplied payments.  My first idea was to highlight all the payments that were unapplied so they would easily pop out at me.  Unfortunately time is money, and this project needed to be completed by the end of the day.    The couple of seconds it took to use my mouse and select the highlight shortcut on my toolbar was adding up to minutes, and I needed a new system.

 

Our office "Excel Guru" showed me how to utilize the “strikethrough” shortcut so I didn’t have to take my hand off the keyboard at all, greatly reducing the time it took to complete the task. Instead of highlighting unapplied payments, I strikethrough-ed all applied payments so the unapplied stood out, unstirred.  

 

Here’s how to do it!

 

Step 1: Like the majority of Excel shortcuts, you must start on an active cell.  What is nice about the “strikethrough” function is you can apply it to cells that do not have any data yet, so if you enter data into those cells they will automatically have the strikethrough formatting applied to them.  

 

 

Step 2: Now that we have a cell selected we can execute the “strikethrough” shortcut.  If we take a look at our Excel Skin we will see “strikethrough” lands on the “X” key, and is printed in green text.  Our corresponding modifier keys are therefore “shift” and “command”.  The full shortcut is “shift” + “command” + “X” simultaneously.  

⇧⌘X

 

Step 3: Executing the shortcut will produce a line through the data of the active cell.  

 

Now we can go through this list of payments, strike through the applied ones and have the unapplied un-striked.

 

What are some ways you use the “strikethrough” shortcut?

 

Using the "Fill Down" shortcut

Last Monday I re-discovered the fill down shortcut. It's one of the most underrated (and under-used) shortcuts in Excel for Mac. What does it do? It takes the data and/or formula from the active cell, and applies it to the cells in the selected range. Here's how to execute Fill Down: 

Step 1: After navigating to the active cell from which you would like to copy data, hold down shift + down arrow to expand the selection range. 


Step 2: The “Fill Down” shortcut can be found on the “D” key of your Excel Skin, and can be also identified by the large yellow arrow pointing downwards. Since the text is yellow we must look at the modifier keys and find the corresponding yellow text. The “control” key is the only modifier key with yellow, therefore our shortcut is executed by pressing "control" and "D". ⌃D

 

D



The data from the cell above now should be copied into the cell you have selected.


This shortcut saves even more time than the copy-paste shortcuts, and can be lead to a more fluid Microsoft Excel experience. Try the “Fill Down” shortcut in your next spreadsheet and you’ll never stop using it!

Selecting Everything

There will be times when selecting everything in the spreadsheet is necessary to format or move information elsewhere.  With this simple two-key shortcut Excel will highlight every single cell in the spreadsheet and wait for your next move.  

Step 1: Click any cell.  
 
This shortcut does not require a cell with any information to be selected.  All that is required is a cell in the spreadsheet. 

Step 2: The "Select All" shortcut is found on the "A" key of our Excel Skin's.  The "Select All" text is in a reddish purple so, just like all Excel shortcuts for Mac, we must look to our modifier keys.  The "Command" modifier key is the only one with the same colors as the "Select All" text so we have successfully found both the required shortcut keys.  




The result:


Now you can do as you please with your data.  I use this shortcut most often to copy a whole spreadsheet into another spreadsheet or when I need to apply a specific format to every number in my spreadsheet such as the currency format discussed in the last blog post. It can also be used to simply clear the spreadsheet of all data by using the clear content.

⌘A

How to highlight the cells related to a selected formula

This shortcut provides a very useful tool for those working with a plethora of formulas in their spreadsheets.  It will highlight all the cells that are involved in the formula of the active cell you have selected.  In the following steps the shortcut's effectivness will be more transparent.

Step 1: For this shortcut you must have a working formula in your spreadsheet.  I will demonstrate with the =SUM formula. 

Step 2: Select the cell that contains the formula.

To double-check there is a formula present in the cell, glance up at the function box. For this example my function box reads:


Step 3: Now that I have selected a cell with a formula in it I can execute the “Referenced” shortcut to see all cells that correspond with this formula.

⌃⇧{


Result: All the cells that are involved in my =SUM formula are now highlighted in the spreadsheet.  

This shortcut is especially useful in large seas of data when it may be hard to pinpoint all the cells related to a specific formula. 

Hiding and Unhiding Columns

The shortcuts of the week are how to hide and unhide columns.

Hiding Columns:

Step 1: Select the column you wish to hide by clicking on the B in this example.



Step 2: Using the Excel Skin as a guide, press the following keys:

(⌃0)


The “Hide Column” shortcut is very simple. It only uses one modifier key and the 0 key.  Taking a quick look at your Excel Skin you can see that the “control” modifier key is the only modifier key that has yellow text.  This is helpful to remember when looking at other yellow text shortcuts.  


Unhiding Columns:

After hiding a column it may seem gone forever, but have no fear.  Unhiding a column is just as easy as hiding one.  


Step 1: Select the columns on either side of the column you hid.  For example above we hid the B column, so to unhide B we select both the A and C columns.

Step 2: Once again using your Excel Skin as a guide, press the following keys:

(⌃⇧0)


The “Unhide Column” shortcut is the same as the “Hide Column” shortcut + the “Shift” modifier key.  Luckily you don’t have to memorize that because the Excel Skin easily maps it out for you! 


Hiding columns is useful for a plethora of reasons.  Maybe your worksheet has information you don’t want others to see, or perhaps the column is holding data in your endless spreadsheet that is just a necessary annoyance. Hidden columns can also serve the purpose of hiding formulas without messing with their functions.  The “Unhide Column” shortcut is used for those times that your hidden information needs to come out of hiding for editing and modifications.  


Overview

There are four primary modifier keys: Shift, Control, Option, and Command. The function key, fn, may also be considered a modifier key for our purposes. Descriptive text for keyboard shortcuts is printed in colors that correspond to modifier keys.

Basics

“Edit an Active Cell”. (F2 for PC users) is a simple and useful shortcut on the “U” key. On the Excel Skin, the shortcut key’s text is written in yellow.

Edit Active Cell Shortcut

Looking at the modifier keys, we see that “Control” is also written in yellow.

Mac Excel Modifier Keys

Therefore, we hit Control + “U” to edit an active cell.

Some shortcuts require more than one modifier key. “AutoSum” is written in green.

AutoSum Shortcut for Mac Excel

Looking at the modifier keys, we see that green appears on both the Shift and Command keys. 

To perform an AutoSum function, we hit Shift + Command + “T.”

AutoSum Key Combination Mac Excel

It’s that easy.


Function Keys

Many people find shortcuts involving function keys (F1-F12) to be confusing. This is understandable because function keys are unique in two ways: 1) Function key shortcuts require the user to hit fn in addition to the designated modifier key combination and 2) default operating system shortcuts override certain application shortcuts.

First, note that “Check Spelling” is written in white text on the F7 key.

Check Spelling Shortcut

Unlike the rest of the keyboard, where white text prompts Shift + Control (the two modifier keys with white on them), white text on function keys means you should type fn + F7.

Mac Excel Shortcuts Function Keys

So remember to hit fn when using a function key shortcut.

Second, take a look at the F11 key on which “New Sheet” is written in blue text.

New Sheet Shortcut Mac Excel

Since this is blue text on a function key, we know the key combination is fn + Shift + F11. Try it out. If you did not successfully add a new sheet, then your application windows probably receded towards the corners of your screen. This means that your Mac’s operating system has default shortcuts that are overriding those in Excel 2011. No big deal, you just need to adjust your user settings in System Preferences. For instructions on how to do that, read Setting up your Mac for Excel.