Excel, one of the key tools in the Microsoft Office suite is a great small business software tool for analyzing business data. With it, you can take an in-depth look at your data and answer questions that you have about your business. Excel provides handy functions and features that you can use to analyze large quantities of data. We’ll show you what they are and how to use them.
 Simplify data analysis by using the Subtotal option to group and total data. (Click for larger image). |
Move Your Accounting Data into Excel
Of course, your business data probably exists in your accounting software or your other small business software, so before you begin you need to get it into Excel. How you do this depends on the program you’re using and whether it interfaces with Excel or not. In some cases, you can export data from your accounting software in an Excel-compatible format; so choose the Excel file format when exporting.
Excel can also import data from a database if you select Data > Import External Data > New Database Query. You can use this method to import data from an Access database into Excel.
In other situations, export the data from your accounting software as a .csv format file or some other format that Excel can read. To see what formats Excel can read, choose File > Open; then click Files of Type and note the formats listed. Then check your software to see which of these formats your program can use as an export format.
Once exported, open the data file in Excel and start by saving it as an Excel format file (if it was exported using a different file format). Depending on your data and the questions you want to answer, there are various ways to analyze it.
Subtotal a List in Excel
The simplest way to work with data is to consolidate it and subtotal it by the field you’re interested in analyzing. For example if your table includes invoice data, you can summarize the invoices by customer if you first sort the data in customer order. To do this, click somewhere in customer name column and, on the Home tab click Sort & Filter > Sort A to Z.
 The filter feature provides lots of sifting options for gleaning info from data that’s formatted as a table. (Click for larger image). |
You can check to see that your data isn’t formatted as a table by clicking in it; if the Table Tools tab appears, click it, click the Design tab, and then choose Convert to Range before proceeding.
To add subtotals, click somewhere in the data and choose Data Tab > Subtotal. Next, select the column you sorted from the At each change in dropdown list. Then select the Function (such as Sum) and the fields to subtotal, and click OK. The data will then be grouped and subtotaled. Use the group and ungroup indicators on the left of the data to hide and display groups.
Use Tables in Excel
You can also analyze columns of data using a list or table. In Excel 2003 the feature is called Lists, and in Excel 2007/2010 it is a Table.
Click somewhere inside the data and, in Excel 2007/2010 choose the Home Tab > Format as Table and select a format to use. You’ll need to confirm that all your data is selected, and if the top row contains column headings, select the My Table Has Headers checkbox, and click OK. In Excel 2003 choose Data > List > Create List.
Sort and Filter Excel Data
When you create a list or a table, Excel adds filters to the column headings. Use these to sort your data in ascending or descending order. You can also filter the data. For example, if you have a category column, you can use the filter button to filter the data by category.
Select the categories you’re interested in from the list, and then disable the checkboxes for any categories that you don’t want to see. Next, click OK to view a subset of your data. To view all the data again from the Home tab, choose Sort & Filter > Clear.
To filter using more detailed criteria, click the filter dropdown list and choose Filters — depending on the type of field you have selected this may be a text, date or number filter. Now choose from various operators such as Equals, Does Not Equal, and Contains — or choose Custom Filter to create your own filter. For example you could view transactions between a range of dates or for all customers in a range of zip codes.
 A PivotTable lets you answer complex questions by selecting and rearranging the displayed data. (Click for larger image). |
Total the Results
Lists and tables include a handy total-row feature. You can access it in Excel 2007/2010 by clicking somewhere in the table and choosing the Table Tools > Design Tab on the ribbon. In the Table Style Options group, select the Total Row checkbox. In Excel 2003, right-click inside the list and, choose List > Total Row.
When you click in any cell in the total row, you’ll see a list of functions that you can automatically apply to that column. For example, the Sum function will add a Subtotal function to that cell which, unlike Sum, calculates the total value of visible cells only. Use these total row functions to summarize the filtered data.
PivotTables in Excel
You can also analyze your data as a PivotTable. To do this, choose Insert > PivotTable, and then select the table or range to use. Next, select the option to place the PivotTable on a new sheet. Now, drag-and-drop the fields to analyze into the task pane.
In some cases, you may want to analyze by week or month, and you can do this if you add your date field as a column or row label, then right-click that label and choose Group. From the By dropdown list, select Days or Months and, if you selected Days, enter 7 as the Number of days to summarize by week.
PivotTables give you the power to answer complex questions simply by rearranging your data. For example you can answer a question such as “How much product did we ship each month and by which shipping companies?” and then compare it to the actual numbers of orders that represents.
Remember, the Excel tools you choose to perform your analysis will vary for each situation depending on what sort of data you have and the information you want to glean from the data.
