Tag Archives: formatting

Fixed Decimal Places

MACRO TIP FOR FIXED DECIMAL

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

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 on.coffee 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…. 
edit_rules

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.

icon_final

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

picture of coffee

  • ” This was my first experience with CPASelfStudy.com and I was very impressed. I will definitely consider taking CPE through CPASelfStudy.com in the future.”   12/2017

    - Christopher Q.

  • Your ethics course was the most interesting and clear course I have taken. I usually dread the years it is required but this year was a pleasant surprise.

    - Terry

  • “always love your site and prompt customer service !! ”  Dec. 2017

    - Thank you :)