The Excel Skinny

A blog about Excel and its users

Microsoft Excel provides the perfect platform for an on-demand pricing calculator.  Instead of fumbling with your calculator while you're on the phone with a prospect, I will show you how to create a pricing chart that automatically adjusts all possible options based on just a few cells. 

In this example I will assume we are charging on a per-seat basis, similar to most SaaS companies.

List price will be $50/core seat, and $25/ lite seat, but I am willing to discount up to 20% off.  I will setup my spreadsheet so that all I have to do is adjust the number of seats, and the rest will be calculated for me. 

Here is my simple price calculator.  The only thing I have to change on each of my calls is the seat number, and the Totals per Month will automatically re-calculate.

Now, lets reveal the formulas in each cell.

 

The Standard pricing formula is straight forward.  All it is doing is multiple the number of Core Seats by $50, the number of Lite Seats by $25, and adding the two numbers together.

The discounted pricing formulas do the same thing, but they first calculate the discount and then subtract that from the Standard Pricing row.  

There are a few different ways to accomplish this calculation so feel free to play with the formulas as needed.  

And there you have it! A complete pricing calculator that only needs you to change the seat numbers to update completely.  You can set this up for unlimited possibilities in discounts or upsells to save you time and money in the future.

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

 

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!

After 5 years of separating Mac and Windows operating systems in Microsoft Office, 2016 closes the gap of shortcut compatibility.

Excel 2016 brings a few new features over its 2011 predecessor such as:
  • Recommended Charts
  • PivotTable Slicers
  • Analysis Toolpak
  • An improved Formula Builder
  • File compatibility improvements
  • Print options
  • Improved keyboard shortcut overlap
The last feature is, of course, the most important to us at Excel Skin. Before Office 2016, Excel shortcuts on Mac were only functional on Macs. Now you can learn Excel shortcuts on your operating system of choice, and the majority of these shortcuts will carry over if you find yourself using the alternate operating system.

If you have upgraded to Office 2016 on your Mac, now is the time to learn the new shortcuts! You can see a full list of shortcuts here, or use the Excel Skin’s color coded keyboard cover to teach you the most useful shortcuts in the least amount of time.

As always, feel free to email us or comment below with any questions you may have!

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!

Microsoft Excel for Mac doesn’t automatically underline incorrect spelling.  This is because formulas are usually abbreviations of words, which would cause almost every cell with a formula to be underlined. There are times that spell check is very necessary though. Note: Excel will not correct grammar like Word, but it will find those misspelt words. 

When your Excel spreadsheet is going to be in a presentation or handed into a professor it’s best to do a spell check on the entire workbook just incase. 

The shortcut to spellcheck is a simple one modifier key shortcut. 

With any cell selected press fn + F7:

This will open the spell check dialog box.  From here you can review all spelling mistakes in the worksheet and correct them if needed. 

If the correction is not needed click Ignore to move on to the next incorrect word. 

Click on one of the Suggestions and then Change to change the selected word to the Suggestion

Click Add to add the word Excel marked as misspelt to the dictionary so it does not select the word as incorrect in the future. 

Ignore All will skip over every instance of the misspelt word in your worksheet.  For example, if I clicked Ignore All on the dialog box above all cells with asdf would be skipped.  

Change All will change every instance of the misspelt word to the word chosen in the Suggestions box.  

Has your Standard Toolbar ever disappeared in Microsoft Excel for Mac? Have no fear, there’s a shortcut to get it back!  

Toggling the Standard Toolbar on the top of the Microsoft Excel window is a simple one modifier key shortcut.   Below is a snapshot of the Standard Toolbar including some other tools I added myself.  When we execute the shortcut the upper most portion of this screen will toggle to disappear.

Shortcut: ⌃7

And poof! It’s gone. 

Execute the shortcut again and it reappears:

And that’s it!  If you ever find yourself missing the Standard Toolbar simply execute this shortcut and it will reappear.  

X

As a daily Microsoft Excel user I make it a point to avoid using the mouse at all cost.  Not only does it slow you down but it often messes things up.  The mouse is a clumsy and ancient tool that is slowly going extinct.  What’s the cure? More keyboard!  

 

Today we will discuss the simplest of shortcut - how to edit a cell.  I know what you’re all thinking – just click on it.  Wrong!  Clicking on a cell and typing will delete anything already in the cell and overwrite it with your new inputs.  Well then why don’t we double-click it? Double-clicking will enable you to edit the data in the cell but it often places the cursor in random spots and is a precursor for mistakes.  

 

So what do we do? Use your keyboard of course  

 

Scroll to the cell you wish to edit with the arrow keys: 

Now that we have the cell we wish to edit active we can execute the shortcut to edit:

⌃U

 

This brings us into the active cell and places the cursor at the end of all the data.  From here we can scroll and make our modifications:

With one click of the Enter key the changes are saved in the active cell.  

And there you have it, another simple one modifier key shortcut that aids in the war against the mouse.  Once you start using the Edit Active Cell shortcut you’ll never double click another cell again.  

Repeating the Last Action

At the beginning of the summer we went over how to undo the last action executed in Microsoft Excel for Mac (http://www.excelskin.com/blogs/the-excel-skinny/7853375-undo-the-last-action).  Today we will learn how to “repeat the last action” – basically how to undo the undo.  

So lets say you just made a big mistake and you hit the Undo shortcut (Command + Z).  Then you realize it wasn’t a mistake and you shouldn’t have executed the Undo shortcut, this is where the Repeat Last Action comes into play.  We can execute the simple 1 modifier key Repeat Last Action shortcut and your Undo is reversed.  

 

Data:

 

I highlighted random cells by mistake:

 

 

So I execute the Undo shortcut (Command + Z) 

 

 

And the highlight goes away.  But 2 seconds later I decide I actually like the highlight.  Now I execute the Repeat Last Action Shortcut.  

 

⌘Y

 

And our highlighted fields are back! 

 

This shortcut can save you a bunch of time from having to retype or redo the last action you mistakenly reversed with the Undo shortcut.  

The 3 most used font formats in Microsoft Excel are Bold, Underline, and Italic.  It is a must that you know the shortcuts to use all 3 without a second thought.  Let me teach you how. 

Click the cell you wish to format.  Note: if the cell already has data the data will be changed. If the cell is empty and anticipating data anything typed into the cell will have the formatting you applied to it.  

 

Bold Shortcut: 

⌘B

 

Underline Shortcut:

⌘U

 

Italic Shortcut:

⌘I

 

And there you have it! The cells have been formatted to match their names

These are simple one modifier key shortcuts that can help you tremendously with formatting an Excel spreadsheet.  

X
1 2 3 Next »