My Spreadsheet Lab

The site is about Microsoft Excel.

  • Home
  • Templates
  • Excel Blog
  • About Me
  • Contact Kevin

Post Category → Tips

Auditing & Editing M code (Power Query)

posted in Get & Transform, Tips on July 16, 2019 by Kevin Lehrbass 0 Comments

Power Query is a powerful and intriguing tool. However, there are stumbling blocks that make it challenging. Let’s audit and then modify some M code! Continue reading →

Subtotal helper column

posted in Tips on May 31, 2019 by Kevin Lehrbass 2 Comments

This formula =COUNTIFS($A$7:$A$309,$AK4,$B$7:$B$309,1) considers all rows within the given range when counting. What if we want to exclude filtered and/or hidden rows? Continue reading →

3 Newly Added Charts in Excel 2019 You Must Learn to Create

posted in Tips on April 30, 2019 by Kevin Lehrbass 9 Comments

This Guest Post is contributed by Aprajita Sharma from ExcelChamps.   Learn about 3 new charts available in Excel 2019 (or with a Office 365 subscription). Continue reading →

Partial Match Count with a Condition

posted in Formula or VBA, Tips on April 22, 2019 by Kevin Lehrbass 3 Comments

Once again I discovered a formula that I had written down in a notebook. What is it doing? Is there an easier way to solve this? Continue reading →

Sorted Data Validation List

posted in Solutions, Tips on April 16, 2019 by Kevin Lehrbass 6 Comments

This post from ExcelFORO explains how to use VBA to create a sorted data validation list. It’s a great solution! Are there other ways to solve this? Continue reading →

Weighted Average

posted in Solutions, Tips on April 9, 2019 by Kevin Lehrbass 0 Comments

I’ll show you the weighted average formula, how it calculates, and I’ll prove that the answer is correct! Continue reading →

Countifs confirms Vlookup

posted in Tips on April 9, 2019 by Kevin Lehrbass 0 Comments

So many times vlookup (or index & match) saves the day and gets your answer!  ….wait…are you sure it’s correct??? Read this post to see why you should use countifs to confirm your vlookup. Continue reading →

Why and How to Unpivot Data

posted in Tips on February 8, 2019 by Kevin Lehrbass 1 Comment

Before you analyze your data it’s important to decide if you need to unpivot it. Why unpivot? What’s unpivot? Are there exceptions? Continue reading →

Rearranging Data

posted in Get & Transform, Tips on February 3, 2019 by Kevin Lehrbass 1 Comment

Rearranging data is initially about recognizing the pattern. This screenshot is from Ken Puls. I instantly saw the steps to rearrange the data (thanks to Ken’s online course!) Continue reading →

Concatenate Values to Create a Key

posted in Efficiency, Tips on November 4, 2018 by Kevin Lehrbass 5 Comments

Excel users who don’t know this concept waste hours manually looking through their data. A filter helps but creating a valid lookup key is what Mr Carlson needs! Continue reading →

← Older posts