Center Headings Without Merging Cells

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.

Continue reading "Center Headings Without Merging Cells"

Create Colored Harvey Balls in Excel

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.

Continue reading "Create Colored Harvey Balls in Excel"

Excel Dates Won’t Change Format

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.

Continue reading "Excel Dates Won’t Change Format"

Highlight Upcoming Employee Service Anniversaries

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.

Continue reading "Highlight Upcoming Employee Service Anniversaries"

Create Your Own Excel Icon Set

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.

Continue reading "Create Your Own Excel Icon Set"

Highlight Duplicate Records in an Excel List

With Excel's conditional formatting, you can highlight cells based on specific rules. There are some built-in rules available, and you can use formulas to create your own formatting rules.

Highlight Duplicates

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.

highlightduplicatesribbon

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.

highlightduplicaterows03

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.

=CONCATENATE(A2,B2,C2,D2,E2,F2)

highlightduplicaterows04

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.

=COUNTIF($G$2:$G$8,$G2)>1

highlightduplicaterows01

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!

highlightduplicaterows05

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.

_________________________

Keep Numbers Aligned When Zooming

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.

numberfont01

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.

numberfont02

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:

Euro Currency Character Is Not Displayed Correctly in Excel 2003

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:

  1. Make a backup copy of the registry before you tweak any settings
  2. Quit any programs that are running.
  3. Press the Window key and R, to open the Run window.
  4. In the Open box, type regedit, and then click OK.
    • numberfont03
  5. Locate, and then click to select the following registry key:
    HKEY_CURRENT_USER/Software/Microsoft/Office/15.0/Excel/Options
  6. With the Options key selected, point to New on the Edit menu, and then click DWORD (32-bit) Value.
    • numberfornt05 
  7. Type FontSub, and then press ENTER.
  8. Right-click FontSub, and then click Modify.
    • numberfont06
  9. 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.
    • numberfont07
  10. Click OK to close the Edit DWORD window
  11. On the File menu, click Exit to quit Registry Editor.
  12. Start Excel, and the numbers should line up correctly, eve when zoomed.

numberfont08

___________________

Midnight Times Missing in Excel Worksheet

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:

=(C2+IF(OR(C2=0,C2<B2),1,0)-B2)*24

showzerovalues02

Missing Midnight

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.

showzerovalues03 

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?

Hidden Zeros

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:

  1. In Excel 2010, click the File tab, then click Options.
  2. 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"
  3. Click OK to close the Options window.

showzerovalues01 

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. 😉

____________________________

Conditional Formatting in Excel 2010

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.

conditionalformaticon00

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.

___________________