• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel Tabular Data Format

You are here: Home / Excel / Excel Tabular Data Format
Excel Tabular Data Format
October 30, 2013 by Mynda Treacy

Today I want to share with you an Excel secret. It’s so powerful that when you know this your Excel life will become much easier.

I say it’s a secret because I get A LOT of questions from people struggling to use Pivot Tables, and functions like SUMIFS, COUNTIFS etc.

Most of the time they’re struggling for one reason…

Their data is in the wrong format.

I’ve identified 5 , oops, make that 6 main data format categories based on what I see most often.

Let’s first look at the perfect data format so you know what to aim for.

Excel Tabular Data - The Perfect Format

Excel Tabular Data Example

Tabular data is typically at a granular level of detail. Often transactional i.e. every row represents one transaction.

As is the case with the example above, where every row represents one order and has the related data for that order in a single row i.e. country, salesperson, units and amount.

When you start at this bottom level of detail it’s easy to summarise or slice and dice your data into any number of combinations with PivotTables, SUMIFS, COUNTIFS etc.

But when you skip this step and start with data that has already been summarised it is much more difficult to change the summary format, and it often means you can’t use a lot of the built in Excel tools and functions in the way they were intended.

You might think you're saving time by skipping this step but it will eventually come back to haunt you.

The rules for tabular data:

  1. Every record is housed on one row.
  2. Each Column contains a type of data e.g. date, order number, quantity, amount, salesperson, region etc.
  3. There are no blank rows or columns. Note: blank rows aren’t the end of the world but they will make your life more difficult than it needs to be.
  4. Column labels are in one cell per column and located on the first row. Note: they don’t have to be on the first row, but they need to be in one row only. i.e. not split over multiple rows.
  5. There are no subtotals interspersed in the data.

Now that you know the rules for perfect tabular data let’s look at some examples that break these rules so you know what not to do.

The Semi-report

A semi-report is data that has had some degree of summarising already applied.

Like in the example below we can see that the data has been summarised into regions (UK and USA) as these have their own column labels, as opposed to the ideal tabular data format above which has a column label for ‘Country’ and the actual country/region is recorded on each row.

Excel Tabular Data Example

Signs of a semi-report (some or all of these will be present):

  • Column labels are on two rows.
  • Column(s) for Totals.
  • Columns for time periods (weeks, months, years) or other grouping of data.
  • Blank rows or columns.
  • Rows containing subtotals and or a Total.

What’s wrong with a Semi-report:

  • You can’t use this data in a PivotTable because a PivotTable can’t have blank columns or duplicate column labels, and column labels must be on one row only.In the semi-report above row 3 has ‘units’ and ‘order amount’ repeated. Note: this can be fixed with a Flat Data table – more on that below.
  • You can’t easily change the way the data is summarised. For example what if your boss wanted you to change the view so that it was summarised by year like the example below?

limitations of changing a report

You can do this in a PivotTable in seconds when your source data is in a tabular format but it’s not as easy when it’s in a semi-report format…I’m not saying it’s impossible, it’s just more complicated than it needs to be.

Flat Data Table

A flat data table is similar to a semi-report in that it has some degree of summarisation already applied, however it doesn’t have blank columns or column labels on two rows, which means you can use the data in a PivotTable report.

Excel flat data table Example

Signs of a Flat Data Table

  • Column labels are on one row.
  • Columns for time periods (weeks, months, years) or other summary levels like regions, departments etc.
  • No blank rows or columns.
  • No rows containing subtotals and or a Total.

Whilst the flat data table can be used in a PivotTable it still has limitations because there is already a degree of summarisation in the data.

That is; there is a limit to how much you can manipulate the data in your PivotTable. For example you can’t create the PivotTable report below using the Flat Data table above, but you can with the first Tabular Data example:

limitations of pivoting a flat data table

Note: you could use some complicated formulas to summarise the flat data table into the above report format, but why make your life difficult when you can do it more easily with tabular data.

Data Entry Format

The data entry format gets its name due to the intuitive layout which makes it easy for the person keying in the data.

However it doesn’t give any consideration to further analysis of the data.

This is perhaps the most frequent mistake I see people make and the one which I get the most questions from because people try to use functions they think should work, like SUMIF(S) and COUNTIF(S) etc.

However when your data is in the wrong format you can't use the built in functions the way they were intended.

data entry format

Just imagine this data spans more than 100 columns (data for years 2011 to 2013) and your boss has asked you to tell him the total ‘Order Amt’ for UK and total for USA.

Sure you can do it with a complicated array formula, but if you had recorded your data in a Tabular data format you could create the above report and all the totals in a matter of seconds using a PivotTable.

And you could quickly calculate the total sales for UK and USA with a couple of SUMIF formulas referencing the Tabular data like this:

=SUMIF(Table1[Country],"USA",Table1[Order Amount])

=SUMIF(Table1[Country],"UK",Table1[Order Amount])

Note: my formulas above use Structured References because my tabular data is formatted as an Excel Table.

Excel Tables have many advantages from quickly writing formulas to automatic set up of dynamic ranges. If you aren't familiar with them I highly recommend you learn how to leverage their benefits.

Report Format

The worst type! Often 3rd party systems attempt to ‘help’ by providing you with a series of default reports, all nicely formatted of course!

Unfortunately these reports often fall short of what you actually need and manipulating them into a format so that you can further analyse the data can be time consuming.

report format

If you have a system that spits out data in a report format and you end up spending hours manipulating it to get what you actually need, find out if there is a way to get the transactional data i.e. tabular data, out of the system and import it into Excel.

Rest assured it’ll be there (after all it’s creating reports for you from this very data), you just have to find a way to get it out.

Then you can use the tools God gave you, I mean Microsoft gave you and wield your Excel magic to get what you really want.

Multi-Sheet or Multi-Workbook

I can't believe I initially forgot this format.

This is where you have a separate worksheet (tab) or workbook for each month, year, region, department, salesperson etc.

multi-sheet format

multi-workbook format

You might also have some of the other data formats present on each of these sheets/workbooks, but the mere fact that you have split your data into separate sheets/workbooks makes things very difficult to summarise or compare.

Since Excel 2007 you have more than 1 million rows to play with. It can handle a fair bit of data. You are only making things difficult if you split it over multiple sheets or workbooks.

Instead add a new column to your source/raw data sheet for the date/region/salesperson, whatever the category is that you have separate sheets for, and put the information from the tab name in the cells of your new column.

Think Tabular format - each row represents a record.

If you split it into multiple workbooks you cause even more problems for yourself and often end up with bloated files full of links to external places.

Tip: If your source data isn't going to change then you don't need a live link to it. Just bring the data into your analysis file as a value and analyse it from there.

Summary

Excel was designed to work with data in a tabular format. Tools like PivotTables and many of the functions work best with tabular data.

This is the format I recommend to my dashboard course members when building dashboards, because when your data is in a tabular format you can easily build dynamic reports that are quick to update.

I hope that from this you will have understood the difference between the different data formats and know that the best by far is Tabular data.

That’s not to say you can’t work with data in any of the other formats, it’s just that you’re making extra work for yourself if you do.

So on your head be it!

Converting Data to Tabular Format

If you suffer from any of the data problems above I have a few options for you:

  1. Use Power Query to unpivot and clean the data.
  2. Convert data with this Reverse PivotTable trick.
  3. Reverse engineer the data with Jon Acampora's Formula method.

Download

Download the file and play around with analysing the different formats.

See how easily you can analyse the Tabular data compared to the other formats.

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Click here to Download the Excel Workbook.Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

Excel Tabular Data Format

More Excel Posts

excel templates

Where to Find Free Excel Templates

Where to find free Excel templates and how to create your own Excel templates. Using templates saves time and effort.
Easily Remove Password Protection from Excel Files

Easily Remove Excel Password Protection

How to remove Excel password protection when you’ve forgotten the password. Works for sheets, workbooks and read only files.
Import data from a picture to Excel

Import Data from a Picture to Excel

Import data from a picture to Excel. Works with pictures from a file or the clipboard and loads it to the spreadsheet.
excel online

5 Excel Online Features Better than Desktop

5 Excel Online Features Better than Desktop including searchable data validation, track changes, single line ribbon and more.

10 Common Excel Mistakes to Avoid

10 common Excel mistakes to avoid, including merge cells, external links, formatting entire rows/columns and more.
new Excel features

Cool New Features in Excel for Microsoft 365

Cool New Features in Excel for Microsoft 365 including the navigation pane, smooth scroling, unhide multiple sheets and more.
dynamic dependent data validation

Dynamic Dependent Data Validation

Dynamic Dependent Data Validation with dynamic array formulas like FILTER make it quick and easy to set up.
QAT

Excel Quick Access Toolbar

The Excel Quick Access Toolbar is not only a handy for your mouse, but it also enables some super easy keyboard shortcuts.

Share and Collaborate in Excel

Share and Collaborate in Excel just like Google Sheets! Show changes, custom views, threaded comments with @ mentions and more.
Workbook Protection

Excel Workbook Protection

Excel Workbook protection can prevent your users from breaking your reports while still allowing interaction with Slicers and refreshing.
Category: Excel
Previous Post:sports league tables excel dashboardExcel Football Dashboard Extreme Makeover
Next Post:Favourite Excel ShortcutsFavourite Excel Shortcuts

Reader Interactions

Comments

  1. Scott Petitjean

    December 14, 2022 at 6:22 am

    I need help writing a formula(s) that will convert flat data to tabular data. I am not able to upload or paste a picture illustrating my predicament, but I have one available. Can someone help me out? I’m willing to pay for a workable solution. Scott

    Reply
    • Mynda Treacy

      December 14, 2022 at 8:45 am

      Hi Scott,

      I wouldn’t use a formula for this. You should use Power Query as explained here: How to unpivot data with Power Query.

      Hope that points you in the right direction.

      Mynda

      Reply
  2. Jon Peltier

    October 6, 2021 at 12:09 am

    I always tell people, they can spend five minutes with their data first, or spend five hours trying to make their chart conform to the wrong data layout. Thanks for your detailed explanations.

    Reply
    • Mynda Treacy

      October 6, 2021 at 9:13 am

      🙂 nicely put, Jon!

      Reply
  3. Ivy

    April 1, 2021 at 7:39 pm

    Hi,
    thanks for sharing these excel information. Would like to know if we are able to convert data entry/ semi report in your examples into tabular format using power query?

    Reply
    • Mynda Treacy

      April 1, 2021 at 7:54 pm

      Yes, you can see this video on Power Query Unpivot Scenarios.

      Reply
  4. Matt Ferguson

    March 31, 2020 at 4:27 am

    I know this maybe a redundant point and you may have answered it in your Multi-sheet, so hopefully this will just add more clarity to that question. I thought it would be beneficial to pull out data that was overly redundant in one spreadsheet and then reference it, when needed, in another. For instance, the Salesperson is in the Technology Department and is in the Southwest Region. S/He has over a 1,000 records. Doesn’t it save space to have the Department and Region broken out and merged when needed?

    Reply
    • Mynda Treacy

      March 31, 2020 at 9:29 am

      Hi Matt,

      If you’re using Power Pivot to model your data, then yes. If you’re using regular PivotTables or any other type of formula based analysis, then it’s more efficient to have the data included in the one table as a static value i.e. not a formula. One of the most common causes of file bloat is a VLOOKUP formula bringing data in from one table to another.

      Mynda

      Reply
  5. Sal Carr

    October 4, 2019 at 7:50 am

    In a big enough tabular sheet the second column convert will throw an error, I went around it by using IFERROR and rounding down, instead of UP.

    What happens is that the MOD will be 0 at certain cells, the you get the DIV/0 error.

    =IFERROR(INDEX($B$1:$U$1,ROUNDUP(MOD(ROWS($A$2:A121),COUNTA($B$1:$U$1)+0.1),0)),INDEX($B$1:$U$1,ROUNDDOWN(MOD(ROWS($A$2:A121),COUNTA($B$1:$U$1)+0.1),0)))

    Reply
    • Mynda Treacy

      October 5, 2019 at 8:03 pm

      Hi Sal,

      I’m not sure what this comment has to do with this post.

      Mynda

      Reply
  6. Beth Lewis

    July 6, 2018 at 11:42 pm

    Hi Mynda – your dashboard course has been “life altering” for me and my teammates! I have shared your tutorials with so many folks, and my company is certainly reaping the benefits of your tips and tricks! I have something of a problem though – because I have too much data to take it to a tabular format. Also, the structure of our data and underlying architecture creates a grouping versus a summing need. Here are high level examples.

    First, I have summary, “grouped products” in my data architecture. The Product level has a 1:M relationship with the Program level. And when a client has the Product, they also have all of the Programs. So Product 1 has Programs A, B, C. The client has 10 employees and all three programs are available to each. I need to count how many employees have Programs, but a “belly button” is a belly button – so I only ever have 10 employees eligible for Product 1, or Program A, B, C. Additionally, each client purchases 1:M Products (which can have a 1:M Program relationship too.) I really need the counts of employees grouped versus summed, and I’m having a hard time figuring out how to roll things up and down given the belly button count need.

    Second, I have a scenario in my data like Norms. So there are natural summary categories associated with my clients – region, state, payment categories, client type. My company sells many types of products and I am trying to determine penetration rates by the summary categories, but again, the 1:M thing is causing me to get a multiplier effect in all my charts… I realize I can only accurately calculate penetration at the summary levels, so maybe if I can figure out how to count belly buttons at various levels, it will solve this challenge.

    I would love to know what thoughts you might have, as I am certain you have run across this issue in the past. I have millions of rows of data per month, so bringing tabular data in at the one row one record level just isn’t feasible. Also, as a member of the Product organization, I can’t just drop the offending attributes from my data! Because we count the employees, and each client purchases 1 or more products and the employees are eligible for all of the purchased programs, I cannot summarize employees at any level but the lowest right now…and THAT doesn’t lend itself to analysis as I have ~300 programs!

    Thanks for your thoughts – Beth

    Reply
    • Mynda Treacy

      July 7, 2018 at 7:14 am

      Hi Beth,

      It’s great to hear we’ve been able to help 🙂

      You need Power Pivot! Simple as that. And probably Power Query to convert the data into a tabular format and then load into Power Pivot, which is not constrained by the 1.5M row limit of Excel worksheets.

      You absolutely must convert your data to tabular, from there you can use Power Pivot to create the summaries etc. that you need.

      Kind regards,

      Mynda

      Reply
  7. Nevena

    March 25, 2018 at 10:03 pm

    Thanks for the easy-to-understand explanation! Could you please give some advice how to clear data automatically that has empty rows or rows where not all fields are filled according to the tabular headers (some empty cells, then some scattered data, then empty rows, then a range of correctly filled in rows)?

    Reply
    • Mynda Treacy

      March 26, 2018 at 8:47 am

      Hi Nevena,

      To automatically clean your data you need to use either VBA or Power Query. If you have some sample data you could post your question and Excel file on our forum where we can give you an example with Power Query.

      Mynda

      Reply
  8. Sahat Gebima Sihotang

    October 31, 2017 at 4:37 pm

    where do you get the tabular data ?
    can u share the link or u can email me ? 🙂

    Reply
    • Mynda Treacy

      October 31, 2017 at 8:33 pm

      Hi Sahat,

      There is a link to the sample file containing the tabular data used in the example above at the bottom of the post under the heading ‘Download’.

      Mynda

      Reply
  9. Luis J Azuaje

    March 25, 2016 at 1:25 am

    Mynda: this is a Great article .The main problem with people using excel is that they want to create outputs ( Final reports) instead of learning on how to work with data: Data needs to be tabular with rows and columns and not a single cell empty in the table. Excel becomes your worst enemy and it cannot do its magic if you create outputs and try to perform analysis your boss is expected to have the report ready in 2mins…Great insigth

    Reply
    • Mynda Treacy

      March 25, 2016 at 1:39 am

      Thanks, Luis. Glad you appreciate this article 🙂

      Mynda

      Reply
  10. Mark Atkinson

    September 24, 2015 at 9:07 pm

    Some very useful information here. Only complaint was that the potentially brilliant Power Query will not work unless you have some obscure version of Office 2010 – why do Microsoft make what could be really useful tools incredibly inaccessible? 🙂

    Reply
    • Mynda Treacy

      September 24, 2015 at 9:09 pm

      Hi Mark,

      Power Query will work in all Excel 2010 desktop SKU’s. It’s Power Pivot that’s more difficult, and or Excel 2013.

      You can download the free Power Query add-in here.

      Kind regards,

      Mynda

      Reply
  11. MANUEL ANTONIO

    April 9, 2014 at 7:58 am

    Muchas gracias, la información se expresa facil y es claro en el objetivo de mostrar informes.

    Interesante y didactico.
    Atte.
    Manuel Quilodrán

    Reply
    • Philip Treacy

      April 9, 2014 at 9:33 am

      Hola Manuel,

      Me alegro de que haya encontrado esto útil.

      Phil

      Reply
      • Juan Aguero

        June 8, 2014 at 6:55 am

        Congratulations for your Spanish, Phil, perfect! 🙂

        Reply
        • Philip Treacy

          June 8, 2014 at 9:34 am

          Thanks Juan. I did cheat a little though, thank you Google translate 🙂

          Reply
  12. Duncan

    November 1, 2013 at 9:36 am

    Nice! Like you, I carry out Excel training and like you I STILL come across accounting and finance staff who neither know pivot tables nor excel tables. One delegate created a 31 worksheet workbook for his work that contained 30,000 (I kid you not) SUMIF formulas. That file recalculated for 15 – 20 seconds every time he pressed Enter. I deleted his 30 sheets, converted his input sheet to an excel table and then created a pivot table in the form he wanted. Finally I created the 30 sub reports in pivot tables using field pages.

    It took he just five minutes to do what he spent hours doing every month!

    This is just one of so many stories!

    Duncan

    Reply
    • Mynda Treacy

      November 1, 2013 at 9:39 am

      🙂 I hear you Duncan.

      Reply
  13. Jon

    October 31, 2013 at 3:18 am

    Hi Mynda,

    Great article! I think this is a really important concept when working with data, pivot tables, and tabular formulas, and is often overlooked. I wrote a similar article that explains an approach to converting semi-reports to tabular data using formulas. But I agree that the best way is to get the data in the proper format to begin with.

    Thanks again!

    Reply
    • Mynda Treacy

      October 31, 2013 at 8:15 am

      Cheers, Jon 🙂

      Actually there’s another data format I forgot to add: The multi-tab/multi-workbook format. I will have to update my list!

      Here’s the link to Jon’s article on converting semi-reports to tabular data:

      http://www.excelcampus.com/modeling/structure-pivot-table-source-data/

      This Reverse Pivot method is also handy for converting data into a format for use in a PivotTable:

      https://www.myonlinetraininghub.com/excel-factor-entry-1-reverse-pivottable

      Kind regards,

      Mynda.

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Shopping Cart

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now
  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
 
  • About My Online Training Hub
  • Contact
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x