Chat with us, powered by LiveChat

Category Archives: Formatting

Find & Select Constants

I just discovered this little timesaver and thought I would share it with you. Okay, to be honest, my husband told me about it.

Suppose you have an Excel spreadsheet and you have a number of product codes all in one row and need to break them out. You can do text to columns but if the number of product codes in the rows are uneven, it can be time-consuming  to collect them all for analysis. Using Find & Select Constants solves this problem.

Let’s use a simple example. Below is a spreadsheet showing certificate ids that customers have been issued.

If I want to break out those certificate ids to count how many of each were issued, I would first select all the data in Column C and then select Data>Text to Columns. As you can see the data is delimited with Commas.

Click Next and select Comma.

Select Finish.
Text to Columns separates all the certificate ids  into separate columns.

In this example, it would be fairly easy to copy and paste the different certificate numbers into Column C but if you had rows and rows of data it would be very time-consuming especially if the certificate ids  extended over into Column L and M or further. This is where  Find and Select Constants comes in.

Select all of Column D and then click Find and Select.

Excel selects all the cells in Column D that contain data.

All the cells are selected so I can just select Copy and then Paste those certificate ids  into Cell C9.

find and select constants in excel

Excel ignores the blank cells and nicely grouped my selection so they are all contiguous. I can then delete the data in Column D since it now displays in Column C and then move on to the next column of certificates.  Unfortunately you need to copy as Excel does not let you cut multiple range selections.

Hopefully you see some applications for this. I found it quite handy.

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

  • You guys do a great job!  As a person in industry it can be really expensive to stay current and you have great options that are actually useful as well as cost effective!

    - Sue

  • Great course! Price was right and the material was very thorough.

    - Alex

  • “..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