• Live Course: Master Your Excel Data

    Course Description

    In this hands on course we are going to look at the most important Excel features that will turn you into a quick and efficient data master in Excel.

    Most users missed the implementation of the Excel Tables feature that happened in Excel 2007. It stealthily slid its way into the product with very little fanfare, yet it is one of the most important architectural features that you can implement to enforce data integrity in your solutions. It’s with that feature we’ll start, showing you how and why this little feature should form the foundation of every solution you build.

    Next, we’ll look at how we can quickly summarize data sets ranging from 10 to 10,000 (or more) rows with only a few clicks. Another overlooked and underused feature, PivotTables are SUPER easy to use once you get through a couple of very basic lessons. We’ll show you those and more, as well as how to avoid the most common PivotTable issues that affect users.

    “Great!” you’re thinking, “But my data is a mess. Can I really use these tools?”

    Maybe you’re thinking about that text file you have to import … the one with repeating headers and garbage rows that you have to pull in through Excel text import wizard. The one you have to then land in the worksheet and do further cleanup. The one you get the pleasure of re-doing every month. What if we told you we would teach you to do it once and you’d only need to click “refresh” to do it next month? How much time would that save you?

    Maybe you’re thinking about the 20 text files that you have to manually open and copy and paste together to use in your solution. What if we told you we would teach you to do this with only a few clicks? What if that also came with the built in ability to just click “refresh” to update it next month?

    What if we told you we could “unpivot” a data set with ease? How about merge two tables together with no VLOOKUP formulas required? Pull data from your emails and land it in a nice Excel table that you could just refresh? Retrieve data from the web and refresh it at will?

    We can do it all and more using Power Query – a free add-in released by the Microsoft Excel team – which works with Excel 2010 and Excel 2013 Professional Plus. It’s free, it’s powerful and best of all its incredibly easy to use!

    If you work with data – clean, dirty or somewhere in between – you need to come and take this course. It will increase your efficiency, your effectiveness and pay you back in spades. It will change the way you work with data forever.

    Target Audience

    This course is intended for users who have to import and clean up data in Excel.

    Course Requirements

    This is a hands on course. All attendees are required to bring a laptop with one of the following versions of Excel installed:

    • Excel 2010 Professional
    • Excel 2013 ProPlus
    • Excel 2013 Professional Plus
    • Excel 2013 Standalone
    • Excel 2016 (any version)

    In addition, you will also need to have the Power Query add-in downloaded and installed on your device. You can download this software here. If you require a more detailed guide to configure Power Query, please click this link.

    Course at a Glance

    • Excel Tables
      • Creating Excel tables
      • Using Excel tables

    • PivotTables
      • Creating PivotTables
      • Modifying PivotTables
      • Modifying PivotTable data sources
      • PivotTable best practices

    • Power Query
      • Importing and refreshing single files
      • Importing all files in a folder at once
      • Appending tables
      • Merging tables (with no VLOOKUPS)
      • Un-pivoting data
      • Query organization and best practices

    Registration Includes

    • A full day of hands on, in class instruction (9:00AM - 4:30 PM)
    • Coffee breaks
    • Lunch
    • A certificate of attendance (for claiming Professional Development credits)
    • Example files based on real world issues (including completed versions for later review)
    • A one hour video that demonstrates the majority of the Power Query concepts taught during the course, and
    • A copy of our Magic of PivotTables video course

    Registration Details

    This course is currently being offered on the following dates/locations:

    Date City Price Early Bird Price Registration Link
    2016-10-21 (Fri) Victoria, BC 245.00 245.00 Add to Cart

    Don’t see a date/location that works for you? Request the course be run in your area. If we can make it work, we will!

    Other Courses

    Remember, you can always find all of our currently scheduled courses on our training calendar at http://www.excelguru.ca/forums/calendar.php


    I'm afraid that you must be logged in to comment or leave a testimonial. I wish it could be otherwise, but I'm trying to keep my site spam free for everyone's benefit. If you don't yet have an account it's completely free to sign up, and a very quick process. Simply click here to Register. Not only can you post a comment here, but it gives you full access to posts questions in our forum as well!


    If you already have an account, and just haven't logged in yet, what are you waiting for? Login Now!

  • Recent Knowledge Base Articles

    Ken Puls

    Making Pivot Table Hyperlinks Clickable

    If you’ve ever built a PivotTable that contains hyperlinks, you’ll notice that clicking the hyperlinks doesn’t do anything. This can be a bit frustrating as the reason you put that field on the Pivot in the first place is that it’s valuable information you want to use. When you click the hyperlink,... read more
    Ken Puls 2013-10-31, 01:58 AM
    Ken Puls

    Temperature Forecast Chart

    Some of the really cool charts that we can build in Excel involve the trick of combining multiple chart types together to make them happen. In this article, we’ll build one of those; a temperature chart that not only shows the forecasted high and low temperatures, but also the season highs and lows. The beauty of this chart is that it provides a lot of information, some of which essentially fades into the background until you really need it.
    ... read more
    Ken Puls 2013-03-21, 05:04 AM
    Ken Puls

    VLOOKUP for Pictures

    Something that can be very handy when you’re building a dashboard is to return a certain picture depending on a condition. We can use VLOOKUP to look up data in a table and return the corresponding value from a different column, but unfortunately we can’t do that with pictures... or can we?

    This example shows how to accomplish the equivlanet of a picture VLOOKUP, and is based on looking up a picture to display the appropriate... read more
    Ken Puls 2013-03-14, 05:47 AM
    Ken Puls

    Understanding How Conditional Formatting Rules Are Applied

    Conditional formatting in Excel is a powerful tool that allows you to dynamically format cells depending on the values of that or other cells’ data. In Excel 2007 the conditional formatting engine was re-written, opening things up to allow more than 3 conditional formats on any cell, as well as conditional formats that could overlap ranges. All in all, these were fantastic improvements that can lead to some very versatile and useful worksheets.

    Unfortunately, the user interface to control conditional formatting is not the most intuitive. The purpose of this article is to help you understand the way Excel applies rule precedence so that you can build powerful formatting rules of your own, without getting frustrated along the way.
    ... read more
    Ken Puls 2013-01-22, 06:30 AM