My Spreadsheet Lab
  • Home
  • Templates
  • Excel Tips
  • Video Tutorials
  • Excel Blog
  • Excel Coach
  • Excel Training
  • Contact Kevin

Tips

You are here: HomeArchive by Category "Tips"

Tips

Auditing & Editing M code (Power Query)

  • July 16th ,2019
  • Kevin Lehrbass
  • Get & Transform, Tips
  • 0
  • 0
  • Share
    • Facebook
    • Google+
    • Twitter
    • Pinterest
    • LinkedIn

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! (more…)

Subtotal helper column

  • May 31st ,2019
  • Kevin Lehrbass
  • Tips
  • subtotal
  • 2
  • 0
  • Share
    • Facebook
    • Google+
    • Twitter
    • Pinterest
    • LinkedIn

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? (more…)

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

  • April 30th ,2019
  • Kevin Lehrbass
  • Tips
  • charts
  • 6
  • 0
  • Share
    • Facebook
    • Google+
    • Twitter
    • Pinterest
    • LinkedIn

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). (more…)

Partial Match Count with a Condition

  • April 22nd ,2019
  • Kevin Lehrbass
  • Formula or VBA, Tips
  • 3
  • 0
  • Share
    • Facebook
    • Google+
    • Twitter
    • Pinterest
    • LinkedIn

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? (more…)

Sorted Data Validation List

  • April 16th ,2019
  • Kevin Lehrbass
  • Solutions, Tips
  • 6
  • 0
  • Share
    • Facebook
    • Google+
    • Twitter
    • Pinterest
    • LinkedIn

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? (more…)

Weighted Average

  • April 09th ,2019
  • Kevin Lehrbass
  • Solutions, Tips
  • 0
  • 0
  • Share
    • Facebook
    • Google+
    • Twitter
    • Pinterest
    • LinkedIn

I’ll show you the weighted average formula, how it calculates, and I’ll prove that the answer is correct! (more…)

Countifs confirms Vlookup

  • April 09th ,2019
  • Kevin Lehrbass
  • Tips
  • 0
  • 0
  • Share
    • Facebook
    • Google+
    • Twitter
    • Pinterest
    • LinkedIn

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. (more…)

Why and How to Unpivot Data

  • February 08th ,2019
  • Kevin Lehrbass
  • Tips
  • Get & Transform, Power Query, Unpivot
  • 1
  • 0
  • Share
    • Facebook
    • Google+
    • Twitter
    • Pinterest
    • LinkedIn

Before you analyze your data it’s important to decide if you need to unpivot it. Why unpivot? What’s unpivot? Are there exceptions? (more…)

Rearranging Data

  • February 03rd ,2019
  • Kevin Lehrbass
  • Get & Transform, Tips
  • Get & Transform
  • 1
  • 0
  • Share
    • Facebook
    • Google+
    • Twitter
    • Pinterest
    • LinkedIn

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!) (more…)

Concatenate Values to Create a Key

  • November 04th ,2018
  • Kevin Lehrbass
  • Efficiency, Tips
  • 5
  • 0
  • Share
    • Facebook
    • Google+
    • Twitter
    • Pinterest
    • LinkedIn

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! (more…)

1 2 … 4 Next →

Share

Newsletter

Do You Excel?

Kevin_Col-275x300-small-2019.png
“Improving your Excel skills can change your career!. Visit my 'EXCEL TRAINING' section!”

- Kevin Lehrbass

Excel Made Easy

Learn Excel

Free Templates

Video How-To's

© 2014 My Spreadsheet Lab| Entries (RSS)
website design & development by Engine Communications