• ## Ken Puls

### Temperature Forecast Chart

by Published on 2013-03-21 05:04 AM     Number of Views: 531

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.
...

### VLOOKUP for Pictures

by Published on 2013-03-14 05:47 AM     Number of Views: 898

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 ...

### Understanding How Conditional Formatting Rules Are Applied

by Published on 2013-01-22 06:30 AM     Number of Views: 1407

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.
...

### Using HASONEVALUE in a DAX IF statement

by Published on 2012-12-17 03:50 AM     Number of Views: 1677

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.
...

### Hide Calculated Items With Zero Totals In PowerPivot PivotTables

by Published on 2012-12-13 05:02 AM     Number of Views: 2045

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: ...

### Creating a Spacer Column in a PowerPivot PivotTable

by Published on 2012-12-13 04:50 AM     Number of Views: 743

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!
...

### Adding VBA Code For The First Time User

by Published on 2012-10-27 02:48 AM     Number of Views: 2463

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.)
...

### Highlight Subtotals for Easy Reading

by Published on 2012-05-17 05:48 AM     Number of Views: 3300

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.
...

### Retrieving Selections From A PivotTable Slicer

by Published on 2012-05-02 07:45 AM     Number of Views: 7256

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.
...

### Sales Affiliate Program

by Published on 2012-04-26 06:00 AM     Number of Views: 3034

If you have a website or blog, or distribute newsletters by email, you can earn a 30% commission by selling Excelguru products!

All commissions are tracked by e-Junkie, the same vendor ...

### The Magic Of PivotTables (2010) - Video Course

by Published on 2012-04-24 08:10 AM     Number of Views: 7312

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.
...

### The Magic Of PivotTables (2007) - Video Course

by Published on 2012-04-24 08:07 AM     Number of Views: 2497

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.
...

### The Magic Of PivotTables (2003) - Video Course

by Published on 2012-04-24 08:03 AM     Number of Views: 2205

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: 3220

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.
...

### Sorting A Column Of PowerPivot Data By Another Column

by Published on 2012-03-28 06:16 AM     Number of Views: 4032

One of the things that used to drive me crazy about working with PivotTables in PowerPivot’s initial (2008) release was summarizing dates by month. With a standard PivotTable, we can use the built in Group functionality to group dates by Years, Quarters and Months. But in PowerPivot, that functionality wasn't implemented. To deal with this, we have to provide our own date table, but the months never really sorted well, and we had to resort to tricks to coerce them into the right order.
...

### Displaying “Last Updated” Date And Time In PowerPivot

by Published on 2012-03-28 05:49 AM     Number of Views: 3205

We use PowerPivot to display key information in dashboards, some of which can be refreshed right up to the current second. Naturally, one of the first questions asked when looking at the reports (particularly if they get printed) is “When was the data last updated?”
...

### Approximate Matches With VLOOKUP

by Published on 2012-03-22 06:00 AM     Number of Views: 5272

The purpose of the VLOOKUP function is simple: it looks up data in tables and returns results from a different column. So if you have a table of products, for example, you could ask VLOOKUP to return the price for an item given the ID of the product.

But VLOOKUP is more than just that; it is the gateway to real Excel knowledge. The VLOOKUP function contains everything that a function can throw at you: multiple required parameters, optional parameters with defaults, and needs both ranges and numeric data in its input strings. If you can master this function, you can master ANY other function in Excel.
...

### Easy Outlook Email Integration

by Published on 2012-01-23 04:52 AM     Number of Views: 3620

Over several years of participating in forums, and working on my own projects, I always felt it was a bit awkward to create and send new emails through Excel. Invariably, every time I found that I needed email code, I ended up heading off to a site to copy an example (usually from my colleague Ron de Bruin's excellent site), then customizing to make it work.

The goal of this article is to provide an even easier way to add email functionality to your Excel (or any other Office) project... something easy enough for beginner coders to use as effectively as master coders. I wanted a re-usuable chunk that I could just drop into my project with ease, and I believe I've accomplished that here.
...

### Prevent Printing In A Workbook

by Published on 2011-12-06 04:36 AM     Number of Views: 1823

If you'd like to prevent anyone from printing your workbook, this code will do the trick (subject to the caveat below).
...

### Trigger Conditional Formats Before Printing

by Published on 2011-11-11 06:56 AM     Number of Views: 3004

My staff and spreadsheet users will tell you that any time I build a spreadsheet, there are always shaded cells on the grid. I preach that "Green means go", and make sure that any cell they enter data in has a green background. I also use blue backgrounds for "update these sometimes" cells, like tax rates. If cells are left with no colouring, though: everyone here knows that they should be left alone.

Often, I have a few data entry cells or blocks on multiple worksheets. While this follows good spreadsheet design principles, it does have a bit of a side effect: When ...
Page 1 of 6 123 ... Last
• ### Recent Knowledge Base Articles

#### 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.
Ken Puls 2013-03-21, 05:04 AM

#### Categories:

Excel - General Tips  Excel - Charts and Data Visualization

#### 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

#### Categories:

Excel - Formulas  Excel - General Tips

#### 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.
Ken Puls 2013-01-22, 06:30 AM

#### Categories:

Excel - Formulas  Excel - General Tips

#### Using HASONEVALUE in a DAX IF statement

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.
Ken Puls 2012-12-17, 03:50 AM

#### Categories:

Excel - PowerPivot  Excel - PivotTables

• ### Recent Forum Posts

#### Cross Sheet References within cells

Hello
How is the data in Sheet 2 formatted? If the district codes are a string of characters in one cell adjacent to the County, then a VLOOKUP...

Hercules1946 Yesterday, 09:31 PM

#### IF AND Statement with ISBLANK and conditional

Hello Emartz
Have a look at the attachment. Ive added a couple of columns to include the date when the Container # is input, and the number of days...

Hercules1946 Yesterday, 07:42 PM

#### IF AND Statement with ISBLANK and conditional

Hi-

I'd like to have cells turn RED via a conditional based on the following:

If the column called CONTAINER # does not have...

emartz 2013-05-17, 09:00 PM

#### Cross Sheet References within cells

I have a list of hospitals in different counties. Based on the county, I want a column to display the appropriate district names within that county. I...

Connor Dickey 2013-05-17, 07:03 PM

#### Increase Cell Value Daily

I doubt that you would be able to do this with one cell.

I would suggest in B1 enter the start date, in B2 enter the start value then in...

royUK 2013-05-17, 01:09 PM