Do you use Excel to keep track of software subscriptions, or domain registrations, or other things with an expiry date? It's important to keep track of those dates, so here's an example of how to monitor expiry dates in Excel, and see what needs to be renewed soon.
Aside from staring at them closely, how can you compare two cells in Excel? Here are a few functions and formulas that check the contents of two cells, to see if they are the same. We'll start with a simple check, then move up the formula ladder, for more complex comparisons.
Have you seen the articles that blame Excel for all kinds of business errors? In some cases, problems occur because rows were hidden, and that distorted the data analysis. To help avoid those problems, I created a sample file that shows an Excel hidden data warning, if rows or columns are hidden.
Continue reading "Excel Hidden Data Warning"
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.
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?
Last week, I ran into problems counting Excel data with COUNTIF, and it's Twitter's fault! Why did they do that? The COUNTA function can cause problems too, when it counts cells that look empty. Let's see how to fix both of those issues.
Do you use Excel's SUBSTITUTE function very often? It's a handy way to count items in a cell, when they're separated by commas or spaces. The examples below show different ways to use this function – have you tried the variation in the last example?
As Juliet famously said to Romeo, "What's in a name?" And she was talking about rows (misspelled as "rose"), so maybe Juliet was using a spreadsheet at the time. There are special rules for Excel names, but you might be surprised to see what is allowed.
Long ago, I created a Christmas planner in Excel, and it has evolved over the years. Well, tomorrow is the first of December, so you've probably finished your planning for this year. But, if you haven't, this year's new and improved version of the workbook might help. Now it's an Excel Holiday Planner – you can pick any date as your holiday, instead of being Christmas themed.
Excel has a SUBTOTAL function, which ignores hidden or filtered rows. There is a Subtotal feature too, that quickly groups your data, and adds one or more rows of subtotals. Do we still need these Excel subtotals though, now that we have pivot tables and the AGGREGATE function?