The Excel Skinny

A blog about Excel and its users

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

Currency Format with Two Decimal Places

This shortcut is another quick and easy way to manipulate your data with a predetermined format.  Two weeks ago we discussed how to apply two decimal places, a thousands separator, and a minus sign if necessary to a cell or range of cells.  This week’s shortcut is along the same lines as it applies a predetermined currency format with two decimal places


Step 1: Select the cells we want to manipulate. 



Step 2: This shortcut is found on the “4” key and is labeled “$#,###.00”.  Looking at our Excel Skin we can see that the text for this shortcut is in white, so we take a look at our modifier keys.  We can see the “Control” and “Shift” keys also have corresponding white text.  Execute the shortcut by depressing the following keys:


⌃⇧$



Our data has now been manipulated to have a currency format with two decimal places.  This number will show up in red with parenthesis if it is a negative value. 



As our second predetermined format shortcut, the amount of time saved from not having to open the Format Cells dialog box and manually applying these formats is tremendous.  Being able to make the number 15555 turn into $15,555.00 with the click of three buttons will greatly enhance your productivity and efficiency in Excel for Mac.  




Applying the number format with two decimal places, thousands separator, and minus sign (-) for negative values.

This shortcut provides a fast and easy way to manipulate your data with a predetermined format.  This predetermined format is one of many that are built into Excel but few users know about it.  In the following steps you will learn how to add two decimal places, a thousands separator, and a minus sign for negative values to a specific cells, or range of cells, without opening up the Format Cells dialog box. 

Step 1: Select the cells we want to manipulate.

 

Step 2: This shortcut is found on the “1” key and is labeled “#,###.00”.  Looking at our Excel Skin we can see that the text for this shortcut is in white, so we take a look at our modifier keys.  The “Control” and “Shift” keys have white text as well.  Execute the shortcut by pressing the following keys:

 ⌃⇧!

 

Our data has now been manipulated to have two decimal places, a thousands separator, and a minus sign if a negative value is present. 

 

This shortcut shows one of the many ways to increase efficiency in Excel for Mac.  By simply pressing three keys we avoid opening the Format Cells dialog box and the inevitable mouse movement needed to format the cells. Now you can easily reformat your cells to this predetermined format of two decimal places, a thousands separator, and a minus sign if necessary.  Enjoy all the free time you just saved!

 

Hiding Rows:


Two weeks ago we discussed how to hide and unhide columns.  In this short blog post I will teach you how to do the same with your rows.


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



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

(⌃9)


Just like the “Hide Column” shortcut, the “Hide Rows” shortcut uses just one modifier key: “Control” and the “9” key.  


Unhiding Rows:

Step 1: Again, just like the process of unhiding columns, we must select the rows on either side of the row that we hid.  



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

(⌃⇧9)


Your hidden row should now appear.  By simply adding the “Shift” modifier key we change the yellow “Hide Rows” shortcut to the light blue “Unhide Rows” Shortcut.  

To read more about the uses of hiding rows and columns check out our previous post here: http://www.excelskin.com/blogs/the-excel-skinny/7154640-applying-and-removing-cell-borders-with-excel-skin


P.S. We know hiding your data on Excel is fun but remember to unhide it when handing in those quarterly reports to your boss! He might not like blank spreadsheets as much as we do.  (Unless you are a secret spy) 

 Displaying the Format Cells dialog box 

One of my favorite shortcuts when working with numbers is the shortcut that displays the Format Cells dialog box without taking your hand off the keyboard.  It’s so simple and efficient for such a useful shortcut that I cant believe I didn’t know about it before the Excel Skin!  


Step 1: Like most shortcuts used in Excel, we must first select the cell we are trying to modify.  



Step 2: The “Format Cells” text on the Excel Skin is in red so we look to our modifier keys.  The “Command” modifier is the only one with red text as well, therefore we press both keys simultaneously:

(⌘1)


And this pops up!



From here you can choose whichever category suites your needs and format your cell how you like.  


This is a great example of how the Excel Skin can save you time and frustration by mapping out useful shortcuts like this “Format Cells” dialog box display.  


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.  


Applying cell borders

In two short steps you can keep your hands on your keyboard and use this Excel shortcut for Mac to outline a cell or multiple cells on your worksheet.  

Step 1: Select the cell or range of cells you wish to outline.



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


(⌥⌘0) This particular shortcut requires two modifiers, both the option and command key.  The “outline cell” shortcut is easily identified on the Excel Skin by following the purple text.  Using the Excel Skin helps easily map out all shortcuts through a color-coded system that keeps your hands on the keyboard and away from that mouse, saving you time and frustration.


Step 3: The cells you selected now have a border.  



Removing cell borders 

Removing cell borders is just as easy as applying them to selected cells. To remove a border:

Step 1: Once again select the cells in which you would like to remove the border.


Step 2: Using the same two modifiers as we did when applying the border, follow the purple text associated with removing borders and press:


(⌥⌘-)


Step 3: The cells you selected will be now be borderless.



Unlike the default gridlines that do not print with your Excel worksheet, cell borders that you apply do appear on printed pages.  This can be especially useful when trying to draw attention to specific cells that are part of a larger workbook.  

Outlining cells can also be important when separating information.  In a sea of Excel cells it is easy to lose track of important numbers and data that need to be available for quick viewing.  




And the winners are::

Max G., Jennifer W., and Lina V.!! 




Win a Skin Giveaway 

Now that Excel Skin is up and running we’re ready for the world to know! To celebrate we’re giving away 3 Excel Skins to our fans!


It takes 2 easy steps to enter on our Facebook page! 

1.   One you like "Like" our Facebook page. Enter by leaving us your email on the contest tab.



2.  Share it! 

Share any of our Facebook posts about the contest on your own wall so your friends can enter too!


 

We'll pick winners at the end of October! Good luck!

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.