• Ken Puls

    by Published on 2015-10-22 06:42 PM     Number of Views: 6502 

    M is for (Data) Monkey: The Excel book that will change YOUR life...
    It may have a funny title, but this will be one of the most important Excel books you ever buy in your career.

    Written BY Excel pros FOR Excel pros, this book has been designed to guide you through learning how to master the new "Get and Transform" data experience in Excel. Released as a free add-in from Microsoft for Excel 2010 and 2013, Power Query technology is now built in to Excel 2016 and the Power BI Desktop application. This technology is a game changer, and will revolutionize the way you work with your data forever.

    Way back when this book was nothing more than a concept, we knew that it needed to be approached in a specific way. It had to speak to Excel users, the problems that they face on a daily basis, and ways to solve those problems both effectively and efficiently. It had to be written to keep you engaged, and to take you on a guided journey, learning from someone who understands you as an Excel pro, how you work, and what you face on a daily basis.

    We're proud to say that we believe that we've delivered on that goal, starting with common tasks you perform, and back filling them with real world issues, inspired by a wide variety of real world data sets. Packed with downloadable files and step by step instructions, this book is built to help you master Power Query so that you can use it in your own work, with your own issues. This book was designed from the beginning to get you up to speed quickly, ...
    by

    Course Description

    How many times have you begun building a spreadsheet, only to have it evolve on you based on requests from users and managers? While this is a great compliment to your work and skills, these changes can cause our spreadsheets to grow ...
    by

    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 ...
    by Published on 2014-12-10 03:20 AM     Number of Views: 7578 

    This page is dedicated to listing out Excel courses that are available online. I am VERY selective about the courses that I include on this page, and will only publish listings from those who I trust to deliver a great online training experience that will add value to you.

    Courses By Ken Puls (of this very site)
    I maintain a list of all of all of my online courses here in one place:



    I've also worked with a company called GoSkills to help design and build a targeted Excel course which is offered in an online format. This course is set up to offer short videos to train on the specific topics you need to get the most out of Excel. You can learn more about it in the video below (and yes, the Expert they talk about is... me!)






    Even better, there's even a discount available to you if you're ready to sign up. Just enter the code XLGURU66 at checkout any you end up getting the course at 1/3 the regular price. (A savings of almost $200!) Click the banner below to get started, or to check out the sample videos available:






    Chandoo's Excel School
    Chandoo is a fellow Excel MVP who has been at the online training game a long time. He offers a full Excel School online, as well as a dashboarding course and a variety of others. I can guarantee that you'll learn some useful tricks by working through is material.

    To learn more or sign up, just click here.
    Rob Collie's Power Pivot Pro University
    Learn PowerPivot from the guy who not only wrote the books on it, but actually worked on the development of the initial product. Rob's work is fantastic, and the course is comprehensive.

    You can find out more about it by downloading his brochure here: PowerPivotProOnlineUniversity.pdf.

    And when you're ready to sign up, this link will take you directly to the page to do so.
    by Published on 2013-10-31 01:58 AM     Number of Views: 32464 
    Article Preview

    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, ...
    by Published on 2013-03-21 05:04 AM     Number of Views: 13883 
    Article Preview

    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.
    ...
    by Published on 2013-03-14 05:47 AM     Number of Views: 84334 
    Article Preview

    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 icon for a weather forecast; something we use on our dashboards from our golf course. We update the weather data daily ...
    by Published on 2013-01-22 06:30 AM     Number of Views: 24646 
    Article Preview

    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.
    ...
    by Published on 2012-12-17 03:50 AM     Number of Views: 16146 
    Article Preview

    As an accountant, I build financial reports, and one of the issues that we have to deal with is getting the numbers to display in a friendly format. Because of the way that debits and credits are stored in databases though, this can be a little challenging.

    In this article, Iím going to walk through the process of building a simple profit and loss statement with PowerPivot, showing how to make all values show correctly. There are some certain key issues that weíve got to work through though, and weíll do that using a conditional DAX measure.
    ...
    by Published on 2012-12-13 05:02 AM     Number of Views: 24107 
    Article Preview

    The method for hiding items with zero totals in a PivotTable is different if you're working with a regular PivotTable or a PowerPivot PivotTable. This article focusses on how to accomplish this goal in the PowerPivot version. (If you're working with a regular and you want to hide calculated items that have zero balances, you'll want to check out Debra Dalgleish's blog post on the subject.)

    To start, assume that weíve got a fairly simple PowerPivot pivot table that looks like this: ...
    by Published on 2012-12-13 04:50 AM     Number of Views: 5967 
    Article Preview

    Mike Alexander has a great blog post on how to Add Column Spacing In A PivotTable. We can also accomplis the same thing through a PowerPivot solution, but using DAX. And in this case, DAX is even easier to use that the old method, taking one less step!
    ...
    by Published on 2012-10-27 02:48 AM     Number of Views: 16132 
    Article Preview

    It's always nice when you go to a forum and someone gives you a nice bit of VBA code that is supposed to accomplish your goals. But if you've never used VBA code before, it's kind of hard to know what to do with it! This article has been written to get you up and running and get that code in the right place.

    Please note... this article assumes you've been directed to add your code to a standard module, as 99% of code is housed there. If your coder told you to put your code in a worksheet module or the ThisWorkbook module, this wont' quite get you there. (You should still read this article, but also this one which lists the other types of Excel modules.)
    ...
    by Published on 2012-05-17 05:48 AM     Number of Views: 13472 
    Article Preview

    One of the things that always struck me as odd about using subtotals is that only the words in the subtotals turn bold, and not the actual subtotals themselves. With a long list of data this can make it hard to see which numbers are the subtotals amongst the data. Fortunately this is very easy to fix using conditional formatting.
    ...
    by Published on 2012-05-02 07:45 AM     Number of Views: 47840 
    Article Preview

    So you've built a really cool PivotTable, and you hooked up a slicer to allow exploration of the data. And now you want to do something really cool, but you need to make your formula react to the slicer value. Can you do it? Of course you can, but how?

    This article will focus on the technique to do exactly that: return the value of a slicer to a formula. Note that, in order to follow along you will need Excel 2010 or higher, as Slicers didn't exist prior to this version.
    ...
    by Published on 2012-04-26 06:00 AM     Number of Views: 14262 
    Article Preview

    If you have a website or blog, or distribute newsletters by email, you can earn up to 30% in commissions by selling downloadable Excelguru products!

    All commissions are tracked by ...
    by Published on 2012-04-24 08:10 AM     Number of Views: 29427 
    Article Preview

    Excel PivotTables: Itís a polarizing term. People who use PivotTables absolutely love them. For those who donít, the term is mysterious and encourages the fear of powerful features that are the domain of geeks and Excel junkies, and out of reach to the common man. But nothing could be further from the truth.

    Why You Should Take This Course:

    If youíve never created, or donít regularly use PivotTables in your work, let me show you that you are missing out on one of the most useful, impressive and easy-to-use tools in Microsoft Excel.

    Excel PivotTables are an amazingly powerful feature that can be used to very quickly summarize and slice and dice data with ease. And contrary to many usersí fears, they are actually VERY easy to use once youíve been shown how.

    So why donít you let me do just that? In this one hour video training course, I will teach you how to build your first PivotTables. Youíll see first hand just how easy they are to create, how fast they work, and how easy it is to change them to display your data the way you want to see it.
    ...
    by Published on 2012-04-24 08:07 AM     Number of Views: 11237 
    Article Preview

    Excel PivotTables: Itís a polarizing term. People who use PivotTables absolutely love them. For those who donít, the term is mysterious and encourages the fear of powerful features that are the domain of geeks and Excel junkies, and out of reach to the common man. But nothing could be further from the truth.

    Why You Should Take This Course:

    If youíve never created, or donít regularly use PivotTables in your work, let me show you that you are missing out on one of the most useful, impressive and easy-to-use tools in Microsoft Excel.

    Excel PivotTables are an amazingly powerful feature that can be used to very quickly summarize and slice and dice data with ease. And contrary to many usersí fears, they are actually VERY easy to use once youíve been shown how.

    So why donít you let me do just that? In this one hour video training course, I will teach you how to build your first PivotTables. Youíll see first hand just how easy they are to create, how fast they work, and how easy it is to change them to display your data the way you want to see it.
    ...
    by Published on 2012-04-24 08:03 AM     Number of Views: 10156 
    Article Preview

    Excel PivotTables: Itís a polarizing term. People who use PivotTables absolutely love them. For those who donít, the term is mysterious and encourages the fear of powerful features that are the domain of geeks and Excel junkies, and out of reach to the common man. But nothing could be further from the truth.

    Why You Should Take This Course:

    If youíve never created, or donít regularly use PivotTables in your work, let me show you that you are missing out on one of the most useful, impressive and easy-to-use tools in Microsoft Excel.

    Excel PivotTables are an amazingly powerful feature that can be used to very quickly summarize and slice and dice data with ease. And contrary to many usersí fears, they are actually VERY easy to use once youíve been shown how.

    So why donít you let me do just that? In this one hour video training course, I will teach you how to build your first PivotTables. Youíll see first hand just how easy they are to create, how fast they work, and how easy it is to change them to display your data the way you want to see it.
    ...
    by Published on 2012-04-04 05:36 AM     Number of Views: 9367 
    Article Preview

    XLG File Tools is a FREE add-in which was created to increase the functionality of Excel and address the plethora of extra clicks that were introduced begining in Excel 2007. It's current feature set is designed to make the job of opening existing files and creating new files more efficient.

    XLG File Tools is easy to use, and is guaranteed to make you more efficient. In addition, it's a snap to install and requires no administrative priviledges to do so.
    ...
    Page 1 of 6 1 2 3 ... LastLast
  • 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
  • Recent Article Comments Widget