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

Outline Groups

By Jeff Lenning | June 27, 2013 |

Let me ask you a question.  How do you hide a row or a column? Since this is Excel we are talking about, there are several methods to accomplish this goal. Common Approaches One common approach is to select the row or column, and then select the Hide command, either from the Format Ribbon icon…

Read More

Excel University Volume 2 Table of Contents

By Jeff Lenning | June 13, 2013 |

We are getting close to the release of Excel University Volume 2!  I’m absolutely excited about this volume.  We cover the mechanics for setting up hands-free reports. The manuscript is with the publisher now, going through a round of copy-editing.  I expect it to be released in July or August on Amazon.  I want to…

Read More

Keyboard Shortcuts: Precedents

By Jeff Lenning | May 30, 2013 |

You know I love keyboard shortcuts.  I believe that keeping your hands on your keyboard will improve your speed.  I recently came across two very cool shortcuts I’d like to share in this post. Precedent First, let’s get a term cleared up.  A Precedent is something that comes earlier, or before.  In Excel, this idea…

Read More

Count Distinct Values in an Excel 2013 PivotTable

By Jeff Lenning | May 16, 2013 |

This post demonstrates how to count the number of distinct (unique) values in an Excel 2013 PivotTable.  Prior to Excel 2013, this capability was not built-in to the PivotTable feature.  For Excel versions earlier than 2013, there are a variety of different workarounds available, some use VBA code, some use helper formulas, and some of…

Read More

MSN MoneyCentral IQY Web Query

By Jeff Lenning | May 9, 2013 |

Note: see this post for instructions on using the new stock quotes system in Excel. I happened to notice that the MSN MoneyCentral Investor Stock Quotes IQY file did not install automatically with Excel 2013.  I’m not sure if this just occurred on my system, or, if this is by design. In any event, I’d like to…

Read More

Excel 2013 Highlights

By Jeff Lenning | May 2, 2013 |

Microsoft Excel 2013 offers many new features and functions worth exploring. This post provides a few highlights and a link to my May-2013 CalCPA Technology and Business Resource Guide article, “Spreadsheet Nerdiness” which goes into more detail. Summary Design With the exception of some minor cosmetic changes and some animations, the design is similar to…

Read More

Go To Special Places in Excel

By Jeff Lenning | April 25, 2013 |

Have you ever called up the Go To dialog in Excel? There is a little button in it that enables you to go to special places in Excel. This post explores some of these special destinations. For starters, let’s call up the Go To dialog within an empty workbook. There are a few different ways…

Read More

Data Validation Alert Styles

By Jeff Lenning | April 18, 2013 |

The Data Validation feature allows you to control what a user can type into a cell. This post digs into the details of the data validation alert style options. Data Validation Primer First, a quick overview of the Data Validation feature. Data Validation has been in Excel for a long time, and it is one of…

Read More

Perform Approximate Match and Fuzzy Lookups in Excel

By Jeff Lenning | April 11, 2013 |

This post explores Excel’s lookup functions, approximate matches, fuzzy lookups, and exact matches.  The built-in Excel lookup functions, such as VLOOKUP, are amazing. When implemented in the right way for special projects or in recurring use workbooks, they are able to save a ton of time. The VLOOKUP function alone has saved countless hours in…

Read More

Make an Excel Worksheet Very Hidden

By Jeff Lenning | April 4, 2013 |

Did you know that Excel has two levels of hidden worksheets?  Excel has “hidden” worksheets, and, “very hidden” worksheets. This post walks through the differences, and how to hide worksheets at each level. By default, all new worksheets are visible.  A visible worksheet’s tab appears in the bottom of the Excel window, enabling the user…

Read More