With the Analytics Edge add-in for Microsoft Excel, you can easily include data from existing worksheets in your automation macros. This lets you process virtually any data you have downloaded from any source. The Read Worksheet function wizard makes it simple, and has several options to handle a variety of common scenarios. Follow it with other Analytics Edge functions to quickly transform the detailed data into segmented and summarized reports without programming or formulas.
Why you need the Read Worksheet function
You get data from various systems in the format they give it to you; often with little choice as to the column selection, header rows, or cell formatting. Once you load it into Microsoft Excel, you are faced with the challenge of stripping out the unwanted columns, rearranging the remainder, changing column names, reformatting numbers or converting dates, and maybe filtering or pivoting the results for the summary analysis you were looking for. Analytics Edge can automate all these steps, and it starts by reading the data itself.
While you could read from a downloaded text file using the Read Text Filefunction, I prefer to start with something I can see, and loading the data into a worksheet gives me confidence that my analysis started with good data. If something strange appears in the analysis, I can easily refer to the detailed data.
Workbooks, Worksheets and Selections
Analytics Edge can refer to data in other worksheets as well as worksheets in other workbooks. To refer to another workbook, that workbook needs to be open when you build the macro, but not when you run it; Analytics Edge will open it automatically when the macro is run. Simply select the workbook and the worksheet from the drop-down lists.
Read the entire worksheet as a single table
If your data is nice and cleanly formatted in columns with a header row in row 1 and nothing else in the worksheet, then select this option. Analytics Edge will automatically detect the columns, column names and load all the data, regardless of how many rows or columns are contained in the worksheet.
Read a table in the worksheet
If your data is contained in an isolated table on the worksheet (a group of rows and column surrounded by empty cells — it does not have to be formatted as an Excel “table”), but does not start in cell A1 or there is other data on the worksheet, you can use this option. Enter the top-left cell of the data or click the reference button and click on the cell in the worksheet to auto-enter it.
Read a specific range only
If you want to grab specific cells from the worksheet that may be surrounded by other data, use this option. Enter the cell range in the format of “A2:C3” or click the reference button and select the range desired.
Header Row Is Optional
Sometimes data is delivered without a header row; there are no column names provided, or you want to grab data from the middle of a worksheet. No problem; just check the box Data does not include a header and Analytics Edge will automatically assign columns names of Col A, Col B, etc. to your loaded table. Of course, you can easily change them using the Arrange wizard.
Turning the World On Its Side
What if the data is not provided as columns in a table? What if it appears as rows in a report with headers at the left? No problem; check the box Data is transposed and Analytics Edge will automatically swap the rows for columns as it is loaded. This is really useful for reading existing trend reports or profit/loss statements.
Loading Into Named Tables or Assigning Range Names
A couple of advanced features of Analytics Edge are the ability to hold a set of data in memory so it can be referenced later in the macro (see Table Name). The entire table can be held by assigning it a table name, and checking the boxLoad direct into a named table will do just that, using the worksheet name.
You could alternatively check Assign range names to each column to make the various columns available to the macro as range names. This would allow you to use the specific values in cells as variables in other macro functions. This feature makes it easy to create a configuration worksheet with things like date ranges or value settings that you want used in the macro, creating easily customized reports without modifying the macros.
Ways to use the Read Worksheet function
Reading imported data – In the simplest form, download your data to a worksheet, then automate your analysis and report updates with Analytics Edge. All the detailed source data is included in the workbook, so anomalies can be investigated or alternative analysis can be performed with ready access to the original source.
Reading existing reports – sometimes the data you need comes from another report, so Analytics Edge makes is easy to extract the information you need, regardless of how it was formatted. Being able to transpose data as it is read means you can still use the data, even if it was pivoted or provided with a row-orientation.
Reading configuration settings – custom reporting is one step easier with Analytics Edge. Read values like a desired date range or filter values right from a worksheet. The macro can use these values to tailor your analysis.
Simple automation with Analytics Edge
Reading data from Worksheets is just the start of automating your report updates. Stop manually reformatting your downloaded or imported data and let Analytics Edge automate the process. Keep your focus on your task, not the tool.