Excel University Blog

Read on for in-depth articles, tutorials, and videos. Search or browse for specific topics. Be sure to subscribe if you'd like to be notified when we write something new.

Posts

Announcing Guest Lectures

By Jeff Lenning | February 19, 2015 |

I’m pleased to announce the introduction of Excel University guest lectures! Until now, all of the Excel University courses were written by a single instructor (me). Moving forward, Excel University will also offer guest lectures by my Excel friends and recognized Excel experts. They will be talking about their areas of interest and expertise. Guest…

Read More
Slicers by Jeff Lenning

Slicers as an Alternative to Conditional Drop Downs

By Jeff Lenning | February 9, 2015 |

Beginning with Excel 2013 for Windows, we can use Slicers to filter table data. In this post, we’ll explore how to use Slicers as a relatively easy alternative to conditional drop-downs. Objective When we want to allow a user to select a choice from a list of items, we often consider using the Data Validation feature to…

Read More

Customize Conditional Formatting Icon Sets

By Jeff Lenning | February 5, 2015 |

The conditional formatting feature of Excel is one of my favorites. In this post, we’ll customize a default rule to create alert icons for our journal entry log that indicate which entries are out of balance. Objective Our objective is to create an alert when a journal entry is out of balance. Since this is Excel, there…

Read More

Center Across Selection QAT

By Jeff Lenning | January 22, 2015 |

Excel’s Merge and Center command has an icon, but, it is often better to Center Across Selection instead of merging cells. Unfortunately, the current version of Excel doesn’t have a Center Across Selection Ribbon icon. In this post, we’ll create a simple macro that we can activate with a Quick Access Toolbar (QAT) icon. Objective Let’s…

Read More

PivotTable Report Group Formatting

By Jeff Lenning | January 14, 2015 |

In this post, we’ll check out a few PivotTable formatting techniques that help get our report looking just right. Objective Our objective is to create the following PivotTable report. There are a few formatting points to note about the report. First, the region groups, such as Midwest and Northeast, are in the same column as the…

Read More

Income Tax Formula

By Jeff Lenning | December 31, 2014 |

In this post, we’ll examine a couple of ideas for computing income tax in Excel using tax tables. Specifically, we’ll use VLOOKUP with a helper column, we’ll remove the helper column with SUMPRODUCT, and then we’ll use data validation and the INDIRECT function to make it easy to pick the desired tax table, such as single or married…

Read More

Indirectly Refer to Table Columns

By Jeff Lenning | November 20, 2014 |

Previously, we explored using the INDIRECT function to refer to various tables in a workbook. In this follow-up post, we’ll expand the discussion and refer to individual table columns. Objective Let’s start with our objective. We have several tables in a workbook. They have the same structure and store department data. For example, here is…

Read More

Referring to Tables Indirectly

By Jeff Lenning | November 13, 2014 |

In this post, we’ll allow the user to select a table name from a data validation drop-down and our Excel formulas will operate on the values from the selected table. Thanks to Neelima for asking about this technique! Objective Before we get too far, let’s be clear about our objective. We have several data tables in…

Read More

Excel University V3 Course is Available!

By Jeff Lenning | October 31, 2014 |

I am pleased to announce the immediate availability of the Excel University Volume 3 online course! This online course teaches the content of Volume 3 through online videos, homework activities, quizzes, and includes instructor-moderated forums in case you get stuck. It picks up right where Volume 2 ends. More Info   The topics¬†include PivotTables, external…

Read More

Heat Maps in Excel

By Jeff Lenning | October 29, 2014 |

In this post, we’ll recreate a heat map presented in Stephen Few’s Information Dashboard Design, 2nd Edition, using an Excel PivotTable and a bit of conditional formatting. Objective Before we get too far, let’s take a look at our objective. We are trying to create a heat map that uses variation in color intensity to…

Read More