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.
I know what you’re thinking. The title of this post is a sad attempt at stringing together an English phrase that actually makes sense (i.e. Excel Formulas Not Working). To that I say, you’re absolutely right! Nevertheless, it seems that the majority of people out there are searching for this exact statement on Google. For that reason, I am going to leave it and I’m also going to try to put together a comprehensive series of blog posts that can answer this question – or this statement rather. We’ve all come across the dreaded situation. We put together a massive spreadsheet and start looking at the results of our calculations and formulas only to realize that the results are incorrect. Now you’re thinking, well what is the problem? In this first of several posts we are going to look at what Tech Republic says is the most important thing to teach your Excel users and that is absolute and relative cell referencing. This post will give you a solid idea of why your formulas may be failing or may just be not giving you the results you expected and at the same time cover the basics of absolute and relative cell referencing so you can understand what the heck you’re doing.
Relative, Mixed and Absolute Cell Referencing: The Reason Why Your Excel Formula Didn’t Auto Fill or Copy As You Expected
I know sounds brutally boring, right? It kind of is really; but if you understand how this works, you’ll save yourself a lot of time trying to figure out why your formulas aren’t producing the results you were expecting. This is one of the first things you want to learn about Excel before you start using formulas as it will save you tons of time in the future. Alright let’s get into the less than exciting details and then look at the fun stuff.
What Exactly Does Cell Referencing Even Mean?
Literally and in the most laymen terms cell referencing means to reference a cell from your current cell. This allows you to make the contents of a cell dynamic meaning if someone updates cell A1 and you are referring to that cell in cell C5, cell C5 will update to reflect the changes made to A1. Let’s look at an example and check out the step by step process.
A STEP BY STEP HOW TO ON CELL REFERENCING
(1) Notice that our formula in cell F3 is (=D3*E3 – here we are referencing cell D3 and E3) – which simply translates to multiply the contents of D3 by E3 (225 * $68.60). The result in cell F3 is then $15, 435.67.
(2) The beautiful thing about cell referencing is that now if we change the value of one of the cells being referenced (let’s say the on hand quantity- column D), this will change the result in cell F3 automatically. Notice that the value in cell D3 is currently 225.
(3) Let’s change the value of cell D3 to 200 – notice the red circle for point 3 that the value has now changed.
(4) Now that we’ve updated cell D3, because the formula in F3 remains the same (D3*E3) our result will be dynamic and change to reflect the new multiplication of values between D3 and E3 (=200*$68.60) which gives us $13, 720.59.
That is cell referencing in a nutshell. Instead of doing a multiplication of two values and re-populating cell F3 every time based on the changes that are made for cell D3 and E3, we can easily refer to those two cells so that we don’t need to manually calculate the change in our inventory value.
Alright, now that we’ve covered cell referencing in general, let’s look at the different types. We’ve got relative, mixed and absolute cell referencing.
Relative Cell Referencing
By default your formulas use relative cell referencing (it’s important that you understand that this is the default setup in Excel and that there will be times where you will need to make your formula not relative). What this means is that if you copy your formulas to other cells the references will simply be offset exactly by the same number of rows and columns that you moved your formula (Microsoft did a great job at explaining this here).
BREAKDOWN OF RELATIVE REFERENCING
(1) Enter your formula in cell F3 (=D3*E3).
(2) Hit Enter to process the formula.
(3) Use small square in bottom right hand corner of cell F3 to copy/auto fill formula to next cell (drag).
(4) Now that you are on cell F4 – the formula bar shows that the formula was copied over.
(5) Notice that the column and the row of each cell reference is offset exactly by the number of cells that we moved and copied the formula (F3 to F4 – 1 row down to row 4 – D4 * E4).
Mixed Cell Referencing
When we are talking about mixed cell referencing, here we are referring to both relative and absolute referencing in one formula/cell. This means that a part of the formula will always be referencing a specific range or a cell when copied or moved (absolute referencing) and part of the formula will be referencing a dynamic or a changing range or cell (relative referencing). As an example, as you copy and paste your formula, you may want to always refer to column A but at the same time want to make sure that you change rows as you paste your formula to new cells. In order to do that, you are going to place a dollar sign in front of the column character that you are dealing with – $A1. Let’s have a look.
CLICK IMAGE TO SEE ANIMATED GIF
BREAKDOWN OF MIXED REFERENCING
(1) Enter your formula in cell F3 (=$D3*$E3) – the dollar sign before the column of each reference means we want it to stay the same. So we will continue to reference column D and E when the formula is copied but the row reference will change as we move down a row.
(2) Type Ctrl + C (in windows) to copy the formula from F3.
(3) Paste the formula into cell G4. The reason that I changed columns is because I wanted to show you that even though we moved over a column (offset of 1 column), we continued to refer to column D and E in our formula – but notice that our row offset itself accordingly (new formula in G4 = $D4*$E4).
Note: If we had not put the $ in front of D and E, our new formula in G4 would have been =E4*F4 giving us an entirely different and unexpected answer.
Absolute Cell Referencing
Absolute cell referencing is the complete opposite of relative cell referencing. When we develop a formula and add the $ symbol before both the column and row reference, the formula is said to be absolute. This means that no matter what, when we copy the formula from one cell to the next, we are always going to reference the exact same cells and ranges that were initially outlined in the originating cell. For example cell F3=$D$3*$E$3 – if we copy this to cell G4, G4 will equal the exact same thing (=$D$3*$E$3). This ultimately means that our result will be the exact same for F3 and G4. A good example of when to use this is if we have a storage fee per cubic feet in a cell and we want to calculate the storage fees based on the total cubic feet used of each item (I’m just sticking with inventory theme here to keep things consistent). Let’s take a look at this example.
BREAKDOWN OF ABSOLUTE REFERENCING
(1) Enter your formula in cell H3(=G3*$M$2) – the dollar sign before the column M and row 2 means we are going to refer to this specific cell when the formula is copied. Because our first cell reference G3 has no absolute reference on row or column, when we move down only one row, we will stay in the same column but change rows (offset down 1 row) hence now referencing G4.
(2) Copy the data down the column by double clicking the small square in the bottom right hand corner of H3.
(3) Double check cell H4 and you’ll notice that we are still referencing cell $M$2 but we’ve changed our first value/cell reference to G4.
Practical Examples: Situations and Solutions to Excel Formulas Not Working as a Result of Cell Referencing
To really try and drive this concept home, we are going to go over a few different examples so that we can see what happens when we don’t apply the proper referencing syntax. This will hopefully teach you enough so that you no longer have to be worried about your formulas not working as a result of cell referencing.
Don’t be Lazy – When Not to Use Relative Referencing or When to Pick up the Slack and Start Using those $ $igns
There comes a time as an Excel user where you need to step away from just using the standard Excel default cell referencing. When the time comes you’ll need to make sure to use those dollar signs. As you can see in our example below, we forgot to make the reference to cell M2 absolute. As a result, because we copied our cell one row down (the new cell column remained the same as the previous), we moved our reference one row down as well to M3.
The Solution: Before you copy the contents of H3 over to new cells, be sure to make the storage fee an absolute reference. In this case our formula in H3 should then be as follows (H3=G3*$M$2). You should know this by now but if you are still asking why does G3 not have any dollar signs, it’s because we know that as we move to H4, we also want to move the reference to G4 (as the row changes in the cell that contains the formula, we want the row to change on the reference for the cubic feet column/field). Technically, we should make G3 have an absolute column of $G but because we are not copying the formula to another column we can get away with this.
You’re Not Making Any Money With Those Dollar Signs – When Not to Use Absolute Referencing
Alright so it’s one thing to forget to use the absolute reference (using dollar signs) but it’s also important to know when not to use them. In this scenario we are going to look at when and why it’s important not to make your cell references absolute. In the image below, you’ll notice that when we make our formula in F4 absolute, and we drag that formula to F11, every cell from F5 to F11 calculates $D$3*$E$3 instead of changing rows to multiply the Qty On Hand by the Unit Price for each record.
The Solution: Again, it’s important to look at what you are looking for in terms of a result and this will determine what you need to do for the referencing. In our case, we’ve made both of the cell references in F3 absolute (again the dollar sign before a row/column reference makes it absolute) and we’ve copied that formula by double clicking the small box in the bottom right hand corner of the cell (TIP: this will fill your current column down as far as data exists to the right or left columns). Because both the column and row references were absolute, our formula remained the exact same multiplying D3 by E3 for each of the cells. To fix this, we need to remove the absolute referencing by selecting each individual reference in F3 and hitting F4 until no dollar signs exist in our equation. This will of course make our formula relative (REMINDER: relative referencing is the default) which will allow us to copy this formula down and ensure that we multiply QTY ON HAND by UNIT PRICE for each row/record in our data set. It’s important to note that we don’t need to make the column reference absolute here because we are not copying the formula into a cell that is outside our current column. This is because relative to our original cell, we have not moved over to the left or right a column – the next part of this section should explain this in detail with a screen shot.
We Can’t Always Operate on a Hybrid – When You Need to and When You Don’t Need to Use Mixed Referencing
In this example I’m going to show you a situation where you need to use mixed referencing to ensure you get the result that you are looking for – this is the same example as above but bare with me. This is kind of an uncommon situation but let’s pretend like we had two columns for Total On Hand Value (OHV2 being the second column). In this column, we want to ensure that we have the same result as we have in F4 which is by the way (a relative reference to D4*E4). If we were to copy the relative formula from F3 (D3*E3) to G4, the formula in G4 would be E4*F4 which would not give us the total on hand value as we expected (this is because we moved 1 row down and 1 column right – as opposed to just 1 row down which was the case in cell F4). Let’s look at how this works:
F3= D3*E3 ——Copied to G4 ——–G4=E4*F4 (G4 is located exactly 1 row down and 1 column right of cell F3)
D3 therefore has to move 1 row down and 1 column right when the reference is relative. That means G4 will reference E4.
E3 will also move 1 row down and 1 column right when the reference is relative. That means G4 will reference F4.
The final formula in G4 therefore becomes E4*F4.
Using a relative reference in F3 and copying it to cell F4 would work. This is a situation where mixed referencing is not required because we are copying our cell formula and pasting it in the same column (F4 is 1 row down from F3 and no columns to the right or left which means that each of our references in our formula will also only move 1 row down when copied).
The Solution: To make sure we get this right (so G4 is the result of D4 multiplied by E4), we need to use a mixed reference in F3 so that we make our columns absolute (rows will remain relative) which will then allow us to copy the formula to G4 and get the result we are looking for (G4=$D4*$E4). Below provides the example of the correct answer/result.
Make sure you have a look at the table below our data set. This outlines each reference method iteration that we can have in F3 and how it will effect the result if it is copied to G4. The table also explains how the formula works (formula instruction column) for each iteration. You may want to keep this screen shot as a quick reference to how cell referencing works.
Summary and Closing Thoughts on Excel Formulas Not Working and Cell Referencing
I think that about covers all of the bases to make sure that when you are using formulas that you are also using the correct referencing method. The biggest thing to keep in mind is how you would like your formula to be copied over and what cells you’ll want your formula to reference in the cell that you are pasting to. If you understand and apply these concepts, you’ll be sure to rule out your Excel formula not working as a result of incorrect cell referencing.
If you enjoyed this post and found it useful, please do share with your peeps! Also, I don’t often get many comments but I’d love to hear your thoughts on what other problems you experience when it comes to formulas not working so PLEASE DO LEAVE A COMMENT!!!
I’m looking forward to adding other articles to this Excel Formula Not Working series and I hope you are too.
It’s been quite some time since I last posted because I’ve been trying to work on a Custom Number Format Guide for the readers. This is in an attempt to try and provide a little more value when you come visit my website. While working on this I needed to build a way to quickly understand the syntax behind using custom number formatting. In the spirit, I decided to create a video that shows what syntax is required to build a custom number format. (more…)
I haven’t hadn’t opportunity to post much lately but I was using this today and thought it would be something that people might like to see. Excel 2013 allows you to integrate with the Bing Map App to display location specific data sets. Do you have any thoughts or ideas on how this may be useful to you? Have a look! (more…)
I know, I know. I am starting to get lazy and I haven’t really done any full length posts but these image posts will at least give you some quick pointers. That being said this is just another visual lesson on how you can use Excel Sparklines with the merging tool to make large charts that represent your data. (more…)
Happy Monday everyone! This is another very quick post about how you can use some of Excel’s awesome functionality. In this post I want to show how to use Excel custom number formatting to format a range of numbers into thousands (k). Simply follow the steps below and you’ll be right on your way. If you have any questions, feel free to leave a comment. (more…)
