The Excel Skinny

A blog about Excel and its users

Applying borders to specific sides of a cell can be a very easy way to spruce up an area of an Excel spreadsheet.  Borders help separate cells from each other, making it easier to differentiate one from another.  

Perhaps the greatest function of borders is that they show up when you print your Excel spreadsheet, something the grid lines don’t do by default. 

 

To get started select a cell, or a range of cells, which you want to apply a border to.  

The shortcuts below are side specific, meaning you can only apply a border to 1 side at a time.  

 

Bottom Border:

Top Border:

Left Border:

Right Border:

 

For this example I am applying a border to the bottom of the selected cells.

 

Next I am going to apply another border to the right of the cells to effectively block them off.

  

 

Let’s get even fancier - let’s extend both borders one cell and sum them all up:

If you ever want to remove a border you can execute the shortcut to apply a border to that specific side and it will be removed, or you can execute the Remove Borders shortcut to remove any border touching the cell.  

The Remove Borders shortcut is:

And there you have it, the simplicity, and complexity, of borders in Microsoft Excel for Mac.  

XXXXX

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.  

This is a cool shortcut that can come in handy for any Excel users that have multiple formulas in a spreadsheet.  It’s very simple - all it does is toggles your whole spreadsheet so show either the cell values, or the cell formulas.  This effects the entire spreadsheet so any formula will be toggled back and forth when executing the shortcut. 

This shortcut can be executed from anywhere on the spreadsheet.  To execute simply press: 

⌃`

Once executed your spreadsheet will change from values to formulas, or vice versa.

     to    

Now go have fun toggling back and forth between your formulas and values!  

Displaying the "Find" Dialog Box

For those of you who don’t know this shortcut…you are about to learn a staple in the shortcut world.   Quickly displaying the Find Dialog box will save you those precious seconds of finding the option in your toolbar or menu, and now when your boss asks you if invoice #1245 has been paid you don’t have to scroll through your whole spreadsheet searching franticly for it.

Step 1: The only real requirement of this shortcut is that there is something to find in your Excel spreadsheet, meaning there is data somewhere.  You can tweak the search by highlighting specific rows or cells you wish to search in, or execute the shortcut with without highlighting anything to search the entire spreadsheet.

Step 2: Execute shortcut:

⌘F 

 

And this pops up:

 

Type in the “Find what:” box the data you are searching for.  I recommend keeping the options at their default settings but you can mess around with those if you’d like.  

I searched Display the Find Dialog Box and clicked “Find Next”.  This scrolled me to the cell with Display the Find Dialog box in it.  

 

This is an invaluable tool when looking for data in a large spreadsheet.  No one wants to scroll through a sea of data searching for that one cell, let the “command” + “F” shortcut do it for you!

 

This is a neat shortcut I learned the other day that can save you a quick second or two by not having to move back to your mouse.  As we all now, the mouse is the enemy and our goal is to avoid it as much as possible. It is the main thing that slows us down and the main reason shortcuts exist!

The shortcut we are going to review today performs a very simple task – when multiple cells are selected it reverts back to the original active cell.  In other words if you have several cells selected it will select the original cell you clicked to highlight those cells.  Let’s get started.

Step 1: You can only perform this shortcut when multiple cells are selected so naturally the first step is to select multiple cells.

 

Step 2: Now that I have my cells selected I can perform the shortcut.  This will effectively unselect every cell but the one I initially selected to highlight the rectangle above.  The shortcut is “shift” + “delete”:

 ⇧Delete

Step 3: After executing the shortcut you will have only one active cell.  

 

From here you can go ahead and select a different array of cells, or whatever else you like.  This shortcut is very simple but I find it useful when I have selected multiple cells and want to revert back to just one active cell.  It is one of the many useful shortcuts MS Excel for Mac provides to help us stay away from using the mouse.    

 

Messing around with multiple workbooks in Microsoft Excel for Mac can sometimes be a pain.  I am stuck in the bad habit of pressing “command” + “tab” to cycle through applications in hopes that it will cycle through my MS Excel worksheets but that’s not how it works.  The Excel Skin maps out “command” + “tab” = “Cycle thru: Applications” but if we take a second to inspect our “tab” key we can see it also has an option to cycle through workbooks. Excellent! Now lets put this tab key to work.

 

Step 1: The only prerequisite to performing this shortcut is to have two or more workbooks open and at least one active at the time you execute the shortcut.  More simply you need multiple workbooks and MS Excel has to be the active application.  

 

Step 2: Execute the shortcut.  This shortcut resides on the “tab” key and in yellow text.  Taking a look at our modifier keys we can see there is only one modifier key that has the corresponding yellow text.  Therefore our shortcut is:

⌃Tab

 

Step 3: Executing the shortcut will switch from the active workbook to the next.  If you have only two workbooks open this shortcut will keep switching back and forth between the two.  If there are more than two workbooks open it will cycle from the first to the last workbook, and then back to the first in a loop.  

 

This is extremely useful when working with multiple workbooks.  The time it takes to drag one workbook over so you can see the other one is ridiculous. The “Cycle Thru: Workbooks” shortcut is the only efficient way of switching back and forth between workbooks.  

 

Although I am still stuck in the bad habit of “command” + “tab”, which just switches applications and wastes more time, my goal of the week is to make the “control” + “tab” shortcut embedded in the Excel side of my brain. 

 

 

The Formula Builder is a great place to find any formula you may need in Microsoft Excel for Mac.  The shortcut to pop up this Formula Builder is a simple, one modifier key shortcut utilized to improve efficiency in MS Excel. Here at Excel skin we try to stay away from the computer mouse as much as possible!

 

Step 1: Select the cell you wish to build your formula in.

 

 

Step 2: Execute the Formula Builder shortcut.  This shortcut is found on the “A” key and only requires one modifier key, the “control” key.  

 

⌃A

 

Step 3: When you execute the above shortcut a Formula Builder window will appear.  

 

From here you can use the “Search for a function” bar to find a function.  It also provides you with the “Most Recently Used” shortcuts, which, upon double-clicking, will insert the formula into the active cell.  

 

For this example I want to find the VLOOKUP formula.  I know what you're thinking, just type =vlookup into the active cell.  In a few seconds I will show you why the Formula Builder is so great compared to simply typing =vlookup.  So I start typing the name of my formula: 

 

And it only took 2 letters to find the VLOOKUP formula!  I can now double-click VLOOKUP in the Formula Builder window and start my VLOOKUP.  

 

I told you the Formula Builder is great and here is why.  This drop down menu that appeared when I double-clicked the vlookup formula is where the magic happens.  From here I can simply enter the values for my VLOOKUP and it will build the formula for me.  For complex formulas like VLOOKUP’s this is invaluable.  Next week we will go over what exactly a VLOOKUP is, and how to utilize them.  

 

Scroll to display active cell


When you start working with Microsoft Excel spreadsheets that have data filled in through the BA column you know how difficult it is to find the cell you have selected.  Thankfully MS Excel for Mac has provided a shortcut that scrolls to your active cell so you never get lost in the sea of data again. 


Step 1: The purpose of this shortcut is to be able to find your selected cell without having to search the spreadsheet manually.  Therefore a selected cell is required.  This is a tricky shortcut to explain but first we will select a cell, then we will scroll away from it.  Here we go:

 


I have selected 3B.  Now I am going to scroll so it is not on my screen:



3B is no longer visible so we need to execute the shortcut to scroll back to 3B.  The shortcut can be found on the “delete” key and is written in yellow as “Display Active Cell.”  The yellow text corresponds to only 1 modifier key, the “control” key.  Our shortcut is:

⌃Delete


After executing this shortcut Microsoft Excel scrolls to my active cell. 




One thing to remember with this shortcut is you have to keep the initial active cell active, meaning you cannot click any other cells while scrolling through your spreadsheet.  If you do click a different cell, the active cell we had in the beginning is no longer active, and this shortcut will not scroll you back to it.  



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.