When I first started to learn how to use Excel Pivot Tables, I’m not going to lie, I was a little intimidated. At the time I really wasn’t certain about how and why I should be using them to analyze and summarize my data. Because I was a newbie I analyzed my data by literally manually selecting the cells that I wanted to summarize in combination with simple formulas like SUM and AVERAGE. At this point I was still unaware that the majority of the manual interaction with my data set could be eliminated with formulas like SUMIF and AVERAGEIF. I completely skipped over learning the SUMIF/SUMIFS and AVERAGEIF/AVERAGEIFS formulas and went right into learning pivot tables. I think it was because I was always intrigued and excited about learning an entirely new concept/application within Excel. Once you start to learn how to use pivot tables you’ll realize that all of the manual work is completely removed and you’ll notice your productivity sky rocket. I am going to show you 10 productivity hacks that can be applied to Pivot Tables in Excel 2010 / 2013 and definitely most can also be applied to earlier versions of Excel as well (one of the features may only be applied to Excel pivot tables 2013). (more…)
In business, I often see disputes between the finance and operations departments on which metric is a more important indicator of performance. Operational leaders (more often than not) like to see their numbers in terms of units/quantities and the finance executives/leaders want to see everything in dollars or currency. This is where the Excel option button form control comes in handy (note that if you are part of a finance team, you can also use this logic to switch between local currency versus your company’s global standard currency). I’m going to show you how you can use the option button to switch between quantity/units and currency all in one graph.
You know the saying, “a picture is worth a 1000 words”? Well, I’m hoping that this post can convey to you in a quick, easy and visual fashion how to create a pivot table. Please do not be shy and share it with everybody you know – or just post it on your social media accounts and maybe even your office cork board (do they still exist?). (more…)
As humans, we are always looking for smarter, better and quicker ways of doing things – especially the repetitive and often annoying day to day tasks. There are days where we quite literally wish we had a monkey by our sides to perform these tasks, allowing us to spend our time doing things that are of actual value. Thankfully Excel offers tab shortcuts that help us easily navigate between workbooks, change the name of workbooks at the drop of a hat and enter formulas, formatting and other Excel goodies onto multiple sheets all at the same time – can you believe it! In this post we are going to look at 3 smart and simple tab shortcuts to make our lives easier and really, just make our day way better. (more…)
I know what you’re thinking. What’s so incredible about number formatting? I can honestly say that this part of Excel can allow you to do some pretty amazing things with data display and this post might help you discover how to come up with some other impressive ways to display your numbers and values.
Let’s have a look at 5 incredible number formatting tricks that will help impress your boss – if you haven’t already impressed her/him enough.
Download the Workbook for Free Before we Begin
The Excel file is indeed free. When you click the ‘GET IT FREE’ button be sure to enter in $0 for the value and this will remove the request for your credit card information. All you’ll need is your email address!
Trick # 1: Display Large Ugly Numbers in Millions (M) or Thousands (K)
Alright we all know what happens, the big boss has a meeting with the executives and they need the numbers to be displayed in millions and they don’t care about seeing the dollars earned down to the penny. That means numbers like $100,583,230,05 turns into $100.6 M. Depending on the size of the numbers and dollars we are talking about, the executives may want to see the numbers in thousands so $5,520,300.90 turns into $5,520 K. Alright so what is the best employee ever to do, you’re going to convert the number format on all values into millions or thousands. As you may have already guessed, I’m going to show you how to do this.
How To Format Numbers In Millions and Thousands
Select the Data that you would like to change from a standard format to thousands or millions. In our case, we will select the 3rd column and then the 4th column and apply different formatting to each of them.
Select all of your data in the 3rd column (at this point it’s still displaying as the entire number in millions), and then type CTRL + 1. This will bring up the formatting screen. You’ll want to go down to custom and then on the right you’ll now be able to put in a custom format type.
$#,##0,, “M”
Once the custom number format has been typed in you can click OK. I’ll leave at least one link at the bottom of the post to a tutorial that explains the syntax for the custom number format. I will quickly say however that adding a comma after the zero will change our value to thousands and 2 commas after the zero to millions.
Select all of your data in the 4th column and then type CTRL + 1. This will bring up the formatting screen. You’ll want to go down to custom and then on the right you’ll now be able to put in a custom format type:
$#,##0, “K”
Once the custom number format has been typed in you can click OK.
Here is the Result:
Trick #2: Use the Conditional Formatting and Color Syntax To Change How Your Data Looks for Different Values
Being able to change the color and format of your values based on certain conditions is awesome to use when your developing KPI tables and charts. This allows you to quickly identify and provide the user viewing the spreadsheet with a performance visual for any given data set. In the example below, I’ll show you how you can check your month over month sales revenue change using conditions and colors within custom number formatting.
How to Conditionally Format and Add Colors to Values Using Custom Number Formatting
The first thing you’ll want to do with your data is highlight it. We’re going to highlight the column and values that we would like to format.
Now that we’ve highlighted our data set, type CTRL + 1. This will bring up our formatting screen. From here we are going to select the custom format type for numbers under the category section. From there we are going to put in a new custom number type. We will have three different sections to our number format which will be separated by semi-colons. Here we go.
1) This first section will only affect numbers greater than or equal to $50,000. We are going to make the text green and we’ll also make sure that the text is displayed in thousands with a suffix of K – to identify the number in thousands.
[green][>=50000]$#,##0,”K”;
2) The second section will only affect numbers that are between $-49,999 and $49,999. We are going to display these numbers in standard/regular format (not in thousands), with a dollar sign and we’ll use yellow to indicate that the month over month change was not great but was not something to be overly concerned about.
[Yellow][>=-49999]$#,##0;
(3) The third section will affect numbers that are less than $-50,000. Here we are going to want to put our numbers in red and for the fun of it we’ll add parentheses to indicate that the numbers are negative. Red from a sales standpoint will mean that our revenue decreased too much month over month and that something needs to be done.
[Red]($#,##0,”K”);
Here is the Result:
Trick #3: How to Use Symbols In Your Custom Number Formatting For Killer Looking Tables and Charts
We don’t often associate using symbols with Excel but in this instance we’re going to use them to show the change in our sales revenue and whether it is increase or a decrease of greater than or less than 50K. This technique of using symbols will allow the user to technically use any symbol that they find relevant to their chart or the data table/data set. Let’s take a further look.
How to Incorporate Symbols Into Your Custom Number Format for Data Tables and Charts
The first thing you’ll want to do is to insert the symbols that you’ll want to use into a cell anywhere so that you can copy them into your type field on the format screen.
Next step will be to copy (select one arrow and type CTRL +C) the arrows individually as you use them within the sections of your custom number format type. Paste the up arrow to the positive number format section and the down arrow beside the negative syntax section.
Now that we’ve pasted the symbols into your new custom number format type we will hit OK and we’ll see that our data range that we applied the formatting to is now updated with the arrows in the data set (depending on where the values lie).
Here is the Result in the Data Table and The Chart:
Trick #4: Use Conditional Formatting Icons With Words By Assigning Words to Values
This is definitely by far one of my favorites. Here you can assign words to values by using each section of the custom number format type. This means that numbers that are positive will display with one word, numbers that are negative will display another word and numbers that are zero will display as a different word. This is awesome for one reason: we can now use conditional formatting icons with text (or text disguised) (note: you can’t use conditional formatting icons with words as it only uses values – numbers and percentages).
How to Turn Numbers into Words and Then Use Icon Conditional Formatting In Excel
The first step of the process is to input a 1, a 0 and a -1 into three different cells as shown here.
The second step of the process will be to now highlight the cells with the three different values and type CTRL + 1 to bring up the formatting screen. We’ll now be able to assign each of the values a word using the custom number formatting type. Positive numbers, negative numbers and then zeros will each display with a different word. The syntax setup here is positive numbers; negative numbers; zero; text (here we are not going to use the text portion/section of the custom number format but we’ll see how we can use this in our next Trick). Below you’ll see we’ve applied the word “PASSED” to positive numbers, “FAILED” to negative numbers and “ON HOLD” to zero value numbers.
Note: Any numbers that are greater than 1 will show up as “PASSED” and any numbers that are less than -1 will show up as “FAILED”.
Here’s what our table now looks like:
Now we can highlight our set of data in our table and we can assign conditional formatting icons to our data because each cell has a number behind it but is simply displaying a word (this is kind of like a light switch: ON=1 switch OFF = 0). Here we are going to have the value of 1 be in green (“PASSED”), value of 0 will be in yellow (“ON HOLD”) and a value of -1 will be in red (“FAILED”).
Step 1 – Select the data that you would like to apply the conditional formatting to.
Step 2 – Select the conditional formatting option from the home tab and then select the stop lights.
Step 3 – Update your light value association. A value of 1 or greater will be green, a value of 0 will be yellow and a value of anything less than zero will be red. Make sure you change the type from percentage to number. Once you are done hit OK.
Here is the Result:
Trick #5: Use Custom Number Formatting to Validate Your Data
Sticking with our sample data from Trick # 4, we are going to use the last section of the syntax that is available in custom number formatting (which is how to display values that are text) to use as data validation for the set of cells from which the data entry will occur. In plain English, all we want to do is to update our cell value to say “Please enter a Number” if the user enters text (as opposed to numbers) in any of the cells.
How to Use The Text Section of Custom Number Formatting as Data Validation
The first step of the process will be selecting the data that we would like to apply the data validation to. Here it will be the values next to each of the project #s.
Once we’ve selected the data, hit CTRL + 1 to bring up our formatting screen. Now that the formatting screen is up, we are going to add the text “Please Enter A Number” after the 3rd semicolon in our syntax.
Click OK once you’ve added the text section to the syntax for our custom number format type. Below you’ll see the results.
Here is the Result:
Summary
Here’s a brief summary of the post to quickly go over what we learned. You should be able to use these tricks to come up with new ideas and ways to use customer number formatting in your day to day operations.
Trick # 1 – Use 1 comma after the 0 to turn numbers into thousands and 2 commas to turn numbers into millions. Use quotes around letters to get them to display within a cell (“K” and “M”).
Trick # 2 – Conditional statements and text color go between square brackets in number formatting syntax ([Red], [>=50000]).
Trick # 3 – Paste symbols into your custom number format by inserting them into a cell and then copying them. Your symbols will get displayed on your charts.
Trick # 4 – Assign text to numbers by using quotes around text for each section of the number formatting syntax. Apply conditional formatting to the numbers that have been disguised as text.
Trick # 5 – Use the text section of the custom number formatting (after the 3rd semicolon in the syntax) to place data validation in cells that you do not want users entering text into.
These tricks should help you get an understanding of the general functionality behind custom number formatting and may help you explore other options on how you can use it to make your day a little more efficient. I’ve included some additional resources below just to help you out if your not fully in tuned with what and how custom number formatting truly works. Make sure to leave a comment below letting us know which custom number formatting trick you find the most useful and also how you think you can use it.
