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"

Fix Excel Conditional Formatting Duplicate Rules

Conditional formatting is a great way to highlight specific data, but did you know that it can automatically create new rules on its own? I'll show you how that happens, and an easy way to fix those conditional formatting duplicated rules.

Continue reading "Fix Excel Conditional Formatting Duplicate Rules"

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! Here's how you can center headings without merging cells.

Continue reading "Center Headings Without Merging Cells"