Excel Boot Camp

 

WIth Excel Boot Camp articles and videos, you’ll find more ways to customize the conditional formatting to get just the results you, your physicians, and your administrators need to see.

Excel Boot Camp #119 - Shortcuts to make the percentage calculations easier

Excel Boot Camp #119 has two shortcuts to make the percentage calculations we’ve been discussing easier to choose.  Percentages in Pivot Tables are a great way to give context to your data.  Try a percentage in the next Pivot Table you build.

Excel Boot Camp #118 - Video calculating the percentage of the parent column total

Excel Boot Camp #118 is very similar to Excel Boot Camp #117, using parent columns instead of rows. It can be a little confusing to understand how Excel is doing these parent calculations, so I did one more video calculating the percentage of the parent column total instead of the parent row total like we did in Excel Boot Camp #117.  Here’s the link to Excel Boot Camp #118

Excel Boot Camp #117 - New feature for having multiple fields in the Row Labels area

The percentages we discussed last time work great, but having multiple fields in the Row Labels area complicates things just a little in Excel Boot Camp #117. Fortunately, there’s a new feature starting in Excel 2010 to help.

Excel Boot Camp #116 - Introducing trends over time by using percentages in your Pivot Table

Excel Boot Camp #116 introduces a powerful way to look at trends over time by using percentages in your Pivot Table. Instead of putting numbers in the values area of the Pivot Table, today we’ll replace the numbers with percentages to see how the mix of clinic locations has changed over time.

Excel Boot Camp #115 - Calculations in a Pivot Table

In Excel Boot Camp #115 we’re ready to start talking about calculations in a Pivot Table. Today we’re primarily working with the Values area in the Pivot Table. You can drag multiple fields to the Values area and you can even drag the same field to the Values more than once. Once you have the fields you want in the Values area, click the drop-down arrow next to the field and choose Value Field Settings. It’s easy to sum, count, or average the values in your Pivot Table. There are several other options as well, including max and min.

Excel Boot Camp #114 - Three ways to make your Grouped Items expand or contract

Excel Boot Camp #114 will review three ways to make your grouped items expand (show detailed data) or contract (show summary data). You can click the plus and minus buttons next to each group to expand or contract the group. If you don’t like the plus and minus buttons, I’ll show you how to turn them off. Whether or not the plus and minus buttons are displayed, you can also double click the group name to expand and contract the field. Finally, if you have lots of groups, I’ll show you how to expand or collapse all of the groups at once.

Excel Boot Camp #113 - Grouping Groups

If you liked the ability to group text in the last Boot Camp, you’ll like the additional grouping options we’ll discuss today in Excel Boot Camp #113. The first trick we’ll discuss is how to group groups. You can create levels of grouping to get exactly the analysis you’re looking for.

Excel Boot Camp #112 - How to group text in a Pivot Table

Now that you’re familiar with grouping dates, watch Excel Boot Camp #112 to see how to group text in a Pivot Table. When we grouped dates in Boot Camp #111, we grouped by month and year so Excel knew the names of the months and called the new field “Years” for us. When we group text, Excel doesn’t know how to name the groups, so it calls the group Group1. Watch how easy it is to rename Group1 to something more meaningful, like PCP.

Grouping is a great way to organize and deeply understand your data.  Get started today.

 

Excel Boot Camp #111 - Grouping is another powerful way to analyze data in Pivot Tables

Grouping is another powerful way to analyze data in Pivot Tables and it’s the topic of Excel Boot Camp #111. We’ll start our discussion of grouping data by using a date. Notice how when I click on a date in a Pivot Table Excel recognizes that it’s a date and gives me several ways to group the date. Also notice that I can choose more than one way to group. In the video I’ll group my dates by month and by year.

Excel Boot Camp #110 - Best of using Pivot Tables

If Excel Boot Camp #110 doesn’t cover the best feature in a Pivot Table, it’s certainly in the top three. Pivot Tables are great for data analysis, but if you’re like me, you’ve had the experience of taking a report to a physician or an administrator and having them say, “Your numbers aren’t right.” The beauty of a Pivot Table is that you can double-click any of the values in the Pivot Table and Excel will open a new worksheet tab with a copy of the data that makes up that cell. Excel gives you the entire row of your source data, so it’s easy to do further analysis.

Excel Boot Camp #109 - Search Box Filter

The search box filter can make your filtering a lot more flexible.  Learn how in Excel Boot Camp #109.

Excel Boot Camp #108 - Search Box
Watch Excel Boot Camp #107 - More clever tricks with Slicers

Watch Excel Boot Camp #107 today to see several more clever tricks with Slicers. You’ll see some formatting tricks and a time saving way to connect a Slicer to filter multiple Pivot Tables at once.

Excel Boot Camp #106 - Slicers (part 1)
Excel Boot Camp #105 - Top 10 Lists in Pivot Tables

Excel Boot Camp #105 has way to get Top 10 Lists in Pivot Tables. Just because it’s called Top 10 doesn’t mean you’re limited to top or 10. You can easily select the bottom 5 instead of the top 10. You can also choose whether you want items, percent, or sum. If you choose Top 10 items, Excel gives you the 10 labels with the highest values. If you choose Top 10 percent, Excel gives you the top labels that make up 10 percent of the total. If you choose Top 2000 sum, Excel will give you as many labels as it takes to get at least 2,000. In other words, how many of my biggest patients did it take to collect $2,000?  Top 10 is a very handy filter for analyzing your practice.  Put Top 10 to work today!

Excel Boot Camp #103 - See how Excel customizes your filter options

Watch Excel Boot Camp #103 to see how Excel customizes your filter options based on the type of data in your rows and columns. When we looked at label filters in the last Excel Boot Camp, we had a text field in the Row Label field we were filtering, so Excel gave us text based filtering options. Watch what happens when we put a date field in the Row Labels area. Instead of begins with and ends with, Excel gives us options to filter by today, tomorrow, this week, next month, last quarter, and much more. Excel recognizes that the date field we’re filtering is a date and gives us date-based options to filter with.

Excel Boot Camp #102 - Introducing Label Filters.

Excel Boot Camp #102 adds some real power to your filtering skills by introducing Label Filters. Label Filters allow you to filter by whatever is in the Row or Column Label. In our example today, we’ll filter text data. It’s easy to select labels that begin with, don’t begin with, end with, don’t end with, contain, don’t contain, etc. anything you’re looking for. For example, if you have a list of providers and you want to find all of the physician assistants in the list, you could filter on labels ending with PA. If you’re looking for all of the E&M codes, you could filter for labels beginning with 99.

Excel Boot Camp #101- An easy way to filter your Pivot Table data.

Excel Boot Camp #101 shares an easy way to filter your Pivot Table data. There are several ways to filter, or limit the amount of data in your Pivot Table. Today we’ll discuss the easiest way to filter, simply by checking boxes. I call this way of filtering a manual filter because you manually check and uncheck the items you want in your Pivot Table. Manual filters work for both the fields in the Row Labels and Column Labels areas and for fields in the Report Filter area. Watch how I check the Select Multiple Items box so that you can choose more than one item to filter by in the Report Filter area.Excel Boot Camp #101 shares an easy way to filter your Pivot Table data. There are several ways to filter, or limit the amount of data in your Pivot Table. Today we’ll discuss the easiest way to filter, simply by checking boxes. I call this way of filtering a manual filter because you manually check and uncheck the items you want in your Pivot Table. Manual filters work for both the fields in the Row Labels and Column Labels areas and for fields in the Report Filter area. Watch how I check the Select Multiple Items box so that you can choose more than one item to filter by in the Report Filter area.

Excel Boot Camp #104 -- showcases a third way to filter your Pivot Table data, Value Filters.

Excel Boot Camp #104 showcases a third way to filter your Pivot Table data, Value Filters.Where Label Filters focused on what was in the row or column label, Value Filters look at the data in the Values area of the Pivot Table. It’s easy to filter for values that are equal to, greater than, less than, or between whatever numbers you’re looking fo

Excel Boot Camp #100 - Manually sorting data

Welcome to Excel Boot Camp #100!  I hope you have found Boot Camp very helpful.

I missed posting last week to update my Aloha shirt collection in Oahu but am back for #100 tonight. When sorting A-Z or Z-A isn’t good enough, watch Excel Boot Camp #100. We’ll discuss how manually sort data, a particularly handy trick if you are trying to use a Pivot Table to replace an existing report.

Excel Boot Camp #99 - Sorting Pivot Table data

Sorting Pivot Table data, as we’ll do in Excel Boot Camp #99, is much easier than sorting regular data in Excel. You’re probably aware that if you sort regular data in Excel you have to be very careful to select the entire range of data to sort in order to avoid scrambling your data. Pivot Tables are easier. Excel recognizes Pivot Tables as one unit and you only have to click on one cell to sort data.

Excel Boot Camp #98 - Pivot Tables

If you’ve wondered why Microsoft calls it a Pivot Table, watch Excel Boot Camp #98 today to see how easy it is to pivot rows and columns around a value. It’s easy to add and remove data from a Pivot Table. It’s just as easy to move data between rows and columns.

Excel Boot Camp #97 - Introducing Pivot Tables

By popular demand, the next topic for Excel Boot Camp is Pivot Tables. We will start with an introduction to creating Pivot Tables for Excel Boot Camp #97.  I hope you are finding boot camp helpful.

Excel Boot Camp #96 - Conditional Formatting Wrap Up

Excel Boot Camp #96 wraps up our discussion of Conditional Formatting with a discussion of the Stop If True box in the Conditional Formatting Rules Manager. Use Stop if True when you want to apply the first successful Conditional Formatting rule instead of all true rules.

I hope you have found this section of Excel Boot Camp to be very helpful for you in your practice.

Excel Boot Camp #95 - Multiple Conditional Formatting rules continued

Excel Boot Camp #95 continues our review of what Excel does when multiple Conditional Formatting rules are applied to the same cell or range of cells. Excel has a Conditional Formatting Rules Manager window that shows all of the conditional formats applied to a given cell or range of cells. If the formats conflict, such as the first rule shading cells red and the second rule shading cells green, Excel applies the higher ranked rule in the Rules Manager. You can easily change the priority of the rules with the up and down buttons.

Excel Boot Camp #94 - How to apply more than one conditional formatting rule

Excel Boot Camp #94 shows you how to apply more than one conditional formatting rule to the same group of cells, making your conditional formatting toolbox a lot broader.

Excel Boot Camp #93 - Conditional formatted cells in your workbook

Excel Boot Camp #93 helps you find conditionally formatted cells in your workbook and clear conditional formatting you don’t need. It is a short video and an easy shortcut.

Remember to text EXCEL to 33550 to join MGMA Stat and vote on the next topic for Excel Boot Camp. Watch for the text message from Stat next week to vote. You don’t have to a be a member of MGMA to join Stat, but if you already participate in MGMA Stat, you still need to text EXCEL to 33550 so you can vote.

Excel Boot Camp #90 - Top or Bottom Rules work in Conditional Formatting

Excel Boot Camp #90 is a straight-forward demonstration of how the Top or Bottom Rules work in Conditional Formatting. It’s easy to format the highest or the lowest items in a list, or with one click, you can highlight the highest or lowest percentage of values in a list.

Excel Boot Camp #88 - Formatting cells based on their values
Excel Boot Camp #86 - Icon sets
Excel Video 56 - How to conditionally format cells based on a formula

Excel Video 56 shows how to conditionally format cells based on a formula. If the formula is true, Excel formats the cell. It is a bit complex, but once this trick works for you the sky is the limit in terms of what you can make conditional formatting do.