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: 

 

I steered clear of creating pivot tables on Microsoft Excel for many years.  Simply hearing the phrase "pivot table" in the office made me run the other way.  I didn't even know what they did, they just seemed extremely complex and daunting.  

I was eventually asked to create a pivot table and within the hour my fears were gone.  With Microsoft Excel for Mac, creating a pivot table is easily achieved in just a few steps using the toolbar.  No formulas or shortcuts necessary! 

Step 1: The original data. For this example our data for the pivot table will be in Sheet 1.

 

Step 2: Select the cell where you want the pivot table to appear. We are going to use A1 on Sheet2.

Step 3: Select the Data tab from the toolbar in Microsoft Excel.

 

Step 4: Click the small arrow next to the PivotTable icon and select the Create Manual PivotTable… option.

Step 5: The Create PivotTable dialog box will appear. Here we must select the original data table on Sheet1 for the “Use a table or a range in this workbook” Location: box.

To do so, click on Sheet1 and highlight your table:

 

This will auto-populate the information into the Create PivotTable dialog box. Click OK.

 

Step 6: Now the Create a PivotTable box will close and a PivotTable builder will appear.

 

In the upper portion labeled Field name, select the fields you wish to add to your pivot table. I have selected Order Number, Product, Unit Price, and Quantity.

I then moved Order Number from the Values box to the Row Labels, because I do not need a value for the Order Number.

Whenever you are satisfied with your pivot table, that should be changing in the background as you modify the options in the PivotTable builder, click the X to close the builder dialog box.

Step 7: Edit the cell with Row Labels to the correct terminology: In my example that would be Order Number.

And there you have it! A quick and simple way to make beautiful pivot tables for Microsoft Excel on Mac. Please let us know if you have any questions!

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!

Travel Budgeting:

Excel provides a great platform for budgeting just about anything. As a travel addict, I love to use Microsoft Excel on my Macbook Air to track and budget my expenses. Since the Macbook Air is so light, it accompanies me on almost every trip I take, making it the most convenient and ideal place to manage my budget.

The first step is, of course, to have an idea of how much you would like to spend on the trip. I have a known tendency to always underestimate the amount of money I am going to spend…it’s a constant struggle between budget versus reality on my travel adventures.

For this example I am going to allocate an arbitrary $135 per day: $20 for transportation, $50 for housing, $35 for food, $20 for activities, and $10 for gifts.

The next step will be to create an easy to use template in Microsoft Excel so that our expense tracking runs as smoothly as possible. The method that works best for me is to have a daily row, which you update once a day (usually at night). Below is my go-to template for travel budgeting:

 The best part about this template is hidden in the picture above. In column G, the Total column, our budget shows $0.00. When we begin to input our expenses in the designated rows column G will automatically add the expenses together and give you the total sum.

This is due to a formula in column G that adds any number added into columns B – F. This formula can be inserted by typing =SUM(B4:F4) in the cell in column G. Please note the cells in the parenthesis must be for the cells you are going to input only numbers in.

Once you have completed a full week of traveling, and a full week of budgeting, you can add the Total Spent (Weekly) at the bottom. To do so, insert the same formula as the daily sum, but edit the cells in the parenthesis to only include the totals in column G:

 

Below we have a full week of expenses correctly logged:

 

We came in at $27 under budget, even though on Thursday we took part in a $100 activity and spent $30 more on Gifts than we originally budgeted.

The great thing about budgeting in this template is we can see all the days in one concise spreadsheet, and evaluate our activities per day and per week. I like the simplistic nature of this budgeting template, and while some spreadsheets can be very technical and advanced, this template is easy to use for every Microsoft Excel user.

 

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.  
1 2 3 Next »