The Excel Skinny

A blog about Excel and its users

5 Shortcuts to Applying Formats

Instead of just one shortcut this week we are going to cover five simple shortcuts.  These shortcuts will save you a lot of time when you are trying to apply a specific format to a cell. 

 

Shortcut #1: Applying the time format

Shortcut #2: Applying the date format

Shortcut #3: Applying the general number format

Shortcut #4: Applying the percentage format

Shortcut #5: Applying the exponential number format

 

Step 1: As with most shortcuts in MS Excel for Mac you must have an active cell, which you wish to manipulate.  The format shortcuts are unique in the sense that you do not need data in the cell already.  Once you execute the shortcut, which applies a format, any data entered into the cell will adopt the format you have entered.

  

Step 2: Now we must execute the shortcut.  All five of these shortcuts have the same modifier keys: “control” + “shift”.   

 

Shortcut #1: Applying the time format

 

Shortcut #2: Applying the date format

 

Shortcut #3: Applying the general number format

 

Shortcut #4: Applying the percentage format

 

Shortcut #5: Applying the exponential number format 

 

 

 

I have applied the five formats to the cells directly under their labels:

 

 

Step 3: Inserting data into the formatted cells.   

Now that you have formatted the cell to your desired format, you must enter the data.  

*Note: you must enter data in the same format as you want it to appear.  For example the raw data I am entering into these cells are:

2:30 for Time

1/15 for Date

77 for Number

77 for Percentage

77 for Exponent

 

And my results:

 

 

Now instead of right clicking, choosing Format Cells, and having to riffle through the options in the Format Cells dialog box, you can quickly apply these five pre-programed formats to any cell with the tap of three keys.  

 

The Media Browser: 

Have you ever inserted a picture into your Microsoft Excel spreadsheet? You probably used your mouse, clicked the “Insert” menu, and proceeded to the “photo” option.  Luckily there’s a shortcut for that!  

Step 1: The “Media Browser” function does not require an active cell.  All you need is an open Microsoft Excel spreadsheet. 

Step 2: Execute the “Media Brower” shortcut.  This can be found on the “M” key in orange text.  Taking a quick glance at the Excel Skin modifier keys we can see “control” and “command” both have the corresponding color.  Therefore our shortcut is:

 ⌃⌘M

Step 3: The Media Browser will now pop up:

 

From here you can browse the picture files on your computer and find the one you wish to insert into your Microsoft Excel spreadsheet.  Once you have found the right picture double-click it. This will open the picture in the lower portion of the Media Browser.  

 

Step 4: Drag the selected picture onto your spreadsheet and place it wherever you want (You can move it later).  Once you have dragged the picture to the spreadsheet press the red circle in the top left hand corner of the Media Browser to close it.  You can resize your picture by dragging any corner of it to fit your needs.  

  

And there you have it!  You can now insert pictures to your Microsoft Excel spreadsheets with an efficient shortcut rather than searching the Insert menu for this function.  

 

How to Start a Formula

My first summer internship in college is when I realized I no longer have the leisure to scroll over every icon in my toolbar to find what I am looking for.  Deadlines were serious, and my lack of Excel skills started to show.  I remember the most common mistake I had in my first few weeks involved formulas.  I was under the impression that anything I typed into my cell, Excel would do.  No one ever told me how to actually tell Excel, “Here’s a formula.”

For those of you who are reading just to find out how to start a formula, I wont make you wait.  All you have to do is press the “=” key, and you can begin.  

Here are the steps to applying the =SUM formula:


Step 1: Select the cell you wish to have a formula in.  



Step 2: Press the “=” key.



Step 3: In this example I am going to use the simple =SUM formula.  =SUM, as you may have guessed, gives you the sum of the cells you select.  We already have our = sign in the cell so to add our formula we must type in SUM.


By typing the first letter of the formula, S in this case, Microsoft Excel brings up a list of formulas and functions that start with S.  My most recently used formula starting with S is coincidentally the SUM formula (I use it a lot), which is reflected on the upper portion of the list under “Most Recently Used”.  


Step 4:

Click on SUM and a formula builder will appear in your cell:


Excel is telling us, “I know you want to SUM some numbers, now direct me to them.  


Step 5: With your mouse, click on the cells that hold the data you want to sum.


I have selected 5 numbers (1,2,3,4,5) for my =SUM formula.


Step 6: Press “Enter”



And there you have it.  The sum of the 5 numbers I chose appear in the cell we started our =SUM formula in. 


By pressing the “=” key in a cell you can start any formula.  It can be as simple as =(select a cell) + (select a cell) to add two cells together, or a more complicated VLOOKUP formula.  Whatever your formula is, it’s starting with the “=” sign.   


Undo the last action

This shortcut is a very basic but extremely useful shortcut.  Have you ever been working on something and accidentally pressed the wrong key, erasing everything in a split second?  After this brief tutorial you’ll be able to reverse this error in less time than it took to make it.

This shortcut relies on an error or mistake to occur.  For this example I am going to have a cell with data in it, which I “accidentally” delete. 



I have a basic AutoSum formula in the active cell, adding up 55 + 77.  Now I “accidentally” press a random key.


My AutoSum formula is replaced with z.  Have no fear! This is where our shortcut comes in handy.  With a simple 2 key shortcut, “command” + “Z”, the cell will be reverted back to its previous state.  

⌘Z



And the AutoSum formula is back without any questions!  This shortcut is extremely useful when you are zooming through a Microsoft Excel spreadsheet and have no time to take your hands off the keyboard.  It is useful even in a casual environment when the shortcut is simply easier than finding the Undo button in the toolbar.  Whatever your application for the shortcut is, I have no doubt you will be using it often.  


This week we’re going to learn how to copy and paste in Microsoft Excel for Mac with shortcuts.  Although they are 2 separate shortcuts, copy and paste almost always are executed one after the other.  

Copy

The copy shortcut has to be one of the most used shortcuts for Microsoft Excel.  Very rarely will I open up an Excel spreadsheet and not execute this shortcut.  It ensures the information is copied correctly, has the ability to copy formulas, will copy over any information in the cell you paste it, and is a simple one modifier key shortcut.


Step 1: Select the cell you wish to copy.


Step 2: Execute the “Copy” shortcut.  The “Copy” shortcut can be found on the “C” key.  The “Copy” text is presented in the color that corresponds with only 1 modifier key, the “command” key.  Therefore our shortcut is “command” + “C”.


⌘C


The copied cell will now be animated with blinking lines that border the cell.  This is also useful if you forget which cell you have copied, it’s the blinking one!

Now that we have a copied cell we must move onto the Paste function.


Paste

We have the cell with the text “Copy Me” copied and now we must paste it.  


Step 1: Select the cell you wish to paste your copied information into.  

Step 2: Execute the “Paste” shortcut. The Paste shortcut is located convientiely next to the “Copy” shortcut, on the “V” key.  “Paste” is also written in a color that corresponds to the “command” modifier key, and thus our shortcut is “command” + “V”.

⌘V


After executing this shortcut, our copied data will appear in the selected cell.  


Once you get use to it, the Copy and Paste shortcuts will become your most used, and most efficient shortcuts on Microsoft Excel for Mac.  

Inserting the AutoSum formula

The “AutoSum” shortcut is one of those shortcuts that you don’t think about because Microsoft Excel has the AutoSum shortcut on the toolbar by default.  You only learn it once you realize the amount of time you can save by keeping your hands on the keyboard and away from that cumbersome mouse.  One of the great features that the “AutoSum” shortcut does is it selects the entire row or column that corresponds to the active cell you executed the shortcut in.  In the next steps this will become clearer.  


Step 1: If you are even thinking about using this shortcut, you will need multiple cells with numeric values in them, and you will be looking for their total.  Having said that, step 1 is to select the cell you wish the sum to appear in.



In this example I intend on having the sum of this column appear in the active cell directly under the column.


Step 2: Execute shortcut.  The “AutoSum” shortcut can be found on the “T” key and is written in green text.  By taking a glance at the Excel Skin’s modifier keys we can confirm there are 2 modifier keys that have the corresponding green color.  These keys are the “shift” and “command” key.  Therefore our shortcut is:

 ⇧⌘T


Once we execute this shortcut Excel will select the cells it believes you are trying to sum.  



And Excel was right! These are the cells I am trying to add up.  


*If there are cells outside this selected region I can select them by dragging the corners of the selected region, or by holding the “command” key and selecting the cells individually.  


Since the “AutoSum” shortcut was correct in selecting that column for me I press the "Enter" key and the sum appears in the active cell.


The sum is 63.


This shortcut is very useful if you have a tendency to use the AutoSum feature of Microsoft Excel for Mac a lot.  Instead of taking your hands off the keyboard and clicking the AutoSum button on the toolbar you can execute this 3 key shortcut and move onto your next task seamlessly.  



Fill Right

A couple weeks ago I wrote about my new favorite shortcut, the “Fill Down” shortcut. As a “Fill Down” shortcut rookie I didn’t realize “Fill Down” had a rival, “Fill Right”.  “Fill Right” does the same exact thing as “Fill Down”, except it fills to the right…obviously.  


Step 1: Select the cell directly to the right of the cell you wish to copy data from.  



Step 2: The “Fill Right” shortcut can be found on the “R” key of your Excel Skin, and can be also identified by the large yellow arrow pointing right.  Since the text is yellow we must look at the modifier keys and find the corresponding yellow color.  The “control” key is the only modifier key with yellow; we can now execute our shortcut.  

⌃R


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



Just like the “Fill Down” shortcut, “Fill Right” saves time by combining copy and paste into one shortcut.  


 Clearing and Deleting are two very different functions in Microsoft Excel.  On one hand we have a single key to “clear the contents of the selection,” and on the other hand we have a simple shortcut to “delete the selection.”  Before getting into the shortcuts we must define clearing and deleting.  

When you clear the contents of an active cell you are simply clearing whatever is inside the cell and nothing more.  Deleting the selection is a different story.  Deleting the selection effectively clears everything in the cell by removing the cell from the spreadsheet entirely.  When you execute the “Delete Selection” shortcut a dialog box will pop up to ask you which way you would like to shift the cells, a result of the complete removal of the active cell.    


Clearing the Cell

We will start with clearing the cell.  This isn’t so much a shortcut as it is a function of Microsoft Excel that resides on the “delete” key.  To clear the content of the active cell all you must do is select the cell you wish to clear, and press the “delete” key.  It’s so simple no pictures are needed!  The only caveat to this function is that it will only delete 1 cell at a time.   

Now onto the fun stuff, deleting.  


Deleting the Cell

Step 1: As with most Microsoft Excel Shortcuts, the “delete selection” shortcut starts by selecting the cells you wish to modify.

  


The example above is to show the shifting capabilities of the “delete selection” shortcut.  


Step 2: Now that I have selected the cell I wish to delete I can execute the shortcut: 


⌃Hyphen


And this pops up:



I would like to replace the “Delete Me” cell with the “Don’t Delete Me!” cell so I chose “Shift cells up” and click OK. 



I have successfully deleted the cell that was in 1A and replaced it with the cell that was in 2A by shifting the cells up. 


When using Microsoft Excel for Mac I tend to use the “delete selection” shortcut more than the “clear selection” shortcut.  This is not to say the “clear selection” is useless. It is the fact that the “delete selection” shortcut effectively clears the selected cell as well as shifts all cells, which is something that is usually done (especially after clearing a row or column) anyway.  


Insert Date or Time Shortcut

A shortcut to enter the date or time is one of those shortcuts that you first learn about and think, when will I ever use that?  Next thing you know you’re mashing away at your number keys trying to enter in the current date or time because the shortcut went in one ear and out the other.  Here is how to execute the shortcut in 2 simple steps.  


Step 1: Select the cell you wish to enter the current date or time in.



Step 2: There are, of course, 2 different shortcuts for entering the date and the time.  Microsoft Excel graciously placed these two shortcuts on the same key, the semicolon or “;” key.  The difference is in the modifier keys.  Lets start with the “Insert Date” shortcut.  

“Insert Date” shortcut text color corresponds to the “control” modifier key on our Excel Skin so we know the “Insert Date” shortcut is executed by pressing: 

⌃Semicolon (;)


The current date will appear in the cell you have selected.




To execute the “Insert Time” shortcut we go through the same thought process.  The “Insert Time” text corresponds with the color on the “command” key and therefore our shortcut is: 


⌘Semicolon (;)


The current time will now appear in the cell you have selected.

This week’s shortcut will combine two similar and very useful shortcuts: selecting the entire row and selecting the entire column.  Although they are executed with two different key combinations they perform a very similar function.  

Selecting the Entire Column:

Step 1: A great part about this shortcut is you can execute it from any cell in the column you want to select.  


With the active cell in the picture above I am setting myself up to highlight the column.


Step 2: To highlight the column, execute the “Select Entire Column” shortcut.  This shortcut can be found on the space bar.  Like all Excel Skin shortcuts, the “Select Entire Column” shortcut is color-coded and corresponds to the modifier key, or keys, that match the color-coded text.  The “Select Entire Column” text is yellow.  Looking to our modifier keys we can see the "control" key is the only modifier key with the corresponding yellow text.  Therefore our shortcut to select the entire column is ⌃Space Bar.



Once the shortcut is executed the entire column will be selected:

From here you can modify the selected column in any way you like.


Selecting the Entire Row:

Selecting the entire row uses the same exact process as selecting the entire column, only with different keystrokes for the shortcut.


Step 1: Select a cell in the row you wish to highlight.


Just as when selecting the entire column, any cell in the row you wish to select may be active.


Step 2: The “Select Entire Row” shortcut is also found on the space bar in blue text.  The “Select Entire Row” text corresponds with the color on the “shift” key, therefore our shortcut to select the entire row is ⇧Space Bar.


Now the entire row is selected.




Selecting an entire row or column can be used for an infinite number of reasons.  Perhaps you want to highlight a row or column to make it stand out in your spreadsheet, or maybe you are trying to copy the row or column and move it elsewhere.  Whatever it is you may be doing, selecting the entire row or column with this shortcut will speed up the process.