Excel tips and tutorials
It’s Fancy Footer Friday! Check with your boss – maybe you can leave early to celebrate.
This week, I’ve been working on Excel printed reports, and one of my clients wanted some fancy features in the footer. There are built-in footer options in Excel, but my client wanted to pull information from the worksheet, and format the date, so we needed some footer programming.
Continue reading “Excel Footer with Formatted Date”
Happy Canada Day! I made a couple of holiday weekend flags in Excel, to help you celebrate.
In your early days of working in Excel, you probably saw the occasional cell full of number signs (you might call them pound signs or hash marks). Here’s what you can do to get rid of number signs in Excel.
In Excel 2007 and Excel 2010, you can use icon sets in conditional formatting. There are built-in icon sets, and in Excel 2010 you can Customize Excel Conditional Formatting Icons, to some extent. Here’s how to do that, and a workaround to create icons on the worksheet instead.
Continue reading “Customize Excel Conditional Formatting Icons”
Do you ever open an Excel workbook, and find that tragedy has struck your comments? You spent hours inserting those comments, and making them just the right size and shape. Then, for no apparent reason, everything changes. Comments are in the wrong place, and wrong size. Here’s how to fix those wandering Excel comments.
Yes, it’s Valentine’s Day today, and if you were too busy to buy your sweetie a card yesterday, you can make one in Excel. Phew!
Your boss won’t mind if you spend a couple of hours working on this today, because it’s an Excel project! This Excel Valentine card uses a named range, data validation, a formula, and conditional formatting (to change the heart from white to pink to red).
If you won’t have time, or if your drawing skills are worse than mine, you can download the sample Excel Valentine file, at the end of this blog post.
And if you want some romantic music in the background, while you work on your Excel Valentine card, you can listen to the YouTube playlist, compiled by John Walkenbach and his blog readers.
To create the heart shape,
The formula will count how many text items have been added at the top of the worksheet, and the result is used for conditional formatting.
=COUNTA($E$1:$E$3)
With the Heart range still selected, set up the following conditional formatting:
The heart shape will be hidden, and only revealed when the Valentine message is selected.
To hide the heart:
Next, you’ll create three drop downs, for the Valentine message at the top of the worksheet.
To prepare the cells for the drop down lists:
Create the following data validation drop down lists:
Tip: To type a heart shape, press Alt and type a 3 on the number keypad (if no number keypad, try Fn+Alt+L). On a Mac, another key combination might be needed.
The Excel Valentine heart has white fill and white font, so it’s not visible.
To see the heart:
To see how the card works, you can download the Excel Valentine Card sample file.
The file is in Excel 2007 format, and zipped, and it contains no macros.
_____________
An Excel scroll bar can be used for practical (and sometimes boring) things, like testing the effect of price changes, or adjusting a chart’s date range.
But this is the festive season, so let’s use a scroll bar for something more, well, festive!
In this example, instead of accounting and finance, you’ll see how to use an Excel scroll bar to decorate a Christmas tree, without macros.
Unfortunately, this Excel file can’t make hot chocolate or eggnog, so you’ll need to provide your own.
It’s not just for the holiday season though — the sample file has useful features that you can adapt to other workbooks too:
To see how the Excel Christmas tree trimming scroll bar works, you can watch this short Excel video.
For instructions on creating the Excel scroll bar file, and to download the sample file, go to my Contextures website: Excel Scroll Bar Christmas Tree Example.
Apparently you have to buy a ticket if you want to win the lottery, so I’m out of luck. However, if you’re in an office lottery pool, or buy your own tickets for the lottery, Excel can let you know if you have a winning ticket.
Continue reading “Highlight Winning Lottery Numbers in Excel”
To keep your data details confidential, you might want to send someone a copy of a pivot table, without the link back to its source data. It’s easy to copy a pivot table, and paste it as values,but it is difficult to copy pivot table format and values.
A nice new feature in Excel 2010 is the ability to refer to a different worksheet when creating conditional formatting and data validation. Let’s take a look at how the conditional formatting from different sheet feature works, and create a workaround for older Excel versions.
Continue reading “Conditional Formatting From Different Sheet”
