I bet you’ve got better things to do than fiddle with your spreadsheet for hours on end.
And why rely on Excel’s default settings when you can easily format your spreadsheet to fit your exact preferences?
There are numerous formatting adjustments you can make to any of the numbers in your Excel file. You can automatically add commas to large numbers, you can automatically round numbers up or down, and you can even automatically fill in cells with the color of your choice.
Adding Commas to Large Numbers
Does your spreadsheet have a bunch of huuuuuge numbers, like 99535767? Sometimes it’s easier to “see” these numbers when they’ve got commas, like 99,535,767 instead of 99535767. Rather than painstakingly adding those commas by hand, use Excel’s built-in Comma Style button.
Rounding Numbers Up or Down
Want to round your numbers up or down? You’ve got two options.
First, you could use the Increase Decimal or Decrease Decimal buttons. The decimal places are still there, inside your original cells, but now they’re just hidden out of view. Try clicking on one of the cells and you’ll see all those millions of decimal places hanging out in the Formula Bar.
Your second option is the =round() function. This function has two pieces. First, tell Excel which cell contains the number that needs some rounding (e.g., A1). Second, tell Excel how many decimal places you want (e.g., 2). Now, when you use the Increase Decimal or Decrease Decimal buttons, you’ll notice that the number has been truncated. Try clicking on one of the cells and only the first two decimal places will show up in your Formula Bar.
Color-Coding Numbers Greater Than a Certain Value
It’s easy to color-code your numbers by hand if you’re only dealing with 5, 10, or 20 numbers. But it’s even easier to automatically color-code your numbers with Excel’s Conditional Formatting feature.
Highlight or select the range of information that you want to color-code. On the Home tab, click on the Conditional Formatting icon. You’ll notice a couple drop-downs within drop-downs that contain dozens of color-coding features for you to explore.
Sometimes I want to see how many numbers fell above a certain value. For example, I might want to see how many people were above age 35. Highlight or select the age values, click on the Conditional Formatting icon, select Highlight Cells Rules, and then select Greater Than.
As shown in the pop-up window, you’ll get to customize your cut-off value (I changed my cut-off from 47 to 35 in this example). You’ll also get to customize your colors. For instance, you might want everyone older than 35 to show up as red, yellow, or green.
Color-Coding Numbers that Fall Between Two Values
Excel’s Conditional Formatting icon has endless possibilities. In this example, I color-code everyone between the age of 25 and 45.
On your Home tab, simply click Conditional Formatting, Highlight Cells Rules, and Between. The subsequent pop-up window gives you customization options that can be tweaked to fit your specifications.
Color-Coding the Largest 10 Numbers
Or, maybe you’re interested in seeing the top 10 highest ages from your list.
Go to Conditional Formatting, Top/Bottom Rules, and Top 10 Items. You might want those highest items to appear green, red, or yellow. Or customize the colors further by selecting Custom Format from the pop-up menu.
Color-Coding the Top 10% of Numbers
Interested in making the top percentage of items stand out?
Go to Conditional Formatting, Top/Bottom Rules, and Top 10% Items. In the pop-up window, select the exact percentage you’re interested in–10%, 25%, or 50%, etc.–and then select the colors of your choosing.
Checking for Duplicate Numbers
Whenever I’m dealing with peoples’ names or ID numbers, I like to make sure each person is only listed once on my spreadsheet. Removing duplicate entries early on in the process ensures that my numbers will be accurate later on.
It would take a lot of time – and mental energy! – to scan a long list for double-entries. No matter how hard I was paying attention, I would probably miss one or two. So, let’s let Excel do the hard work for us.
Excel’s Conditional Formatting will change the color of duplicates to make them stand out. For example, you can add a red or yellow fill to those cells. Highlight your list of numbers and go to Conditional Formatting on the Home tab. Select the first option from the list, Highlight Cells Rules. Then, select Duplicate Values. You’ll get a new pop-up window that gives you plenty of color options. In my example, I selected a light green fill with dark green text.
My dataset contained one set of duplicates—person 116 was accidentally listed twice. Once I spotted the error, I deleted the double-entry by highlighting one of those double rows, right-clicking, and selecting Delete.
Undoing the Instant Color-Coding
Want to remove that instant color-coding and get your spreadsheet back to its original state? Conditional Formatting is easy to undo.
Go to Conditional Formatting –> Clear Rules. You can remove your conditional formatting from just a section of cells that you’ve already highlighted (Clear Rules from Selected Cells) or from everywhere within your sheet at once (Clear Rules from Entire Sheet).
Want to learn more spreadsheet strategies? I partnered with Udemy to share more than 50 of my favorite time-saving tips. Read my full guidebook at https://www.udemy.com/tutorials/learn-excel/.
2 Comments
We may remove duplicate directly by Remove Duplicates on Data Tab.
Nevertheless it could be a good idea to highlight the duplicates for review before deleting it.
[…] K Emery demonstrates a few quick ways to format numbers, and uses conditional formatting to highlight some of […]