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

This has to be the coolest shortcut for us Microsoft Excel nerds that happen to categorize or use the same labels for multiple cells.  What does this shortcut do?  It displays the AutoComplete list for your Excel spreadsheet. 

I use this often when I’ve been adding comments next to cells and have created a pretty substantial AutoComplete list.  I remember one spreadsheet where I had to manually verify how a list of invoices were paid.  Options included everything from “Credited” to “Open” to payment methods like “Check” or “Wire”.  What is cool about the Excel AutoComplete feature is I only had to type Cr for Credited to autocomplete and just a W for Wire to autocomplete.

Anyways! This shortcut basically drops down a list of all the saved phrases that have been added to the AutoComplete list in your Excel spreadsheet.  This way you can see every comment you have entered more than once.  

Step 1: You must have comments or duplicates in your spreadsheet for Microsoft Excel to recognize an autocomplete list is needed.  Once you have built an autocomplete list (you will know when this is done if Excel tries to autocomplete cells for you) click on a cell that holds an autocomplete phrase.  

I keep track of when I write up the blog posts for shortcuts so Excel has started to autocomplete the dates for me.  For this shortcut to work a cell with an auto-completed entry must be active.

Step 2: Execute the shortcut:

⌥down arrow

 

Now the Autocomplete list will drop down and you can select an option, or just view what has been added to the list.

My autocomplete isn’t too interesting as you can see, but some lists can become rather large when you are dealing with multiple comments.  This is a great way of keeping track of duplicate comments and such. 

 

Skip to the edge of the current data region

The title seems daunting, but this shortcut is in my Top 5 favorite Microsoft Excel shortcuts.  What it does is scrolls you to the last cell with data in the row or column that you’re in.  Gone are the days of scrolling around your spreadsheet trying to find that last cell in the column.  Now you can execute this shortcut and be zoomed right to the end.  

For this shortcut you will need data in neighboring cells as seen below: 

Execute the shortcut.  In this instance I will execute the shortcut with the Up arrow to bring me to the cell with 1 in it, but it can be executed in any direction.  

⌘arrow

This shortcut is absolutely amazing for moving around your Microsoft Excel spreadsheet quickly and accurately.  You can jump from data groups to data groups as long as there is a space in between the 2 to stop you. Give it a try, it’s extremely easy to get use to!

VLOOKUP on Mac Excel

The VLOOKUP function, when mastered, is one of the most useful functions in Microsoft Excel.  A VLOOKUP is a function that works off the first column in a list of data.  When would you use a VLOOKUP?  When you are trying to pull specific data from a list into another cell.  For our example we will use an Invoice List: 

For a VLOOKUP to work you must have a unique identifier and that unique identifier must be in the first column of your list.  In this example our unique identifier is the Invoice Number.  Once the VLOOKUP is executed if we put XL_SKIN2013 in a cell with the function it will return to us all the information we want.  Please note the VLOOKUP function has no restrictions whether you want to pull information into the same spreadsheet, same workbook, or different workbooks.  

To start, we put our Unique Identifier into a new cell.  This will be the new list where we want to pull this information into.  

The cell next to XL_SKIN2015 will be where we enter the VLOOKUP formula.  Refer back to the Formula Builder Shortcut to open the Formula Builder.  

Type VLOOKUP in the Search for a Function and double click VLOOKUP to start the function: 

The formula builder will ask you for lookup_value, table_array, col_index_num, and range_lookup(optional).  For these values insert:

  • Lookup_value = the value to be found in the first column of the table, and can be a value, a reference, or a text string.  
    • If you have you click the white box next to the lookup_value you can then go to your new list (where you will be pulling information into) and select the Unique Identifier you have already inserted:

    • Table_array = Tell the VLOOKUP where the original database/list is.  Select the entire list for this. Do not include headers.  

    • Col_index_num = this part of the VLOOKUP is to specify which information from the original database or list we want.  In our second list we said we want the Type of invoice, so we put 3 for Column 3.  Please note the col_index_num is always the number of columns in from your database or list.  It does not matter where the list starts or ends in relation to the Columns and Rows laid out from Microsoft Excel.  
    • Range_Lookup = This is either True or False.  Use the following parameters to decide if you need a value in the Range_Lookup: 
      • If the first column of the database (the column that contains the unique identifiers) is sorted alphabetically/numerically in ascending order, then it’s possible to enter a value of true into this argument, or leave it blank.
      • If the first column of the database is not sorted, or it’s sorted in descending order, then you must enter a value of false into this argument
      • Our first column in the database is sorted so we do not need a value here.  

 

Press Enter and the information should appear in the VLOOKUP cell!

If you would like to add more information to the new list simply drag the VLOOKUP down and keep adding Invoice Numbers.  The information will automatically populate: 

 

After 5 years of separating Mac and Windows operating systems in Microsoft Office, 2016 closes the gap of shortcut compatibility.

Excel 2016 brings a few new features over its 2011 predecessor such as:
  • Recommended Charts
  • PivotTable Slicers
  • Analysis Toolpak
  • An improved Formula Builder
  • File compatibility improvements
  • Print options
  • Improved keyboard shortcut overlap
The last feature is, of course, the most important to us at Excel Skin. Before Office 2016, Excel shortcuts on Mac were only functional on Macs. Now you can learn Excel shortcuts on your operating system of choice, and the majority of these shortcuts will carry over if you find yourself using the alternate operating system.

If you have upgraded to Office 2016 on your Mac, now is the time to learn the new shortcuts! You can see a full list of shortcuts here, or use the Excel Skin’s color coded keyboard cover to teach you the most useful shortcuts in the least amount of time.

As always, feel free to email us or comment below with any questions you may have!

A few months ago we learned how to display the Find Dialog Box and search for text in your Excel workbook

Today you’ll learn the shortcut that repeats the last Find action without opening up the Find Dialog Box.  This shortcut saves you time and keeps you in your workbook.  It’s especially useful when you are fine tuning and editing a bunch of data.  

Example: You’ve just followed the “How to find things in a Microsoft Excel Spreadsheet” blog post and the last thing you searched for was your name.  The search comes up empty and you continue with your work.  At the end of your work session you want to search one more time for your name, just to ensure it is nowhere to be found in your workbook.  Instead of opening up the Find Dialog Box all you have to do is execute a one-modifier key shortcut.

The Find Next shortcut: 

(⌘G)

As you keep executing the shortcut Excel keeps cycling through the workbook to find all instances of the last searched text.  If there are no instances of the last searched text this dialog box will pop up telling you so.  

And there you have it.  The simple Find Next shortcut that can save you time by not opening the Find dialog box, and by not having to type what you are searching for over again.  

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.  

Have you ever wondered how to merge and center text in between two or more cells with a quick shortcut?  Unfortunately this shortcut doesn't exist, but in a few quick clicks you can have your text centered wherever you like.  This tip for Microsoft Excel on Mac can be useful in sprucing up your spreadsheet and giving it a more refined look.  
The trick is to make it look like the cells have merged together, but not actually merge them.  Merging the cells together can inhibit specific Excel functions.  
Step 1: Select the cells you wish to have the text centered in
Step 2: Click the Format menu on the toolbar
Step 3: Click Format Cells under the Format Menu. This will open a dialog box
Step 4: In the dialog box click the Alignment Tab (2nd Tab).  In the Alignment Tab select the following options: Horizontal - Center Across Selection.
Step 5: Click OK.  Your text will now be centered in the selected cells.  

Has your Standard Toolbar ever disappeared in Microsoft Excel for Mac? Have no fear, there’s a shortcut to get it back!  

Toggling the Standard Toolbar on the top of the Microsoft Excel window is a simple one modifier key shortcut.   Below is a snapshot of the Standard Toolbar including some other tools I added myself.  When we execute the shortcut the upper most portion of this screen will toggle to disappear.

Shortcut: ⌃7

And poof! It’s gone. 

Execute the shortcut again and it reappears:

And that’s it!  If you ever find yourself missing the Standard Toolbar simply execute this shortcut and it will reappear.  

X

This week we are sharing a useful resource from our friends at GraduateDegreeProgram.net.  They put together an "Excel Cheat Sheet" that goes over everything from the Basics to VLOOKUPs.  It's definitely worth checking out!  

http://graduatedegreeprogram.net/excel-cheats/