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

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!

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!

1 2 3 8 Next »