FREE EXCEL ONLINE TRAINING (THIS WEEK ONLY): Power Query & Data Cleansing Techniques!

SIGN UP HERE: http://myexcelonline.com/blog/datacleansingsignup

~ Receive 3 FREE training videos that will show you how to save HOURS EACH DAY using Excel’s built in features…including Formulas, Text to Columns, Pivot Tables plus much more!

~Learn the most powerful feature in Excel since VLOOKUP…it’s called POWER QUERY & it will rock your Excel world!!!!!

~ Transform messy data & automate your daily/weekly/monthly reports within minutes (NOT DAYS!)…without using VBA or Macros!

LEARN NOW: http://myexcelonline.com/blog/datacleansingsignup

My book: Guerrilla Data Analysis 2nd Edition

My blog: http://datascopic.net/blog-2-2

I never expected to do a VLOOKUP tutorial because there are so many that other excellent folks have done. But … it’s time.

In this video:
– When to use VLOOKUP 0:50
– VLOOKUP’s pieces 1:45
– Raw VLOOKUP 3:52
– VLOOKUP with Tables 10:23
– Flash Fill 8:39
– weaknesses and Controversies 12:18

I focus on VLOOKUP with False (exact match).
VLOOKUP with True is wrongly described as an approximate match. No! VLOOKUP True is for use when records need to be assigned to tiers or categories. For more on VLOOKUP/True:

My book: Guerrilla Data Analysis 2nd Edition
http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336
My blog: http://datascopic.net/blog-2-2

This video picks up exactly where Day 13 stopped. Here’s that video:

Day 13 introduced the AND and OR functions. This video

– Nests AND inside IF to test for True and False
– Compares cell values to see if an exam was submitted within the time allotted.

Excel Ignited is a video series for Excel beginners. This was supposed to have been a series of videos posted daily during August 2016. However, once started … WOAH! The second video took 10 hours to record and edit. So, rather than daily videos, this will be a 31-episode video series that may take until the end of September to complete.

You with me?

My book: Guerrilla Data Analysis 2nd Edition
http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336
My blog: http://datascopic.net/blog-2-2

This was a real question that came to me. Someone has 113 worksheets in a single Excel file. How can he get all of the data stacked up in a single worksheet?

Here, I show how to consolidate the data on 7 worksheets. For 113, the process would be the same.

Thanks to Ken Puls for sharing this solution in a blogpost

This solution uses M Code in Excel’s Get & Transform. This is best because G&T does have a manual way of consolidating worksheets, and it’s fine if there are just a few worksheets because there’s a step that needs to be repeated for each worksheet. But no! Let’s not do that 113 times.

Using M code … we got it licked!

My book: Guerrilla Data Analysis 2nd Edition
http://www.amazon.com/Guerrilla-Analysis-Using-Microsoft-Excel/dp/1615470336
My blog: http://datascopic.net/blog-2-2