February 2009 Newsletter
Formatting HARP Report Files in Excel
HARP reports provide essential information for managing your A/R. In the last issue of the Quadax newsletter, you learned about downloading report files and importing them into Excel®. This article will cover some of the formatting options that will improve the look and readability of report spreadsheet files. Keep in mind that we are referring to report files in the comma-separated value format and not the actual HARP report.
Column headings. When you first open a report file in Excel, most reports show the report title, number, and other information in the first row, followed by column headings in the second row. It is easier to work with data when column headings are first, so delete the first row. (If you chose to begin importing report data at the second row, then the column heading should already be on top.) But before deleting the row, consider including unique report information (report name, number, date, etc.) in the worksheet name, which appears on the worksheet tab, or the file name.
To rename a worksheet, right-click the tab, and then Rename, or on the Format menu, point to Sheet and then click Rename.*
* The specific paths in Excel in this article are for Excel 2003. In Excel 2007, many of the options are displayed on the Ribbon so will be easier to find.
Changing the look of all cells in the worksheet. If you want to apply changes to all the cells in the worksheet, an easy way to select all of them is to click the square in the upper left corner. This square is the Select All button. You can then go to Format > Cells and change the font size, color, border, alignment, etc.
Fitting columns to data. When you open the spreadsheet, the complete column headings or column data may not be in sight because the columns are too narrow. A quick way to expand all of them at once is to click the Select All button in the upper left corner, and then double-click the boundary between any columns. This method will also shrink the columns to fit. For example, if you change to a smaller font size, click the Select All button again, select the font size, and then double-click the column boundary.
Data format. When you imported the file into Excel, you selected a broad formatting category for data (number, text, etc.). Now you can apply specific formatting to the numbers and dates by selecting Format > Cells > Number tab.
- For dollar amounts, select the Currency category, and then the number of decimal places and negative number format. If you prefer to have the decimal points and dollar signs aligned, select the Accounting category instead of Currency.
- For percentages, change the cells to the Percentage category, and then specify the number of decimals.
- For dates, change the cells to the Date category, and then choose the date format.
Freeze Panes. For long or wide reports, you can select rows (typically the column headings) or columns that will always be displayed while you scroll through the data. To freeze a row or column, select the row beneath or the column to the right of the one that should be frozen, and then choose Window > Freeze Panes.
Sorting. Excel allows you to sort data in ways that are different from the original report sort criteria in HARP. For example, you can sort a Charge Summary that includes referrals by the year-to-date charge amount to easily see the highest performing physicians. To sort, first select a range of cells. In most cases, the range should be all the cells because you want to preserve the data associations in each row. Select all data by clicking the Select All button, and then choose Data > Sort. The Sort By drop-down menus list all the column heading for the sort selection. You can also choose to sort in ascending order (0 to 9 or A to Z) or descending order (9 to 0 or Z to A).
Filtering. Another powerful feature available in Excel is the ability to filter data. Filters allow you to see a subset of data that matches specific criteria while retaining all the data on the spreadsheet. Filtering just hides rows you do not want displayed. You can edit, format, chart, and print the subset without affecting other rows on the report.
To activate filtering, select Data > Auto Filter. An arrow will appear next to each column heading. When you click the arrow, a list of items in the column is displayed. From the list, select the item you want to see. For numbers or dates, the Top 10 option opens a box where you can specify the number of items or percent of total items to show. You can apply filters to multiple columns.
Immediately after data is filtered, the results of the filter are displayed in the lower-left corner of the status bar (ex. 412 of 2155 records found). After a time, the number of records changes to Filter Mode, indicating that some data is hidden. The filter arrow in a filtered column changes to a different color so it is apparent which columns have been filtered.
To show all data on the worksheet again, select Data > Filter > Show All, or Show All in the filter arrow list.
I hope this article provided some useful tips about how to customize HARP report spreadsheets to show the data you want. The next article in this series will deal with pivot tables.