How to Fix Excel Data Bars

To make a chart right in the worksheet cells, use Excel Data Bars, built with conditional formatting. See how to add a standard set of Data Bars (Excel 2007 and later), and adjust their settings to make the bars look better.

And remember to mark your calendars – next Tuesday, October 17th, is Spreadsheet Day – alert your family and friends!

Continue reading “How to Fix Excel Data Bars”

Highlight Cells Based on Two Conditions

Happy New Year! I hope you had time to relax over the holidays, and you stepped away from the computer for a while. Unless you were using the computer as excuse to hide away from all the holiday chaos! Now we’re back to work, and one of the first questions I got this year was how to highlight cells based on two conditions.

Continue reading “Highlight Cells Based on Two Conditions”

Excel Christmas Tree 2015

It’s only a week until Christmas Eve, and to help you celebrate the holidays, I have updated my Scroll Bar Christmas tree. No macros are needed, but this version uses customizable icon sets that are available in Excel 2010 and later versions.

This will be my last blog post until the new year, so happy holidays, and remember to cell-ebrate safely!

Continue reading “Excel Christmas Tree 2015”

Round Numbers With Excel Formatting

Did you know that Excel limits the number of numbers that appear in a cell, in General format? I discovered that limitation this week, while updating my page on rounding functions in Excel.

Have you run into this limit? It was something that I hadn’t noticed before, and there doesn’t seem to be any setting to adjust this.

 

Continue reading “Round Numbers With Excel Formatting”

Highlight Winning Lottery Numbers

No, I’ve never won the lottery, but that’s probably because I don’t buy tickets! Your odds of winning improve (slightly) if you actually have a ticket for the draw.

However, there are many workplaces where someone has organized a weekly lottery pool, and they have a batch of ticket numbers to check.

Instead of checking those numbers manually (and missing one or two!), you can use Excel to check them for you. It won’t even ask for a percentage, if you are lucky enough to win a prize.

Continue reading “Highlight Winning Lottery Numbers”

Show Color With Conditional Formatting

I like to color data entry cells, so they’re easy to spot on an Excel worksheet. If you know that you should type a value in every blue cell, it’s quicker, and safer, to fill in a worksheet.

Recently, I was asked how to color a specific number of data entry cells, based on a number that someone selected from a drop down list. So, instead of manually coloring the data entry cells, I used conditional formatting to color them.

In the screen shot below, 3 was selected in cell C2, so 3 data entry cells are blue, in cells C5:C7.

Continue reading “Show Color With Conditional Formatting”

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!
But sometimes it’s tempting to merge cells, and I often see them used to centre headings across several columns. Sure, it might look pretty when you’re done, but those merged cells can come back to haunt you.
Instead, let’s create the multi-column headings without merging cells. It takes a couple of extra clicks, but it’s well worth the effort.
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”