Excel Video 34 Filtering Text in Tables
Excel Video 34 covers filtering text in tables. Filtering text is more straight-forward with fewer options than filtering dates or numbers, so I’m including a couple of other tricks in this video as well. Watch the demonstration of using custom filters to see how to...
Excel Video 33 Filtering Dates in Tables
Excel Video 33 demonstrates some of many methods Excel offers to filter date fields in tables. When you filter a field containing dates, Excel recognizes the dates and customizes your filter options accordingly. The date filtering options you’re used to seeing in...
Excel Video 32 Totals in Tables
Welcome back for more Excel videos! Excel Video 32 shows you how clicking the Total Row the Table Tools Design Tab puts a total row at the bottom of your table. The cool thing is that next to each cell in the total row is a drop-down arrow with a variety of choices...
Excel Video 31 Adding Data to a Table
Excel Video 31 demonstrates how easy it is to add data to an existing table. If you need to add more rows of data, simply go to the bottom of the table and start typing. Excel will carry over the formatting of the existing table and include the new data in your...
Excel Video 30 Introducing Tables in Excel 2007
Excel Video 30 introduces Tables in Excel 2007. Tables are different than the Pivot Tables we’ve been discussing, but have many similar features. We’ll start by simply creating tables. If you already have a Pivot Table, creating a table is easy, simply double click...
Excel Video 29 Quick Pivot Charts from Existing Pivot Tables
All of the Pivot Chart examples we’ve been through in prior videos involved creating a Pivot Chart and the related Pivot Table from scratch. What if you already have a Pivot Table you’ve been using and want to create a Pivot Chart? You don’t have to start over. Watch...
Excel Video 28 Pivot Chart Example
This is one of those videos that may take a minute to load. Excel Video 28 is an extended example of how to use a Pivot Chart. There are a couple of ideas demonstrated that I hope you’ll find helpful. We look at E&M codes by the coding level (the last digit of the E&M...
Excel Video 27 Introduction to Pivot Charts
I hope by now you’re a fan of the power and flexibility of Pivot Tables. Now that you’ve seen some of the things Pivot Tables can do, imagine a chart or graph in Excel tied to the Pivot Table so that any time you add a column, change a filter, or update your Pivot...
Excel Video 26 Grouping Numbers in Pivot Tables
We talked about grouping data in prior videos, but there’s another grouping trick that will come in handy. When Excel sees you’re grouping dates, it offers a number of ways to group the dates, such as by month, quarter, and year. When Excel recognizes you’re grouping...
Excel Video 25 Printing and Data Options
Excel Video 25 wraps up our discussion of the Pivot Table Options Menu by discussing the Printing tab and the Data tab. The Printing tab has three pretty straight-forward check boxes that control whether to print the expand/collapse buttons on a printed Pivot Table...
Excel Video 24 Pivot Table Display Options
Excel Video 24 walks through the Display tab of the Pivot Table Options menu. There are a couple of highlights worth mentioning. Watch the video to learn what a contextual tooltip is and how to turn them on or off. For those of you who have used Pivot Tables before...
Excel Video 23 Grand Totals and Custom Lists
Excel Video 23 is the second part of our coverage of the Pivot Table options menu. Most of the time, you’ll want to include grand totals in the rows and columns of a Pivot Table, but if you’ve got data like PSA scores, total PSA scores may not be meaningful. Here’s an...
Excel Video 22 Pivot Table Options, Part 1
Excel Video 22 starts another set of videos. Part 1 shows you where the Pivot Table Options menu is and covers the first tab, Layout & Format. These formatting options are helpful when you’re formatting a Pivot Table to print, when you’ve got a lot of filters at the...
Excel Video 21 A Little More on Consolidating Pivot Tables
After the long Excel Video 20, here’s a short video to show how flexible the consolidation process is. I simply take the same data that we used before, where all three ranges had exactly the same Profit and Loss categories, and changed one of the categories in one of...
Excel Video 20 Format and Design Tricks for Consolidated Pivot Tables
First off let me apologize for the length and file size of Excel Video 20. I try to keep these videos just a couple minutes in length and a smaller file size that downloads relatively quickly. The formatting and design tricks in Excel Video 20 took a while, so Excel...
Excel Video 19 Using the Pivot Table Wizard to Consolidate Multiple Ranges
Here’s part 2 of our series on consolidating multiple Excel ranges into a single Pivot Table. Lesson 18 added the Pivot Table Wizard to the Quick Access Toolbar and prepared the data to consolidate. This video goes through the Pivot Table Wizard to actually create the...
Excel Video 18 Consolidating Multiple Ranges into One Pivot Table Part 1
When I spoke at the MGMA Annual Conference in Denver, several people asked me if there was a way to consolidate data in multiple ranges, either in different parts of the same spreadsheet, different tabs on the same spreadsheet, or in different spreadsheets altogether,...
Excel Video 17 Top x Value Filter
Happy New Year! I hope you enjoyed the break. We’re starting 2010 with an easy example to get back into Excel. There’s a cool feature under value filters that allows you to filter for the top or bottom x items, percentage, or sum of items in your table. If you want to...
Excel Video 16 Pivot Table Calculated Fields
In Lesson 13 I showed how use GETPIVOTDATA to create formulas outside the Pivot Table based on Pivot Table data. In Lesson 14, I showed another way to create formulas using Pivot Table data. Both formulas were outside the Pivot Table looking into the data. Lesson 16...
Excel Video 15 Refreshing Pivot Table Data
Lesson 15 demonstrates how to refresh Pivot Table data. It’s critical to realize that once Excel creates a Pivot Table, it doesn’t automatically go back to look at or refresh the data the Pivot Table is based on. Instead, Excel keeps the original data in the Pivot...