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

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

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!

As you may know, Microsoft Excel is a great way of keeping track of basically anything. You can track your workouts, travel plans, packing list, holiday shopping, or even your diet.
In this blog post I will show you how I use Excel to track the type of Customer Support tickets I receive on a weekly basis.
My first workbook is the data center. In this workbook I add the number of times a criteria is applied to a label compared to the total number of tickets.

The next step is to make a chart for those that are a bit more visual. The second tab will be your bar graph, and the third tab will be the pie chart. The bar graph is a nice and easy graph for the number of times a label is applied, and the pie chart is more suitable for the percentages.


To set up a chart, click on the Charts tab in the toolbar.

This will bring you to a list of charts that you can create. Click on the one you want. In this case, I will do a vertical bar chart, or Column.

What appears is a blank screen where the chart will be.

Now we must designate the data we want in the chart. To do so, right click the blank window and click Select Data.

This will open a Select Data dialog box.

In this dialog box, we want to designate the data with the Chart data range: space. Click in the blank space, and then go to your data and select the range of data you wish to select.

Click OK when you are done selecting the data, and your chart will appear.

Please let us know if you have any questions, the Excel Skin team is here to help!

If a function key doesn't work as you expect it to, press the FN key in addition to the function key. If you don't want to press the FN key each time, you can change your Apple system preferences:

  1. Go to Apple > System Preferences > Keyboard.

  2. On the Keyboard tab, select the check box for Use all F1, F2, etc. as standard function keys.

    NOTE   This check box affects how the function keys work for your entire computer, not just Excel. After selecting this check box, you'll need to press the FN key to perform the special features printed on the key. For example, you'll notice that the F2 key has a brightness icon. To use the key to change your brightness, you'll need to press FN + F2.

Moving can be a daunting task regardless of the size of your house or apartment. There are hundreds of little tiny objects that do extraordinary things that you’ll want to accompany you in your new abode. What’s the best way of keeping track of these objects? Microsoft Excel of course!

Using Microsoft Excel for moving has two significant competitive advantages over a simple word document or text based list. You can add the values of each item in case of damage, and you can easily check off items that arrived safely

The first step is to list out your items and the values as shown below:

The next step is to, of course, move these items. Once you have all the boxes in your new home you can start sorting through your items. 

The easiest way of doing so is a color coded system. When you unpack an item, color code it in Microsoft Excel to confirm you have received it.

By the end of all your unpacking you’re Excel spreadsheet should look something like this:

Hopefully you have mostly green!

And there you have it, the most organized and efficient way of tracking your household items during a move in Microsoft Excel.

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

Not all things in Microsoft Excel for Mac require a fancy shortcut. Sometimes Microsoft makes our lives much easier with the ever-improving toolbar. The toolbar is that confusing looking thing above your worksheet that looks like this:

As you can see there are multiple tabs on the toolbar labeled Home, Layout, Tables, Charts, SmartArt, Formulas, Data, and Review. For our tip today we are going to be living in the Review tab.

Once you click on the review tab a new set of icons will appear.

The first icon is to check spelling and grammar. Something that is not often required in Microsoft Excel, but if you need it, it’s there. The second icon is what enables us to insert a comment into a cell in our Microsoft Excel spreadsheet.

Now select the cell you wish to insert the comment in.

Once the cell is selected, click the 2nd icon on the Review toolbar labeled New. This will pop open a comment box.

From here you can insert any information you want about the selected cell.

 

Once you click away from the sticky note, the comment will be hidden in the cell so it does not disrupt your spreadsheet too much. To reopen the comments simply slide your mouse over the red triangle in the upper right hand side of the cell and your comment will appear.

You will notice now all of the comment icons on the toolbar are available to use, where as before inserting a comment only the New comment icon was clickable.

Comments are a great way of adding hidden information to cells. The comment will only pop up when you want it to, and you can reduce clutter in your spreadsheet.

Microsoft Excel for Mac has a few obvious uses. A few blog posts back we covered budgeting in Excel, and how easy it is to set up a simple to use template that does all the calculations for you while you input the data. 

Today we are going to tackle and even simpler use for Microsoft Excel on your Mac. Data Lists. The layout of any spreadsheet is very advantageous when dealing with lists of information. Anything from addresses to contacts to inventory can be listed in an easy to use format in Microsoft Excel for Mac. 

Why is it easier than Microsoft Word, a dedicated word processor? Lets take a look at some data lists in each program and compare.

Here is our list of inventory for our Restaurant Supply store in Microsoft Word:           

And here is the same list in Microsoft Excel:

In my opinion the Microsoft Excel list is easier to read, but that is not the main benefit. The benefit comes from the ease of manipulation. Since our products are in individual cells we can easily remove them, or format them depending on our current inventory. In the Microsoft Word example the list is more of a sentence, and much harder to manipulate without editing the entire entity.

If you like keeping lists of data, but are currently using Microsoft Word, give Microsoft Excel a try! I promise you will find it easier after a few clicks.

And as always, let the Excel Skin team know if you have any questions!

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.