Category Archives: Formatting

Fixed Decimal Places


I have a Fixed Decimal Place tip, in my Must Know Excel Tips Tricks and Tools for the CPA Ebook.  Eric Robinson read my tip and sent me his own tip on fixed decimal places.
If you are unfamiliar with the Fixed Decimal feature in Excel, it is pretty cool.
 If you need to enter columns of numbers, with a fixed number of decimals, into a worksheet, you can have Excel enter the decimals for you, using the fixed decimal feature. So, if you set the decimal point to 2 and then type 12345, Excel will display 123.45. It is very easy to use but you do have to turn it on and off so Eric’s macro tip is very handy.
I have a link below if you wish to read more about the feature.
Eric’s macro allows you to switch back and forth between no decimal place and  decimal places. Switching back and forth can be tedious if you are, for example, entering a long column of check numbers (no decimal) and check amounts (2 decimal places).  I have shared his macro instructions below. Thanks so much  Eric.

Sub ToggleAutoDecimal()
‘ ToggleAutoDecimal Macro
‘ Macro recorded by Eric Robinson to handle fixed decimal places
    Application.FixedDecimal = Not (Application.FixedDecimal)


End Sub

Here is a link to the instructions on how to use fixed decimals on my Excel-diva blog.

fixed decimal place

Formatting Alternate Rows

Formatting alternate Rows

Thanks to Diana Lawless for sending in this Excel Format painter icontip.

Using the Shift key and the Format Painter icon together allows you to format an entire worksheet quickly. If you are formatting alternate rows or columns quickly and do want not mess around with Conditional formatting rules this is a great shortcut.
Diana wanted to format every other row of her 1,000+ row worksheet with a variety of formats to enhance readability.

To change formatting of the spreadsheet:

  • Format line(s) as needed
  • Highlight formatted line(s)
  • Left click Format Painter (paintbrush)
  • Hold ‘Shift’ key
  • Tap ‘End’ key
  • Tap down arrow key
  • Release ‘Shift’ key
    And voila! The formatting was automatically copied to every line in the spreadsheet.
    If you have inserted new rows in an already formatted worksheet, this is a really quick way to fix any formatting problems.

Conditional Formatting and =MOD()

An alternative method, as mentioned above, is to use conditional formatting. If you use conditional formatting and the =MOD() function it is a quick way to create a general ledger type of effect with rows displayed in alternate colors.

Below are the steps you can use to apply a rule to data. In my example here I am using the MOD function and telling Excel to shade every second row of my P&L so that it is easier to read. I am specifying 2 in the MOD function so that every second row is shaded and I am specifying green as that is the traditional accounting color but obviously you can select any color you want – just make sure that it is light enough that that the data can be seen through it.

Formatting dialog box

Formatting dialog box

1. Select your data

2. Click Conditional Formatting on the Home Ribbon.
3. Select New Rule.
4. Click on Use a Formula to determine which cells to format.
5. In the rule description, type =MOD(row(),2)=0.
6. Click Format.
7. Select a light green fill – this is usually associate with accounting general ledgers.
8. Click OK.
9. Click OK.

It should look something like the image below. It really does improve readability.

general ledger formatingt with =MOD

general ledger formatingt with =MOD

Conditional Formatting-Icons

Using ICONS- Conditional Formatting

When I started writing my Excel Driven Dashboard course a couple of years ago I really started appreciating a more visual approach to Excel.
Now I am always incorporating color and images into spreadsheets wherever possible.
So, to that end, I want to talk about icon sets in Excel and how they can be useful.
In the example, I am going to go through I have a column of units sales that is compared against budget and a variance column.
If you have more than a couple of rows, it is difficult to quickly identify your problem areas. You may be able to sort the variance column it but I think using conditional formatting is more useful and helps to convey a better picture of what is going shop

Here is my data.  Yes- everyone knows about my love of chai and lattes!

Column D shows the variance. In case you are wondering I used the accounting format as I like the parentheses around negatives rather than the minus sign which is a little more difficult to see (maybe I am just getting old??)

Excel spreadsheet

Anyway, this is a short list but it is still difficult to determine which products are doing well compared against budget.  Using conditional formatting and the associated icon sets will make it much clearer.

1.First, select the data in the Variance column (D4.D12)
2.Go to the Home ribbon and click on Conditional Formatting and select Icon Sets.
3.Scroll down to the bottom and select More Rules

4.Go down to Icon Style and select one you like. I personally prefer ones that displays well on paper as well as online.

icon_rulesSo, I wanted a green check mark to display on all the positive variances where actual is better than budget, a yellow to display if there is no variance and a red x to display if the variance is negative so:

5. Select >= from the drop-down list, type 1 in the Value section and select Number from the Type drop-down.
6.In the second row, select >=from the drop-down list, type in the Value section and select Number from the Type drop-down.
Yes- having a < or <= would be nice – who the heck programs this stuff anyway?

7.Click OK.

Icon set dialog box

Voila – at a glance you can see where the problem areas are.

Now, you may find that too busy so you do have some options.

If you want to edit a Conditional Formatting rule – it is very easy – Select your data and  go back and click on Conditional Formatting and then select Manage Rules.

Click on Edit Rule…. 

show_icon only

Click and put an x in Show Icon only. This will hide the numbers and you will only see the icons.

no cell icon

An alternative if you want it clearer is to hide the green check marks so that you can focus on the problem products.
To do that, click on the drop-down beside the green check mark and select No Cell Icon from the top of the dialog box. You could also do this for the yellow exclamation if you were not concerned with products that made budget-barely.

Now, doesn’t this bottom screenshot look pretty clear and tell the story at a glance.

conditional formatting

Now, I also like to see the numbers and even if I edit the rule and de-select Show Icons Only, it still looks messy so what I like to do is create an additional column and have the icons display there.  That way, I get the best of both worlds- the numbers and the visual impact.
So, I clicked in cell E4 and typed =D4 and then copied it down so I have the same numbers in Column D and E.
icon_last_dialogboxThen I selected Column E and did the steps 4-6 as outlined above and then clicked the Show Icons Only box and then selected No Cell Icon for the green check mark and the yellow exclamation point.  You have to admit this looks a lot cleaner.


Wow.. .time for that cup of coffee now!

picture of coffee

  • Your website was easy to navigate and informative.” “This was my first use of your resources but I’m sure it won’t be my last. Thank you for your excellent course material and succinctly stated instructions which expedited order and payment of course selection, completion of exam, and immediate availability of Certificate of Completion. A great experience. Thanks.

    - Draper

  • “I am extremely impressed with the courses and the reasonable price.” -Janet A. May 2017

    - Janet

  • “..may I say it is rarer that I ever have gotten a personal reply from a CPE Site! Glad I have found your site.”

    - Tom