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 lottery 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 of your prize money, 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”

Create Colored Harvey Balls in Excel

It’s easy to add conditional formatting icons in Excel, by selecting one of the built in options. There are limitations though.

For example, you can’t get all of the icons in any colour combination that you choose. For example, you can show Harvey Balls (the 5 Quarters icon set), but only in black and white.

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”