As you know, Excel can do almost anything! Recently, I changed the server that my site and blog are on, and Excel helped with the IP Addresses. And since it’s that time of year, let’s see how Excel can calculate Easter dates for us.
Excel Roundup 20180322
Spring has finally arrived, so here is a “Goodbye Winter” Excel Roundup, with articles that I’ve read recently, and upcoming Excel events and free online courses that I’ve heard about. To get Excel links and articles every week, sign up for my weekly Excel newsletter.
Excel Conditional Formatting Colour Macro Problems
If you need to know what color is in a worksheet cell, or a shape, you can check it manually, or use VBA code to find the color number. There are conditional formatting color macros too, but they’re a bigger challenge, and didn’t work for a project that I built recently. Here’s what I was working on, and some of the Excel color resources that I used along the way. There are links to download my sample file, and one from Visio MVP, John Marshall. Continue reading “Excel Conditional Formatting Colour Macro Problems”
Excel Seating Plan with Charts
If you’re planning a wedding, or another type of event, you might need a seating plan. Get this free Excel seating plan with charts, and modify it to show your guest names and table arrangement. See how to use the sample file, and how to add more guests and tables.
Hockey Player Data Analysis in Excel
Congratulations to the USA Women’s Hockey team, who won the Olympic gold medal. They beat our Canadian team, in a hard-fought game that went into overtime, and ended with a shootout. The team rosters were available online, so I used those for a hockey player data analysis, in Excel pivot tables. Is there anything in the player data that shows why the USA team won?
Excel Budget Report with Value Selector
Instead of showing a budget’s forecast, actual and variance data all at once, click a button to view the values one at a time. That makes the report easier to read, and takes less space on the worksheet. See how this technique works in my Budget Reporter with value selector workbook.
Excel VLOOKUP Formula Error Mystery
Someone sent me a workbook in which a simple VLOOKUP formula was returning #N/A errors, instead of the correct results. The product numbers looked the same, but Excel didn’t match them in the lookup. Can you solve this VLOOKUP formula error mystery?
Show Warning in Excel Drop Down
With Excel’s data validation, you can show a drop down list of items in a cell. You can even create “dependent” drop downs. For example, select a region, and see only the customers in that region. See how to show a warning in Excel drop down list, if the source data is not set up correctly.
Create and Copy AutoCorrect List Items
To save time, create AutoCorrect entries for words, phrases, and even symbols that you type frequently. Then, type a short code, and Excel automatically changes it to the full text. See how to create an entry, then print a list of all your entries, and copy them to a different computer, using the AutoCorrect macros below.
Problems Counting Excel Data COUNTIF COUNTA
Last week, I ran into problems counting Excel data with COUNTIF, and it’s Twitter’s fault! The COUNTA function can cause problems too, when it counts cells that look empty. Let’s see how to fix both of those issues.
Continue reading “Problems Counting Excel Data COUNTIF COUNTA”