Excel is an incredibly useful tool that has stood the test of time since it began in the mid 1980’s as one of the go-to spreadsheet tools across a wide variety of industries and use cases. Within Excel itself there are a good range of features, and Excel reporting is a cornerstone in many data presentations to this day. However, the Excel report view does have certain limitations that can make data presentation a little more complicated. Let’s take a look at the different options for creating report tables in Excel, along with their pros and cons.
What are Excel reports?
The first thing to understand is exactly what reports in Excel are. This generic term doesn’t apply to any one particular function that Excel carries out, but rather is an overall term for presenting all the collected information on one sheet clearly and concisely.
Generally, an Excel report will display a portion of the full spreadsheet in a way that is easier to understand than the entire dataset. It often includes some amount of data manipulation to answer a certain question. For example, a report generated on sales per day data may present this information as the average daily sales per month, or total monthly sales. Manipulating the data by adding all the days of a month together, or averaging them over monthly periods, allows them to be more quickly and easily understood in the report view.
Create a report in Excel as a table
A table is a specific way of formatting data and can be used as part or all of a report in Excel. In the simplest terms, a table is a set of rows and columns in a spreadsheet. Excel does provide several options for making more customizable tables, with the Format as Table and Pivot Table features.
Raw data table
The most basic way to generate reports from Excel data is to use the rows and columns of raw data as the report table without any additional steps. This method makes perfect sense if you are dealing with a very small and straightforward dataset, like the one below. Because it is easy to see and comprehend all the data, there is no need to manipulate it to make it clearer, reveal trends, or format it so it is easy to visualize on a single page.
If you want options for more specialized formatting and easy to apply formulas, you can highlight the relevant cells and go to home - “format as table” for additional customization options for your raw data table. This feature used to be called “Excel lists” and allows you to easily format things like headers, banded rows and totals for rows and columns.
Pivot table
When you start to deal with more complicated and larger data sets, basic raw data table reports become problematic. You might begin wondering how to create a summary report in Excel. Something that will let you summarize the raw data to help answer specific questions and make it easier to understand the overall trends in a larger data set. The pivot table is a feature of Excel that allows for much easier creation of summary tables and reports using more complex data sets. Let’s look at a specific pivot table example below.
In the example above, the raw data set is larger and more complicated, with multiple variables per person. It is harder to understand and interpret this table as a report than our first example. The pivot tables given to the right focus on a particular part of the dataset to show how two variables interact with each other much more clearly. By doing a small amount of data manipulation (calculating averages of heights) and comparing them directly to baskets scored and successful limbos, the data trends are much clearer than in the raw data. It is much more obvious in the pivot table that the taller people got more baskets and shorter people did better in limbo competitions.
How to create a pivot table in Excel
Now that you can see how useful pivot tables can be, let’s go through the steps needed to create one. The instructions given will be for creating the pivot table used in our example above. Exactly which variable you want to highlight and what manipulations make the most sense to see overall trends will vary wildly depending on your dataset.
- Create your raw data set - pivot tables work best if there is a single type of variable along the vertical axis (like people in our example above). There can then be as many variables about these as desired along the horizontal axis (i.e. height, baskets, and limbo).
- Go to Insert - Pivot Table
- Select the data range desired to create the table from - it is likely to be the entire data range, but pivot tables can be created from smaller subsections of your data if desired.
- Choose if you would like the pivot table to appear on the same sheet as the data or on a different sheet - a different sheet will work better as a report without additional formatting
- In the Rows field - select the variable field you want to appear in the vertical rows, farthest to the left (limbos successful in the first pivot table, baskets in the second)
- In the values field - select the variable you want to manipulate using one of the set Excel functions (like add, subtract, or average) - in this case, it was height, and we wanted to average it.
FluenceXL: Taking Your Data Farther
Tables can be a very useful way to display data in an Excel report. Excel itself has a lot of options for basic tables, but as you get larger datasets with more and more variables, the options become limiting.
FluenceXL is a plugin that works directly with Excel but increases the capabilities of basic Excel to make them much better at generating reports to show large and complicated data with great visualization and ease. Check out our website for more about everything we have to offer.
