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

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.

 
1 2 3 Next »