MrExcel Excel Videos

Sparklines are a great way to fit a trend into a small space. Episode recap: Sparklines are word-sized charts See the book Beautiful Evidence by Edward Tufte Use Sparklines to fit data in a small space Build a second pivot table from the original data Put the data for the sparklines in the pivot table Remove the grand totals from the pivot table Select the numbers for the sparklines Choose Insert, Sparklines, Column Select where you want the sparklines to go Make the column wider to make the sparklines larger You can also make the row height taller You can even (gasp) merge cells to make the sparklines wider Change the color of the high and low point If you want the sparklines to be scaled together, change the axis minimum and maximum

Slicers are visual filters for a pivot table. Pivot Filters have been around They have to live near the pivot table They are awful when you want to select multiple items Visual filters were introduced in Excel 2010 as Slicers When you insert slicers, they are tiled in the middle of the screen You can change number of columns Arrange the size Change the colors Great for multi-select

Building a pivot chart to summarize a data set I start with a pivot table. If you want years to go across the chart, make them go down the Rows area Change Field Settings to Show Values as a Percentage of Row Format the pivot table as % with no decimal places Create the pivot chart Immediately Cut, Ctrl+Home, Paste Formatting the chart: Adding a Title at the top Triple click the title to edit in place Select second series. Ctrl+1. Narrow the Gap Width. Add Data Labels to only the second series

Icon Sets debuted in Excel 2007 In Excel 2010, they added a set with Up, Flat, Down But an icon set can only look at one cell! How can you use it to compare two cells? Copy two helper columns off to the side Use a calculation to show the change Type that formula without using the mouse or arrow keys to prevent =GETPIVOTDATA Use the SIGN function to convert that to +1, 0, -1 Add the Three Triangles icon set to the helper cells Why do they call it Three Triangles, when it is really 2 triangles and a dash? Manage Rules, Edit the Rule. Show Icon Only. Manage Rules, Edit the Rule. Change from percent to numbers Use greater than 0 for Green up arrow Use greater than or =0 for yellow dash all of the negative numbers will get the red down...

Rather than use VLOOKUP to compare lists, you can solve it with a pivot table. Episode Recap: You have three lists to compare. Time for lots of VLOOKUP?! There is a far easier way Add a "Source" column to the first list and say that list came from List 1 Copy List 2 beneath List 1 Copy List 3 to the bottom of both lists If you have more lists, keep going Create a pivot table from the list Move the Source to the columns area Remove the grand total You now have a superset of items appearing in any list and their answer on each list After the credits, a super-fast-motion view of MATCH, VLOOKUP, IFERROR, MATCH, VLOOKUP, IFERROR old way of solving the problem

A great trick from Szilivia Juhasz. If you want to create a pivot table for each sales rep or each product line or each whatever, you can use the Show Pages feature. Episode Recap: There is an easy way to replicate a pivot table for each rep, product, whatever Create the pivot table To replace the Row Labels heading with a real heading, use Show in Tabular Form To replace Sum of Revenue, type a space and then Revenue Provided you select all value cells and the grand total, you can change the number format without going to Field Settings Greenbar effect using Design, Banded Rows, and then choosing from the Format gallery Add the "for each" field to the Filter area Go to the first Pivot table tab in the ribbon (Options in 2007 or 2010...

Running totals, both in regular data and in Ctrl+T tables. Recap: This episode shows three ways to do running totals The first method has a different formula in Row 2 than all the other rows The first method is =Left in row 2 and =Left+Up in rows 3 through N If you try to use the same formula, you get a #Value error with =Total+Number Method 2 uses =SUM(Up,Left) or =SUM(Previous Total,This Row Amount) SUM ignores Text so you don't get a VALUE error Method 3 uses an expanding range: =SUM(B$2:B2) Expanding ranges are cool, but they are slow Read the Charles Williams whitepaper on Excel Formula Speed The third method is a problem when you use Ctrl+T and add new rows Excel can't figure out how to write the formula The workarounds require...

When you use Ctrl+T tables, the references in formulas are easier to read. VLOOKUP is awesome and my favorite function VLOOKUP haters complain that it is fragile due to the 3rd argument If the shape of your lookup table changes, the answers can change One workaround is to replace third argument with MATCH But imagine doing a MATCH for 1000 rows of VLOOKUP Make your lookup table into a table before doing the VLOOKUP The structured table reference will handle if the table shape changes Plus it does not require doing a MATCH over and over Peter Albert submitted this tip

In this episode, more about Ctrl+T tables. See how they can improve VLOOKUP, Charts, and Data Validation. Episode Recap: In the last episode, Ctrl+T made the pivot table source expand This also helps VLOOKUP and Charts and Data Validation Although it is slightly different in each Create your VLOOKUP then make the table a Ctrl+T table Remarkably the VLOOKUP formula will rewrite itself Build a chart. Make the source data a Ctrl+T table. Add new months. For Data Validation Source: Make it a table and then name the range without the heading Use the named range as the Validation Source Also mentioned in the episode: FORMULATEXT function for showing a formula