You know that merged cells are evil, and should be avoided at any cost. Those merged cells can make it almost impossible to do simple tasks on a worksheet, such as sorting or filtering. Merged cells can even make it difficult to select a range of cells – and that’s annoying, as you probably know! Here's how you can center headings without merging cells.
It’s easy to add conditional formatting icons in Excel, by selecting one of the built in options. These were introduced in Excel 2007, and improved in Excel 2010. However, you still can’t get all the icons in any colour. For example, you can show Harvey Balls (the 5 Quarters icon set), but only in black and white. We'll see how to create colored Harvey Balls in Excel.
Have you ever imported data into Excel, from your credit card statement, or somewhere else, and found that Excel dates won't change format? And, if you try to sort that column of dates, things end up in the wrong order.
That happened to me this week, and here’s how I fixed the problem, using a built-in Excel tool.
Here at Contextures, there aren’t very many employees to keep track of. There’s just me, and I always remember my hire date anniversary, and take myself out for a nice lunch. However, you might have more people on your employee list, and you can use Excel to highlight upcoming employee service anniversaries.
See how to create your own Excel icon set, to overcome a limitation with the built-in options. Icon Sets were added to conditional formatting in Excel 2007, and you can use the icons to highlight the results in a group of cells. This workaround uses symbols on the worksheet, instead of the Icon Set symbols.
How hot was it in your neighbourhood last week? It was so hot, here in Mississauga, that I stayed inside, most of the week. On the bright side, I got lots of work done, but I’d rather have cooler temperatures, than hot, muggy days.
In this example, we want highlight duplicate records in a table. There is a built-in rule for highlighting duplicate values in a single column, but nothing that will check an entire row.
So, we’ll create our own rule, and it will require a new column on the worksheet, before we add the conditional formatting.
Concatenate the Data
In the sample data, there are two identical rows, and these should be highlighted after we apply our conditional formatting.
The first step is to use the CONCATENATE function to combine all the data into one cell in each row. Add a new heading in cell G1 – AllData – and in cell G2, enter this formula, to combine the data from all the cells in that row.
Next, copy the formula down to the last row of data.
Apply the Conditional Formatting
Then, a conditional formatting rule is set, to color the rows that are duplicate records. We’ll use the COUNTIF function to check for duplicates in the AllData column.
If there is more than one instance of a data combination, that indicates a duplicate row, and the cells in columns A:F will be coloured. The two rows with duplicate records are highlighted, so our conditional formatting formula worked!
Download the Sample File
For detailed instructions, and to download the sample file, please visit my Contextures website: Highlight Duplicate Records in a List
Watch the Video
To see the steps for setting up the conditional formatting, watch this short video.
Who knew that this would still be a problem in Excel 2013? Almost 4 years ago, I posted about numbers not lining up, when Excel was zoomed to less than 80%
Now I’m working on a new laptop, and realized that the problem is back. In Excel 2013, I’m using the default font – Calibri 11. Here’s a list of numbers, at 80% zoom.
And here is the same list at 72% zoom – the numbers have changed to a proportional font, and the 1111111s are much narrower than the 8888888s.
Change the Registry
To fix the problem, I followed the instructions that I posted in April 2009, when I was setting up my previous laptop. Here they are again, with adjustments for Windows 8 and Office 2013. The original instructions came from this MSKB article:
This is really a fix for a Euro symbol display problem, but it also fixes the proportional font display.
Here's what I did for Win8 and Excel 15:
- Make a backup copy of the registry before you tweak any settings
- Quit any programs that are running.
- Press the Window key and R, to open the Run window.
- In the Open box, type regedit, and then click OK.
- Locate, and then click to select the following registry key:
- With the Options key selected, point to New on the Edit menu, and then click DWORD (32-bit) Value.
- Type FontSub, and then press ENTER.
- Right-click FontSub, and then click Modify.
- In the Value data box, type 0. Since the value is zero, it doesn’t matter which Base you select – I left it on Hexadecimal.
- Click OK to close the Edit DWORD window
- On the File menu, click Exit to quit Registry Editor.
- Start Excel, and the numbers should line up correctly, eve when zoomed.
Last week, I was working on a client's time sheet file, and noticed something strange. The worksheet was used to record shift start and end times, and I was testing the calculations, to make sure that everything was working correctly.
Shifts that run past midnight can cause problems, so I tested that scenario, and I also tested shifts that started at midnight. I wanted to be sure that everyone would get all the pay they were entitled to!
In column D, I calculated the hours worked, by subtracting the start time from the end time, and adding 1 to the end time, if it was in the next day. Here is the formula in cell D2:
The calculations were working well, but the cells where I had entered a midnight time appeared empty. Fortunately, the calculations still worked, even with the missing start times.
I know that midnight is a favourite time for mysterious things to happen in horror movies, but was my worksheet haunted? Was this an early Halloween prank?
Then I realized that someone had formatted the worksheet to hide the zeros. And, if your worksheet is formatted to hide zeros, the midnight times won't show, because they are equal to zero – 0:00
I turned the Show Zeros setting back on, and all the midnight times came out of hiding.
To show zeros on a worksheet:
- In Excel 2010, click the File tab, then click Options.
- In the Advanced category, scroll down and in the Display options for this worksheet, add a check mark to "Show a zero in cells that have zero value"
- Click OK to close the Options window.
Unhide Your Zeros
I had forgotten all about this problem, until someone left a comment on my blog yesterday, asking why the midnight times weren't showing in her worksheet. At least I'm not the only one who has had this mysterious problem, and if it has happened to you, I hope this tip helps.
It's not one of Excel's most complicated problems, but it's the little things that can drive you crazy. 😉
If you're upgrading to Excel 2010, you've probably noticed that the Conditional Formatting feature has changed quite a bit. Now you can apply more than 3 rules, and there are fancy features like data bars and icon sets.
Intro to Conditional Formatting
I've updated my Intro to Conditional Formatting page, to show Excel 2010 instructions, and made a new video to show the steps. If you're looking for Excel 2003 instructions, they've been moved to this page: Excel 2003 instructions.
Watch the Excel 2010 Conditional Formatting Video
To see the steps for creating two conditional formatting rules in Excel 2010, please watch this short video.
Download the Sample File
To see the sample used in this video, and other conditional formatting examples, you can visit the Conditional Formatting Introduction page.