Go From 48672 Excel Formulas Down to 1 – Contextures Blog

Happy Canada Day, if you’re celebrating tomorrow! And Happy July 4th, on Monday, if you’re in the USA! These holidays inspired me to change an Excel workbook, so it has a formula in only one cell. The old workbook needed 48,672 formulas, to do the same thing! Also, how often does Canada Day fall on a Friday? Let’s find out!
Continue reading “Go From 48672 Excel Formulas Down to 1 – Contextures Blog”

Find Product Price Based on Quantity with Excel VLOOKUP and MATCH

In some Excel workbooks, you might have a simple product pricing table, with the product name in one column, and the product price in the next column. With a simple VLOOKUP formula, you can get the price for any product.

In the video below, there’s a different kind of pricing table. There are product names in the first column, with quantity pricing information in the next four columns.

Continue reading “Find Product Price Based on Quantity with Excel VLOOKUP and MATCH”

Count Duplicate Number Sets With Excel Formula

Do you love a complicated formula problem in Excel? A while ago, there was a “Count the Duplicate Number Sets” challenge, here on the Contextures blog. I posted my solution, and other people shared their methods for solving that problem.  You can still get the Excel workbook, and try it for yourself. And here’s another solution, from David Newell.

Continue reading “Count Duplicate Number Sets With Excel Formula”

How to Fix Excel Chart to Show Hidden Data from Worksheet

If you create a chart from worksheet data, and then filter the data, some of the chart date might disappear. Sometimes, that’s what you want — filter by Region, and see only the East’s sales in the chart. But, if you always want to see all the data in the chart, try this easy fix.

Note: There’s a similar problem with Excel’s in-cell Sparkline charts, and you can fix that too! Continue reading “How to Fix Excel Chart to Show Hidden Data from Worksheet”

Fun With Microsoft Excel: Streaming Radio

Pro Tip – Don’t sign up for TikTok! It’s a giant carnival, filled with hypnotic music, shiny objects, travelling medicine shows, and fortune tellers. You stop in for 5 minutes, and stagger out hours later, dazed and confused. Sure, there’s helpful info on TikTok too, but you’ll probably get sidetracked while you look for it!

Continue reading “Fun With Microsoft Excel: Streaming Radio”

Calculate Easter Date with Excel Formula

Easter will be here soon, and if you want to know exactly (“eggsactly”?) when it is, you can calculate Easter’s date with an Excel formula or User Defined Function. See the example below, and there’s a sample file you can download too, with several Easter formulas and functions. Oh, and there’s cake!

Continue reading “Calculate Easter Date with Excel Formula”

How to Fill Blank Cells in Excel With Value from Above

Some worksheets have blank cells in a column, instead of repeating the headings in every row. That white space makes it easier to read the report, but will cause problems if you need to sort or filter the data.

Here’s how you can fill in those blank cells, to avoid messing up your data. Then, use an easy conditional formatting trick, to hide the duplicate headings. Continue reading “How to Fill Blank Cells in Excel With Value from Above”