Spreadsheet data makes analyzing large sets of data, or even smaller day-to-day data, much easier. Having the ability to easily manipulate data that has been formatted in the optimal database style report can make it much easier to find insights and information from that data. Here are a few ways that formatting your data correctly will make your data analyzing experience more efficient.

Data Diving, Sorting, And Filtering

One of the most notable ways this database style format makes things easier is that it gives us a simple way to dive data.

If you place data into the spreadsheet like in the following example, it becomes difficult to filter as different categories are under the same column header.

Difficult to filter database
Difficult to filter database

By having each item in its own column, it’s much easier to filter. Now we are able to tell more easily how many hamburgers to order, as well as all the different drink types.

Easy to filter database / clean data
Easy to filter database / clean data

This was only a simple example, but what if we make it more complex and say that we wanted to see their lunch orders by day? If we pull this “name level” data by day, we can see multiple dates show up in a single column, but the name associated changes. We would easily be able to filter to a single data and see what everyone ordered, or we can filter to a specific name and see what they ordered all week.

Filter data by name
Filter data by name

Data Pivoting

Pivot tables can be very powerful exploration tools. They allow for easy filtering and sorting and allow you to set your columns. You can even set them up to show the data you want and they will take data from a single source. But just like technology, once you get it, your current data will be quickly outdated by the new.

The real power comes from the single source of data. If you replace the data in the source data, the pivot table will reflect the new data! So, if you have a specific data report that you download consistently that has the same column headers every time, you would only have to pull that report and replace that data over the source data. Once you refresh the pivot table, all of your data is updated! You don’t even have to change your pivot table at all to update the data.

If you know how to use web queries, this becomes even more powerful. Simply set up your web query to pull in data to a single tab in your spreadsheet. From there, have your pivot table reference that tab of data. Once you trigger a data pull from the web query, be sure to refresh your pivot table and–viola!–you have your most up-to-date data in the format in which you set up your pivot table!

Efficient Reporting Next Steps

There are too many more spreadsheet tips and tricks out there to talk about in a single article, but I hope that this helped you get started on your way to trying out new ways of reviewing your data.

Look forward to additional articles in this series as we deep dive into each of these options.

In case you missed it, here is the first post in our series on Presenting Your Reports More Efficiently.


Also published on Medium.