The Excel Skinny

A blog about Excel and its users

The Paste Special shortcut can be used for a plethora of reasons.  I find myself using it most often when copying and pasting between two windows that do not share the same formatting.  

I can copy data in one format, and then paste into another document by using the Paste Special – Values Only option, which will only bring the values over and automatically match the format of my second sheet.  

Step 1: Copy data.  To do this execute the Copy shortcut, Command + C (⌘C).  

Step 2: Now that we have our cell copied, scroll to where you want to Paste this data and execute the Paste Special shortcut.

⌃⌘V

Step 3: A pop up will appear asking you what sort of Special Paste you wish to execute:

I am most familiar with the Values option as this only brings over raw values and nothing else, but as you can see there are many other options provided.  For this example I will stick with my favorite, Paste Special – Values:

Hit Enter or click OK and your data will Paste!

Since I chose Values only the data Copy Me was pasted.  The font formatting was not transferred at all.  

This is a great tool when dealing with multiple spreadsheets or documents that have different formatting.  It ensures the data you are pasting matches the data already in your spreadsheet.  

X

The Select All shortcut is by far the most commonly used shortcut for selecting all the cells in an Excel spreadsheet, but what happens when you have hidden columns and rows that you don’t want selected?  You use the select only visible cells shortcut of course!  

The select only visible cells shortcut will perform the same action as Select All, but it will ensure only the cells you can see on the spreadsheet are selected.  

This is useful when dealing with hidden rows or columns that don’t necessarily need to be selected.  Perhaps you are changing the format of your entire spreadsheet or copying only the visible cells to another spreadsheet that doesn’t need all those hidden columns.  The select only visible cells shortcut has a place in your Mac Excel shortcut repertoire!

Step 1: This shortcut can be executed from anywhere on the Microsoft Excel Shortcut:

⇧⌘Z

And boom!  All visible cells in your Excel spreadsheet are now selected.  From here you can copy the selected cells to paste to another spreadsheet, change the format of all the visible cells, delete everything, or anything else you want to do!  

Dragging your mouse around to select cells is tedious and can result in some pretty wild failures due to Excel's sometimes erratic scrolling.  The preferred method of selecting cells is to do so with the arrow keys, one cell at a time.  That way you can be accurate and efficient, without making a mistake.  

Select the cell you wish to start your selection from.  This is usually one end of the array of data or the other.  Starting in the middle will only add unwanted steps to the process.  

 

From here I want to select only the first 5 cells.  

To do so, hold the Shift key and tap the Down Arrow until the cells you wish to select are all highlighted.

Now I can copy, cut, change font, or anything else I want to do with these 5 cells and I never had to take my fingers off the keys!  

Feel free to comment on any Excel on Mac questions you may have!  

 

Shading your rows with alternating colors is an easy way to make your spreadsheet more legible and less confusing.  There are 2 ways of doing this on Excel for Mac.  

Using the AutoFormat feature completes this task, but if you end up deleting a row your spreadsheet becomes an uneven mess. AutoFormat does not automatically correct the shading to alternate every other row so you end up with 2 rows shaded next to each other - never good.

The more accurate and preferred way of shading every other row is through Conditional Formatting.  Basically we are going to have Excel for Mac calculate if the row is even or odd, and shade them accordingly, so if you delete a cell the shading will shift for the entire worksheet.  

 

How to:

  • Select the Range of Cells you want to shade.  I usually select the entire worksheet by using the Command + A, Select All shortcut 
  • Now select Format – Conditional Formatting from the upper toolbar
    • This pops open the Conditional Formatting Dialog Box
    • Press the + Button on the bottom left hand side to open the New Formatting Rule Dialog Box.  
      • Change the options in this box to the following (In this order):
      • Style: Classic
      • In the second drop down: Use a formula to determine which cells to format 
      • Type in: =MOD(ROW(),2)=0
        •  This tells Excel for Mac to check if the row is even.
      • The Format With option lets you choose the color the shading will be.
    • Click OK and you will be brought back to the original Manage Rules dialog box with your rule now applied.
    • Hit OK again, and your spreadsheet will be striped!  

 

And that’s how to apply a color to alternating rows in Microsoft Excel for Mac!  Let us know if you have any questions or Excel for Mac tip suggestions! 

#Excel

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!

Think Excel doesn’t belong on Macs? Think again!

The first version of Microsoft Excel rolled out in 1985 exclusively on Macs, with the Windows version coming two years later in 1987. Microsoft’s new program, Excel, pushed out the at-the-time popular Lotus 1-2-3 program and solidified Microsoft’s role in the software world.  Microsoft Excel featured an intuitive interface and the ability to perform complex calculations with graphing tools. In 1993 Microsoft decided to bundle Excel with the Microsoft Office Suite, effectively confirming its dominance in the spreadsheet world.

Here is a short video of Bill Gates unveiling Microsoft Excel for Windows in 1987: Link to Video 

Microsoft continues to add features and upgrades to Excel every two years or so since 1985.

Below are images of Excel in its earlier years. As you can see the program hasn’t changed much visually, but the tools and computation ability has vastly improved throughout the years. 

Microsoft Excel 1.06:

Microsoft Excel 4.0:

Microsoft Excel 95: