|
|
The holidays are a great time to catch up on tasks. I've updated another popular Excel video -- Colour a Row Based on a Cell Value in Excel 2007. It now explains the steps in Excel 2010 and Excel 2007, instead of Excel 2003.
To continue the conditional formatting theme, here are a few articles that you might have missed, when they were originally posted.
Highlight Weekend Dates in Excel – To make it easier to keep track of weekends in Excel, you can use conditional formatting to highlight the rows where the date is a Saturday or Sunday. Excel Conditional Formatting – Data Bars – In Excel 2007 and Excel 2010, you can use conditional formatting to add data bars to cells. This makes it easy to visually compare the list of numbers, just as you would in a bar chart. Highlight Lottery Numbers with Conditional Formatting – If
Continue reading Excel Conditional Formatting Update
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!
Trim the Tree
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, it 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:
Scroll bar lets users change a number quickly and easily A text box that displays a changing message based on VLOOKUP formula conditional formatting shows hidden cells when target number is reached named ranges make it easy to work with specific cells
Continue reading Use Excel Scroll Bar to Trim Christmas Tree
Remember, Sunday October 17th is Spreadsheet Day, so you'd better start planning your celebrations. You could start the day with a big bowl of Chex cereal -- each bite looks like a little spreadsheet. For dessert at the end of the day, have some pie, or bars, while you dream about charts.
As my contribution to Spreadsheet Day 2010, I'll list the top 5 Excel tips that I read on Excel blogs over the past year. These were Excel articles that solved problems I'd encountered, or got me thinking about new techniques to try. You can read these articles on Sunday, while you're celebrating Spreadsheet Day. Maybe your kids would enjoy them as a bedtime story!
There were many other Excel articles too, but I only had room (and energy) to list five. You can help out by listing your favourite in the comments.
Copy PivotTable Style Formatting
When PivotTable
Continue reading Spreadsheet Day 2010 — Top 5 Excel Tips
Happy Canadian Thanksgiving! You probably have your own spreadsheet to organize the meal, but you can download my Excel Holiday Dinner Planner, if you don't have one of your own. The dinner planner has data validation cells, where you can select a start time, or type of food, or cooking location, from a drop down list. Data Validation Drop Down Doug Glancy sent me a sample data validation file that he created, that uses a variation on dependent data validation. I changed his file a bit, to "Thanksgiving" it up. The file has a list of Thanksgiving dinner guests, whether they'd like turkey, and if so, the number of slices. In column B there's a simple data validation drop down list, where you can select TRUE or FALSE. Conditional Data Validation In column C, there is a conditional data validation drop down list, based on
Continue reading Excel Conditional Data Validation
If you're looking for love, move along -- the "Canadian Singles" in the article title refers to hit songs, not eligible bachelors. Last week, a new book was published with a list of top 100 Canadian singles, based on a poll of music professionals and fans. In his J-Walk blog, John Walkenbach posted a link to the Canada's Top 100 Singles list, and there was a lively discussion in the comments section. Top 100 Canadian Singles List No discussion is complete without a spreadsheet, so I copied the list into Excel, and cleaned it up. To make it more interesting, I found the release date for each hit song, and split them into decades, using the FLOOR function. From that data, I created a pivot table, showing the count of songs from each decade, listed by rank. Was most of the best music released in the 1970s, or were
Continue reading Top 100 Canadian Singles in Excel
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. It just takes a bit of conditional formatting, and the COUNTIF function. You could use this technique to highlight other things too, based on a list of items to check. For example, you could create a list of bad accounts or obsolete products, and highlight those on an Excel worksheet with product order requests. Highlight Lottery Numbers In this example the ticket numbers are in cells B2:G4, and the winning numbers drawn for the current lottery are entered in cells B6:G6. Follow these steps to highlight the winning numbers in the list of tickets: Select the cells were the conditional formatting will be
Continue reading Excel Knows If You Won the Lottery
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 if you try to paste the values and source formatting, you'll be disappointed by the results. The values are pasted, but not the PivotTable Style formatting. Fortunately, John Walkenbach discovered that you can paste from the Office Clipboard, instead of using the Paste Values command, and the PivotTable Style formatting is pasted too. The result is something that looks like the original pivot table, without the link to the source data. For more instructions on PivotTable Styles and pivot table formatting see the Contextures Pivot Table Format page. Watch the Pivot Table Format Copy Video To see the steps for pasting the pivot table formatting and values,
Continue reading 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 improved conditional formatting feature works, and create a workaround for older Excel versions. Compare Cells on Two Sheets In this example, you'd like to highlight the cells on Sheet1, if they're higher than the matching cell on Sheet2. To highlight the cells in Excel 2010: Select cells A1:C8 on Sheet1, with A1 as the active cell On the Ribbon, click the Home tab, and click Conditional Formatting Click Highlight Cell Rules, then click Greater Than In the Greater Than dialog box, click in the cell reference box Click on the tab for Sheet2 Select cell A1 on Sheet2, and click OK The cells on Sheet1 are now highlighted if their value is higher than the matching cell
Continue reading Conditional Formatting From Different Sheet
This week, there were a couple of Excel conditional formatting questions in the blog comments. Ron asked about changing the font colour for the highest, second highest and lowest values. Guido wants to highlight values that aren't multiples of another cell's value. I'll answer the questions here, so they're easier to find. Maybe you've encountered similar conditional formatting problems, and this will help. Change Font Colour for Top 3 Values The first question is from Ron, who wants to change the font colour, instead of the fill colour: I am trying conditional format a range of three cells where the FONT of the highest value in the range will be in Red follow by Blue and Green. I know it can be done using cell fill but, with a large spread sheet, it will look like dog's breakfast. Any help would be appreciated. Also can this be done across
Continue reading Excel Conditional Formatting Examples
August seems to be a very popular birthday month among my Excel friends. I won't mention any names here, because most of them are quite elderly, and the shock might upset them. ;-) Anyway, to all of them, and you, if you're celebrating this month -- happy birthday! The Birthday List If you're in charge of an employee list, and have to send birthday greetings, or hiring date anniversary congratulations, you can use Excel to help you keep track. In the worksheet shown below, there are fictitious employee names and birthdates. In a separate column you could use the MONTH function to check which birthdates are in the current month. This formula compares the month of the date in column C, to the month of today's date. If the months are the same, the result is TRUE. =MONTH(C2)=MONTH(TODAY()) Highlight Birthdays with Conditional Formatting Instead of adding another column with
Continue reading Highlight Current Month Birthdays in Excel
|
|
Recent Comments