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.
Features
In this post, we’ll explore a method for generating a drop-down that contains a unique list of choices derived from a table column with duplicate values. Objective Before we get started on the mechanics, let’s take a look at what we are trying to achieve. We have a data table that is exported from our…
In this post, we’ll explore three methods for transposing data in Excel. The first method can be used when you just want to quickly to transpose the values manually. The second method can be used when you want formulas to perform the transposition automatically based on the labels. The third method can be applied when…
Generally, I prefer to prepare financial statements with formulas and functions such as SUMIFS because there are no layout or structure limitations imposed. This means I can design the report to my exacting specifications. However, it is interesting to note that PivotTables support basic formulas, and using a PivotTable formula to create a calculated item…
Generally, you can improve your speed by keeping your hands on your keyboard. But, what do you do if there is no built-in keyboard shortcut to execute your favorite command? Well, one approach is to customize the QAT. This post discusses the Quick Access Toolbar and the related keyboard shortcuts it creates. QAT Shortcuts Microsoft…
The Table feature of Excel remains one of my favorites. This post explores one very specific aspect of the feature, the total row. Overview One of Microsoft’s greatest gifts of Excel 2007 was the introduction of the table feature. It allows us to convert an ordinary range into a table (Insert > Table, or, Ctrl+T).…
This post explores options for formatting cells that are locked, or unlocked, in an Excel worksheet. Scenario Let’s pretend we have a worksheet that helps a user compare three different loans. The user is required to enter information, such as interest rate and number of years, into designated input cells, but shouldn’t be allowed to…
Hi guys! Just wanted to draw your attention to my April 2014 Journal of Accountancy article that discusses the power of mapping tables. If you’ve not experimented with this technique, it is worth checking out. In summary, a mapping table sits between the data and report sheets and enables you to translate labels and aggregate…
This post demonstrates how to set up a PivotTable to show 100% on the subtotal lines when using the show values as a percentage of parent total option. Overview Before we begin, let’s review our objective. We want to summarize our data by region, and within each region, by rep. We also want to show…
This post explores macro-free methods for using Excel’s data validation feature to create an in-cell drop-down that displays choices depending on the value selected in a previous in-cell drop-down. Overview As with just about anything in Excel, there are several ways to achieve the goal. This post explores three such solutions, and if you have a…
This post discusses ways to retrieve aggregated values from a table based on the column labels. Overview Beginning with Excel 2007, we can store data in a table with the Insert > Table Ribbon command icon. If you haven’t yet explored this incredible feature, please check out this CalCPA Magazine article Excel Rules. Frequently, we…