Adding Power to Excel Logical Formulas Part II: SWITCH first published in RCM Advisor, the Journal of the Healthcare Billing and Management Association, Volume 25 Second Quarter 2020. This issue introduces another new Excel logical function, SWITCH. The SWITCH function is a simple way to evaluate one logical test against multiple potential results. Similar to the IFS function, SWITCH evaluates the logic in order, so be careful to arrange your formula in the order you want Excel to test.

Adding Power to Excel Logical Formulas, Part I: IFS first published in RCM Advisor, the Journal of the Healthcare Billing and Management Association, Volume 25 First Quarter 2020. Eight years ago, one my earliest articles for RCM Advisor (this publication was then called Billing) was an introduction to the IF function in Excel. IF is a powerful way to add conditional logic to your spreadsheet. IFS is even more powerful, and it is easier to add multiple conditions than the old IF statements were.

Arrays Part 6 first published in RCM Advisor, the Journal of the Healthcare Billing and Management Association, Volume 24 Fourth Quarter 2019. We will build on the array formulas from Part V by adding criteria to the third next available patient formulas.  Criteria can make your ability to pull and manipulate data even more powerful.  This article will also provide ideas on how to allow users to choose the criteria the array formula acts on.  The power, flexibility, and application of array formulas is limited only by your imagination.

Arrays Part 5 first published in RCM Advisor, the Journal of the Healthcare Billing and Management Association, Volume 24 Third Quarter 2019. Welcome back to our extended discussion about using arrays in Excel formulas.  This article builds on the complexity of our discussion of arrays.  Please review the first four articles in RCM Advisor to refresh your memory on arrays.  This article will show another example of using arrays to calculate a common medical practice request and introduce arrays that span multiple cells.

Arrays Part 4 first published in RCM Advisor, the Journal of the Healthcare Billing and Management Association, Volume 24 Second Quarter 2019. Read more about using arrays in a medical practice.  You can really leverage your formulas to add criteria once you understand how Excel uses addition and multiplication in IF statements.  This article will show you how add collections for a given year at multiple locations with a single formula, without using a Pivot Table.  You can use AND and OR criteria to make all kinds of calculations once you understand this usage of arrays.

Arrays Part 3 first published in RCM Advisor, the Journal of the Healthcare Billing and Management Association, Volume 24 First Quarter 2019. Now that we can do three formulas in one array, read more examples of how to use arrays in your practice.  These examples will help remove errors from formulas and allow you to calculate sums and averages even if there are issues with your data.  Part of the trick is understanding formulas like IF.  Another part of the trick is to see how an array can make your life easier.  Put formulas you understand into arrays you can use by reading this article.

Arrays Part 2 first published in RCM Advisor, the Journal of the Healthcare Billing and Management Association, Volume 23 November – December 2018. This article continues our discussion of arrays.  The last article had examples of two formulas at the same time.  This article begins with more examples of two formulas at the same time.  Once that works, we will progress to three formulas in one array that can make your spreadsheets more powerful and accurate.

Introducing Arrays first published in RCM Advisor, the Journal of the Healthcare Billing and Management Association, Volume 23 September – October 2018. The last article in RCM Advisor concluded by stating that arrays can make Excel function like LARGE and SMALL even more powerful.  This article will introduce arrays.  Arrays are Excel formulas that can make complex calculations.  There are two types of array formulas: array formulas that calculate a single result and array formulas that generate multiple results.  This article will start on array formulas that analyze multiple cells to create a single result.

Excel Functions Every Excel User Should Know, Part 6 first published in RCM Advisor, the Journal of the Healthcare Billing and Management Association, Volume 23 July – August 2018. This article will add two more Excel functions to our list of functions that can be helpful in analyzing medical practice data: LARGE and SMALL.  Both functions assume you have a list and want to choose a value from the list either starting at the largest (LARGE) or the smallest (SMALL).

Excel Functions Every Excel User Should Know, Part 5 first published in RCM Advisor, the Journal of the Healthcare Billing and Management Association, Volume 23 May – June 2018. The March/April 2018 issue described SUMIF and SUMIFS, two powerful ways to sum cells that meet given criteria. In this issue, we will cover four functions that operate in very similar ways. The functions COUNTIF and COUNTIFS are like SUMIF and SUMIFS, but count based on criteria rather than sum. The functions AVERAGEIF and AVERAGEIFS follow the same approach but average data meeting the criteria. What follows is a brief description of the syntax of each of the four functions along with ideas on the how functions might be used.

Excel Functions Every Excel User Should Know, Part 4 first published in RCM Advisor, the Journal of the Healthcare Billing and Management Association, Volume 23 March – April 2018. The January – February 2012 article on the Articles page introduced IF, a very powerful way to add logic to your spreadsheet.  Once you are familiar with IF, try using SUMIF.  The SUMIF function applies a logical test to a series of cells and sums the cells where the logical test is true.  If you have one criteria to decide which cells to sum, SUMIF is great.  If you have more than one criteria, such as new patient appointments at the north location, then try SUMIFS.  Both functions are described in this article.

Excel Functions Every Excel User Should Know, Part 3 first published in RCM Advisor, the Journal of the Healthcare Billing and Management Association, Volume 23 January – Feburary 2018. This issue describes calendar functions in Excel that can make working with dates much easier.  Learn how to subtract dates to calculate the number of days between dates.  For more involved calculations, discover the NETWORKDAYS function to calculate the number of days between dates while excluding weekends.  Would you like to exclude holidays, too?  Keep reading.  The NETWORKDAYS function can help.

Excel Functions Every Excel User Should Know, Part 2 first published in RCM Advisor, the Journal of the Healthcare Billing and Management Association, Volume 22 November – December 2017. This issue introduces two more Microsoft Excel functions that can add power and flexibility to your spreadsheets.  The first function, ROUND, is an easy way to consistently round numbers on reports.  The next function, WEEKDAY, is a helpful way to quickly determine days of the week and to identify weekends.  Watch for future articles in this series to make your spreadsheets even better.

Three Ways to Manage the Future Using Appointment Data first published in MGMA Connection, Volume 17 Number 7,  September 2017. Think of all the brick-and-mortar businesses you interact with each week.  How would your favorite lunch stop or grocery store be different if management knew how many people would come in tomorrow and what those people would buy?  Would those businesses staff differently, decrease inventory levels, or change operating hours?  Think of the cost savings available to businesses that knew what tomorrow would look like.

Now think of all the information a medical practice has about future patients.  How could a medical practice delight patients, reduce costs, and increase capacity by leveraging appointment information?  Practices can catch and fix problems in future appointments, reduce unsold appointments, increase provider availability, and much more by mining appointment data for insights.  Read this article for ideas to get started.

Excel Functions Every Excel User Should Know, Part 1 first published in RCM Advisor, the Journal of the Healthcare Billing and Management Association, Volume 22 September – October 2017. This issue introduces a new series devoted to basic Microsoft Excel functions every Excel user should know.  Read this article to learn tricks with COUNT, COUNTA, AVERAGE, MIN, and MAX.  Build on these basic functions to develop powerful spreadsheets that quickly tell you what you need to know about your practice.  Watch for future articles in this series to make your spreadsheets even better.

Working with Spreadsheet Tabs, Part 3 first published in Billing, the Journal of the Healthcare Billing and Management Association, Volume 22 July – August 2017. The last two issues of Billing introduced tabs, an efficient way to organize data in spreadsheets. This article will build on the concepts in those two articles and use the same sample spreadsheet you built as you followed along with the earlier articles. Now that you are familiar with tabs, this article will introduce three more advanced tricks.

Working with Spreadsheet Tabs, Part 2 first published in Billing, the Journal of the Healthcare Billing and Management Association, Volume 22 May – June 2017. Spreadsheet tabs can turn a complex, unwieldy spreadsheet that reaches across hundreds of columns and rows into a much more manageable data organizer. This article will build on the basics of working with tabs discussed in the March/April 2017 issue of Billing. In this issue, you will learn how to copy, move, hide, and color tabs.

Working with Spreadsheet Tabs, Part 1 first published in Billing, the Journal of the Healthcare Billing and Management Association, Volume 22 March – April 2017. Spreadsheet tabs are a great way to organize spreadsheet data. Tabs can hold data on months, locations, physicians, and more. This article will introduce the basics of working with tabs—we will insert, delete, and name tabs.  Watch for future issues that cover more tricks of working with spreadsheet tabs.

Freezing Columns first published in Billing, the Journal of the Healthcare Billing and Management Association, Volume 22 January-February 2017. Large spreadsheets with lots of data can make managing that data difficult.  As you scroll down a page with multiple columns, it can be tricky to remember what column contains what data.  The January/February issue is a good time to talk about freezing.  To Excel users, freezing is a way to lock columns and/or row headings so that even when you scroll, the headings stay on the screen to make navigating the spreadsheet easier.

Sparklines Part 2 first published in Billing, the Journal of the Healthcare Billing and Management Association, Volume 21 November – December 2016. Now that you are familiar with Sparklines, read how to change Sparkline options.  Discover how to edit multiple Sparklines at once, control hidden and empty cells, and highlight specific points on your Sparkline.  You can style options to change the look of your Sparklines.  There is also a way to differentiate Sparklines representing dates from regular Sparklines so gaps in dates are more apparent.

Sparklines Part 1 first published in Billing, the Journal of the Healthcare Billing and Management Association, Volume 21 September – October 2016. In this issue I will introduce Sparklines.  A Sparkline is like a chart that fits inside a cell.  Sparklines are a concise way to provide context for a number in a cell and are especially useful when creating dashboards.  There are three different types of Sparklines: Line, Column, and Win/Loss. Read this article to get started.

Excel Charting Tips: Part Four  first published in Billing, the Journal of the Healthcare Billing and Management Association, Volume 21 July – August 2016. This article builds on the foundation of the last three Billing articles introducing Excel 2013 chart tools.  Parts 1 and 2 discussed the new icon tools next to Excel 2013 charts.  Part 3 reviewed options on the Ribbon to provide more power and flexibility to your charts.  With that foundation, we are now ready to discuss one of the more complex Excel charts, a combo chart.

Here is the spreadsheet to accompany the article.

Excel Charting Tips: Part Three first published in Billing, the Journal of the Healthcare Billing and Management Association, Volume 21 May – June 2016.  This article builds on the foundation of the January – February 2016 and March – April 2016 Billing articles introducing Excel 2013 chart tools.  In parts 1 and 2 we discussed the new icon tools next to Excel 2013 charts that provide tools closer to the chart instead of on the Ribbon.  While the new icons next to a chart are convenient, there are ways to customize charts that can only be done through the Ribbon.  This article will focus on ways to customize charts from the Chart Tools Design and Format menus on the Ribbon.

Excel Charting Tips: Part Two first published in Billing, the Journal of the Healthcare Billing and Management Association, Volume 21 March-April 2016. This article continues the January/February 2016 issue’s introduction to Excel 2013 chart tools.  In part one we discussed the new interface for building charts, focusing on the Chart Elements tool.  This article covers the paintbrush icon that controls Chart Styles and the filter icon that controls Chart Filters.

Excel Charting Tips: Part One first published in Billing, the Journal of the Healthcare Billing and Management Association, Volume 21 January – February 2016. Excel 2013 introduced a new interface for building charts. There are three new Chart Tools icons that move frequently used tools closer to the chart. This article start with the Chart Elements icon and gives you everything you need to get started building charts quickly.

Make Printing Spreadsheets Easier: Part Two first published in Billing, the Journal of the Healthcare Billing and Management Association, Volume 20 November – December 2015. This is the conclusion of a two-part series of articles on printing tips and tricks. Learn how the Page Layout tab and the Page Setup Window to customize margins, headers, footers, and more.

Make Printing Spreadsheets Easier: Part One first published in Billing, the Journal of the Healthcare Billing and Management Association, Volume 20 September-October 2015. Though many medical offices are trying hard to go paperless, sometimes it really helps to have a printed copy of a spreadsheet. More and more, printing doesn’t mean paper either. Lots of spreadsheets are distributed as PDF files. Here are some printing tricks to get you started.

Handling Excel’s IFERROR first published in Billing, the Journal of the Healthcare Billing and Management Association, Volume 20 July-August 2015. If you’ve ever presented a spreadsheet that you thought was perfect but were surprised with an error message, this article is for you.

Excel’s Insert Function Tool first published in Billing, the Journal of the Healthcare Billing and Management Association, Volume 20 May-June 2015. A great way to learn how to use a function in Excel is to use the Insert Function (IF) tool. This tool is like a wizard that guides you through the syntax of functions. In this article we will examine the Insert Function tool to show how it can help you with your next spreadsheet.

Absolute vs. Relative References in Excel first published in Billing, the Journal of the Healthcare Billing and Management Association, Volume 20 March-April 2015. The ability to create one formula and copy it hundreds or thousands of times is a great feature of spreadsheets like Microsoft Excel. You can make copying those formulas even more powerful if you understand the difference between absolute and relative cell references in Excel.

Business Intelligence for Medical Practices first published in Billing, the Journal of the Healthcare Billing and Management Association, Volume 20 January-February 2015. Many of the major practice management and electronic health record software programs are written so that data is stored in a relational database management system from Microsoft called SQL Server. Most versions of SQL Server include at no additional cost three programs designed to take advantage of and work with the data stored in SQL Server. Read more about those three programs and how they can help your practice.

Document Your Work With Excel’s Comments Function first published in Billing, the Journal of the Healthcare Billing and Management Association, Volume 19 November-December 2014. If you’ve ever built a complex spreadsheet, set it aside, and then gone back to it several weeks later only to find you can’t remember how the spreadsheet works or what assumptions were built into the logic, comments may be just the thing you’re looking for. Here’s how to do it.

Paste as You Like It first published in Billing, the Journal of the Healthcare Billing and Management Association, Volume 19 September-October 2014. Now that you’re familiar with cutting, copying, and pasting data from the last Billing article, the next step is to learn how to utilize Excel’s Paste Special function. Recent versions of Excel have replaced words with icons that describe Paste Special options – perhaps because it assumes users know about each option. This article will explain what Paste Special is, when to use it, and some of the most frequently used Paste Special icons and options.

Excel Shortcuts to Cut, Copy, and Paste Data first published in Billing, the Journal of the Healthcare Billing and Management Association, Volume 19 July_August 2014. Cutting, copying, and pasting data are some of the most common Excel tasks. This article will discuss four shortcuts to do each of these functions in Excel, helping you uncover the fastest ways to enter data.

Remove Duplicates Quickly and Painlessly in Excel first published in Billing, the Journal of the Healthcare Billing and Management Association, Volume 19 May-June 2014. It is often helpful to review a unique list of patients, procedure codes, or diagnosis codes when analyzing data. Excel makes it easy to quickly remove duplicates, and in this column, I will show you how to do it.

Flash Fill in Excel 2013 first published in Billing, the Journal of the Healthcare Billing and Management Association, Volume 19 March-April 2014. If you have ever found some of Excel’s complex functions and formulas intimidating or time-consuming to implement, this column is for you. One of the powerful new features in Excel 2013, Flash Fill can recognize patterns in data and extract interesting information for you without writing any formulas.

Data Validation first published in Billing, the Journal of the Healthcare Billing and Management Association, Volume 19 January-February 2014. One way to improve the quality of data entry in Excel is to use a feature called data validation, which allows users to apply a variety of rules to determine whether data entered in a cell is usable and appropriate.

Excel’s Financial Functions first published in Billing, the Journal of the Healthcare Billing and Management Association, Volume 18 November-December 2013. Excel has a variety of built-in functions to calculate loan payments, lease payments, and other financial transactions. Get started with this article.

Calculating Weekdays in Excel first published in Billing, the Journal of the Healthcare Billing and Management Association, Volume 18 September-October 2013. Watch how to convert dates of service and other dates in your practice management system to weekdays so you can analyze things like call premium and productivity by day of the week.

Summing Data in Excel first published in Billing, the Journal of the Healthcare Billing and Management Association, Volume 18 July-August 2013. Unlock the hidden power of this often-used function. Just when you thought there wasn’t anything else to the SUM function…

Conditional Formatting Part 4 first published in Billing, the Journal of the Healthcare Billing and Management Association, Volume 18 May-June 2013. We have discussed many ways to use Excel’s conditional formatting feature in the past three issues of Billing. Now that we are familiar with conditional formatting, we will add Icon Sets to make your spreadsheets even more informative.

Sample Conditional Formatting Data for the May-June 2013 Billing article

Conditional Formatting Part 3 first published in Billing, the Journal of the Healthcare Billing and Management Association, Volume 18 March-April 2013. In this issue, we add color scales to make conditional formatting even more powerful.

Sample Conditional Formatting Data for the March-April 2013 Billing article

Conditional Formatting Part 2 first published in Billing, the Journal of the Healthcare Billing and Management Association, Volume 18 January-February 2013. Now that you are familiar with the Highlight Cells rules and some of the formatting options available, take your Excel skills to the next level by working through these examples.

Sample Conditional Formatting Data for the January-February 2013 Billing article

Referral Letters that Improve Patient Care published in the November – December 2012 issue of Connexion. Enhance relationships with referring doctors and enhance patient care by automatically including clinical data and treatment results in referral letters.

© 2012 Medical Group Management Association. All Rights Reserved. Reprinted with permission from the Medical Group Managmenet Association, 104 Inverness Terrace East, Englewood, CO 80112.

Conditional Formatting Part 1 first published in Billing, the Journal of the Healthcare Billing and Management Association, Volume 17 November-December 2012. Conditional formatting is a powerful tool built into Excel that allows you to automatically format cells based on their contents.

Sample Conditional Formatting Data for the November-December 2012 Billing article

Advanced Table Features first published in Billing, the Journal of the Healthcare Billing and Management Association, Volume 17 September-October 2012. This article adds several advanced features to your Tables repertoire.

Sample Tables Data for the May-June, July-August, and September-October 2012 Billing articles

Using Excel Tables to Manipulate Billing Data, Part 2 first published in Billing, the Journal of the Healthcare Billing and Management Association, Volume 17 July-August 2012. Now that you are familiar with the basics of tables, take your skills to the next level with features found in the Table Tools Design Tab.

Sample Tables Data for the May-June, July-August, and September-October 2012 Billing articles

Using Excel Tables to Manipulate Billing Data first published in Billing, the Journal of the Healthcare Billing and Management Association, Volume 17 May-June 2012. If you are still waiting to make the jump from prior versions of Excel to Excel 2007 or 2010, tables are one of the best reasons to upgrade.

Sample Tables Data for the May-June, July-August, and September-October 2012 Billing articles

Use Excel to Manipulate Patient Names and More first published in Billing, the Journal of the Healthcare Billing and Management Association, Volume 17 March-April 2012. To concatenate means to connect or link together, and the CONCATENATE function in Excel does just that. Use it to combine patient names, addresses with city, state, and zip, and much more.

Using Data Wisely published in the April 2012 issue of Connexion. Don’t just attest for incentives, make your data work for you. © 2012 Medical Group Management Association. All Rights Reserved.

Reprinted with permission from the Medical Group Managmenet Association, 104 Inverness Terrace East, Englewood, CO 80112.

If first published in Billing, the Journal of the Healthcare Billing and Management Association, Volume 17 January-February 2012. If you’re ready to take your Excel skills up a notch, IF is a great function to start with.

Moore Filling Works Great first published in Billing, the Journal of the Healthcare Billing and Management Association, Volume 16 November-December 2011. If you haven’t used the fill handle in Excel, you’ll be filled with thanks for the tons of time this little trick will save you.

How to Find (and Replace) What You’re Looking For first published in Billing, the Journal of the Healthcare Billing and Management Association, Volume 16 September-October 2011. If you still haven’t found what you’re looking for in your spreadsheet, you’ve found the right article. Mastering find and replace in Excel can save you hours and make your spreadsheets more accurate. Get started with this article today.

Measure Meaningful Use published in the October 2011 issue of Connexion. Use Excel’s Remove Duplicates feature to calculate meaningful use measures.

© 2012 Medical Group Management Association. All Rights Reserved. Reprinted with permission from the Medical Group Management Association, 104 Inverness Terrace East, Englewood, CO 80112.

Using Microsoft Excel’s Pivot Table Feature to Turn Medical Billing Data into Actionable Practice Knowledge first published in Billing, the Journal of the Healthcare Billing and Management Association, Volume 16 July-August 2011. Change the way your practice analyzes data with an example of how to create a Pivot Table. Learn how to sort, filter and group data to see your data like you’ve never seen it before.

Introducing Microsoft Excel’s Pivot Table Feature originally published as a web-exclusive article in the November-December 2009 issue of Connexion. Learn how to create a basic Pivot Table in Excel 2007 to powerfully and easily group, filter and sort billing data into meaningful reports.

© 2009 Medical Group Management Association. All Rights Reserved. Reprinted with permission from the Medical Group Managmenet Association, 104 Inverness Terrace East, Englewood, CO 80112.