The Excel Skinny

A blog about Excel and its users

Excel 2016 attempts to bridge the gap between Excel for Windows and Mac.

As many of you may have noticed, the vast majority of the shortcuts mirror each other on both operating systems. With this gap shortened a bit, here are a few tips that will make your life easier in Excel for Mac 2016. 

Formula Builder:

Formula Builder received a nice upgrade.  Instead of remember syntax, now you can simple click Shift + F3 to pop open the Formula Builder side bar.  This pop up will help you find the exact formula you'd like to insert, as well as walk you through the syntax step by step.  

 

Full Shortcut List:

As mentioned above, Windows and Mac key assignments are now quite similar.  Shortcuts like Ctrl+O for Open, Ctrl+F for Find and Ctrl+C for Copy now also work in the Mac version—no need to remember to press Cmd instead of Ctrl. If you want to clear the content of the selected cell or range, just press the delete key. This even works on your Mac laptops, where delete is actually the backspace key. Some popular shortcut keys are listed below; a complete list can be found here.

Touchbar Support:

The new Touchbar is now supported in Office for Mac 2016!  

Typing an equals sign into a cell in Excel immediately pulls up the most recently used functions in the Touch Bar. For example, with a tap (for the formula) and another tap (for a named range) in the Touch Bar, you can quickly sum a range in your spreadsheet. The Touch Bar also provides quick access to borders, cell colors and recommended charts—making it easier than ever to organize and visualize your data.

 

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

 

How do you compare data in Microsoft Excel? Logical Formulas!

Excel uses logical operations to ease the pain in comparing data. These formulas will return what we tell them to, based on specific criteria.

We will teach you how to run less than, greater than, and equal to formulas below.
For the first formula, we will focus on the “Greater Than” formula. This is used when you want to isolate cells that contain values greater than the parameters in your formula.
For the example below, I have a series of numbers between 1 and 10. All I want to know is if the number is greater than, or less than 2.

As shown above, my formula is: =IF(B1>2,"Greater","Less") for the first cell. This is telling Excel, if the data in B1 is greater than 2, input the word “Greater”. If it is less than 2, input “Less”.  I then pulled this formula down by the square in the lower right hand side of the D1 cell to fit my list.  

This is a very simple way to use the =IF logical formulas, but they can become extremely complex if needed.

 

As always, please let the Excel Skin team know if you have any questions!

 

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.