The Excel Skinny

A blog about Excel and its users

Selecting multiple cells with the Mouse in Microsoft Excel for Mac can be an inaccurate and messy affair.  Thankfully, with the help of the Excel Skin, we don’t have to use the mouse. We can keep our hands on the keyboard and select our cells with accuracy using the Shift and arrow keys.  

Here is our data: 

I’d like to select George, Anna, Brian, and David without using my mouse at all, how do I do it?  

First scroll to one of the cells you wish to select with the arrow keys.  

Now to select the others hold the Shift key and use the arrow keys.  

⇧arrow

I am going to click the down arrow once to select Anna, then the right arrow once to select Brian and David:

And there you have it!  I’ve selected the 4 cells I wanted to accurately and without using the mouse. After all, you can never have enough keyboard!  

Anytime you have column headers on your spreadsheet it is a good idea to make them visible at all times.  This helps you clearly see what the column is representing without having to memorize the headers.   There are no reasons to not use this trick, so here we go!  

Select the row immediately under the row you want to freeze at the top.  For example if you have row 1 filled in with the headers, select row 2. 

Now click on the Window menu bar at the top of your screen:

And select the Freeze Panes option 

Excel will place a bold line under the frozen column headers.  These headers now go everywhere with you until you go back to the Window menu and click on Unfreeze Panes.  

 

Notice Row 1 and Row 2 in the screenshot.

Now I will scroll down to Row 777 and we will find Row 1 right above it.

And there you have it! Now your column headers can follow you wherever you may travel on your spreadsheet.  

 

For any questions or comments feel free to email me directly at George@ExcelSkin.com

Hyperlinks can be used in a plethora of ways, but my favorite way to use hyperlinks in Microsoft Excel spreadsheets is to create a main page that I use as a “table of contents”. This eases the pain of scrolling through worksheets and trying to find a specific page. Let me show you what I mean: 

Pretend we have a list of customers. Instead of having that data all on one worksheet, it would be much easier to have a designated worksheet for each customer. The problem is when you reach a large number of customers, scrolling through the worksheets to find a specific customer will become tedious. This is where the hyperlink shines. For this example I will use only 5 customers to demonstrate the effectiveness of hyperlinks in Microsoft Excel for Mac.

First we must create our main page, and a designated worksheet for each customer:

 

The first worksheet will be the list of customers as shown above, with the worksheets at the bottom. 

Our next step is to create the hyperlinks. Select the cell of the first customer, Customer 1 in this example. Using our Excel Skin, execute the Hyperlink shortcut found on the K key.

Now the Insert Hyperlink dialog box will appear.

Select the tab 'This Document'.

Then click the arrow next to Cell Reference and select the customer this cell will hyperlink to. In this instance we want to hyperlink to Customer 1. Click Customer 1 and then OK. 

Now the cell will be underlined and blue:

 

When we click Customer 1 on the customer page, it will bring us to that customer’s page.

In this instance it is not so useful since Customer 1 is right next to the main Customers page, but when you are dealing with several hundred customers the hyperlink shortcut in Microsoft Excel on Mac becomes invaluable.

Now try to hyperlink the other 4 customers! Let us know if you have any questions, the Excel Team is here to help!

To check out 23 gifts for grads, including the Excel Skin, click here to read the BuzzFeed article.

In previous blog posts we learned how to use the VLOOKUP and HLOOKUP functions to find exact matches in a table. Today we are going to learn how to use the VLOOKUP function to find the closest match, instead of an exact match.

This is accomplished by changing the last part of the formula syntax, [range_lookup].  Setting this argument to FALSE returns only exact matches, but if set to TRUE, the returned value of the function will be the closest match.  Note, the table you are pulling data from must be in ascending order for the closest match to work correctly.

When would you want the closest match rather than an exact match?  Calculating grades is a great example for the closest match VLOOKUP. Keep in mind is the VLOOKUP matches the value that is less than or equal to the lookup value.  

 

So we have a score of 81, but we want to know the actual grade:

This searches the Scale table and finds that 81 is closest to a C, so it returns the letter C in the cell that we added our formula. 

 

And that's all there is to it!  Now you can use the VLOOKUP function to search for exact matches, or closest matches in your spreadsheet.

 

As always, please let us know if you have any questions! 

Have you noticed the window that pops up when you open Microsoft Excel? I usually ignore it and click on the blank “Excel Workbook” or just click the Cancel button so I can get started as fast as possible.

Lets take a second to explore the options of the Excel Workbook Gallery.

In the template list there is a plethora of different pre-loaded options.

Time Management Templates:

  • Multi-Page Calendar
  • One Page Calendar
  • Pros vs Cons
  • To-do List 

Business Essentials Template:

  • Consultant Time Tracker
  • Customer List
  • Inventory Tracker
  • Services Price List
  • Weekly Time Sheet 

Personal Finance Templates:

  • Household Budget
  • Loan Calculator
  • Wedding Budget

Business Finance Templates:

  • Business Budget
  • Expense Report
  • Invoice
  • Marketing Budget
  • Twelve Month Cash Flow
Graph Paper Templates:
  • 10x10 Grid
  • 5x5 Grid
  • Plain Grid Large
  • Plain Grid

Out of these templates, the Excel Skin team has a few favorites.

 

The Consultant Time Tracker is an awesome pre-made time sheet. You can even use this as a simple employee time tracker or as an invoice for customers.

 

The Household Budget is a bit more complex than I could handle for a budgeting spreadsheet, but if you want to keep track of all your spending with pre-made graphs included this is a gem.

Last but not least, everyone needs to keep track of wedding spending. The Wedding Budget template lets you add your desired budget, and even creates graphs on overall estimated costs. Don’t let your wedding go overboard!

Explore these templates and see how they can help you. Template creation is one of the biggest time consumers when building out your processes for business or personal use. 

As always, please let us know if you have any questions

The Excel Skin team is here to help!

Overview

In order to use all of the available shortcuts within Excel 2011 (or any other application), you must turn off a few default operating system shortcuts.

 

Follow these simple steps to set up your Mac for Excel.

Go to: System Preferences

Click: Keyboard


 

On the left side, there is a list that includes Launchpad & Dock, Mission Control (only in recent operating systems like Lion and Mountain Lion), Keyboard & Text Input, et cetera. 

Select Mission Control. On the right you’ll see a list of check boxes. Uncheck any the Mission Control group, Show Desktop, Show Dashboard. If at any time you want to revert to the original settings, you can return to this menu and click “Restore Defaults.”


 

While you are in the Keyboard menu, check to make sure the Radio Button next to “All Controls” under Full Keyboard Access is selected. Proper use of this setting will help you reduce your use of the mouse. 

To learn more about navigating from the keyboard, be sure to check out our upcoming post, You Gotta Have More Keyboard Baby.

 

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: 

 

1) Select All with a click

Instead of executing the Select All shortcut, there is a simple button built into every spreadsheet that will select all data in that spreadsheet.

 

2) Using the Formula Builder

We can’t always remember every formula in Excel, which is why we have the Formula Builder! With the Formula Builder you can search for specific formulas by name, insert the function, add your data, and receive any assistance through the Formula Builder. Open the Formula Builder by clicking the fx Insert button in the Formulas tab, or by executing the Formula Builder shortcut.

You will be able to search categorically when clicking the button in the Formulas tab.  

3) Remove Duplicates

Excel is one of the best programs for removing duplicate values. All you must do is copy your data into an Excel spreadsheet, and click on the Remove Duplicates button. I found this especially useful when I found out I had duplicate .jpg files taking up space on my computer, Excel identified and helped me remove those files with a click.

How do we do it? Select the data you’d like to analyze, then click the Remove Duplicates button in the Data tab.

A dialog box will appear telling you how many duplicates were found. Click Remove Duplicates in the bottom right to erase these duplicates from the spreadsheet.

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!

1 2 3 4 Next »