[Solved] Ideas for formatting a table with multiple headers

[ListingAndy]

I do some work where I have take large amounts of the same kind of measurements, in the attached example the measurements are the coating thickness at a bunch of different places. The problem I'm having is that most good tables have one clean header, at the top of each column is a description of the contents of the column below, but for taking large quantities of measurements this stops making sense.

I'm taking measurements on 3 different huge samples, in each sample, I have to test in 3 different locations on the sample, and in for each of those 3 locations I need to take 4 different measurements. Right now I have the table set up so that the very top is describing what the table is for, then the row below that shows the 3 different locations, then the row below that shows spots 1,2,3,4 for each location. It makes sense to me that the 4 spots header should be itself labeled somehow, perhaps to the left in the blank cell above the beam names, but I'm not sure if that would get confusing when headers usually refer to what is directly beneath them. In my opinion this sheet looks pretty bad and there must be a more elegant solution, but I couldn't find anything like this when googling, frankly I'm not even sure what I would look up.

Any ideas on how I could improve the layout of these tables?

[MrProgrammer]

The problem I'm having is that most good tables have one clean header, at the top of each column is a description of the contents of the column below

You have described a simple table. Your attachment is (in Calc terms) a pivot table but constructed manually. To get what you want:
• Enter your data using a simple table. You can use a filter to show only the rows of interest when entering data.
• Display that data using a Pivot Table.

Since you have the data in a simple table you can easily analyze it. For example you can get the maximum and minimum of the four measurements at each location.

202211011201.ods

Data rearranged into simple table with pivot table examples

(16.45 KiB) Downloaded 125 times

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know

[ListingAndy]

Enter your data using a simple table. You can use a filter to show only the rows of interest when entering data.
Display that data using a Pivot Table.

Since you have the data in a simple table you can easily analyze it. For example you can get the maximum and minimum of the four measurements at each location.[/url]

I appreciate the effort, I don't really need to do any analysis though. The purpose of my tables is simply to have values written into them, add the tables to a word document, and then store them in a PDF/Print them. I'm mainly just wanting to structure and design the table in a way that is both aesthetically pleasing without being confusing, I have never made or used multi-row headers before and I don't know what the standard practices are for using them.

[floris v]

There is nothing wrong with your design with headings spanning three rows. It's the sensible thing to do, and it has been done a lot in the past. But IMHO (I'm Dutch, always looking to comment on something well done ), row #3 (Loc. #1 Loc. #2 etc. is rather high; it should be as high as the headings above and below. There's no accounting for tastes though, and I mean that positively: it's up to you and the people for whom your output, in whatever format, is intended, to decide when it looks aesthetically pleasing without being confusing.

[keme]

I appreciate the effort, I don't really need to do any analysis though. The purpose of my tables is simply to have values written into them, add the tables to a word document, and then store them in a PDF/Print them.

What is the purpose of having those values in the table?

Please don't think I mean this in any condescending way. I am writing in earnest, because with your query and response, I don't understand what you find lacking in your own setup (cf. also the comments from floris v).

Some angles on "purpose", to help us focusing the effort, if you really feel that you need additional help with this:

• Target audience:
  Is it for future reference for yourself, documentation for a customer, or a message to future generations?
• Usefulness:
  E.g. enabling you to reproduce a consistent result, predicting durability or planning maintenance?
• Pure aesthetics:
  The numbers in the table are for sheer pleasure, without any bearing on future research, learning, actions or choices.
  I cannot understand that this can be the ultimate goal for such a table of figures. Hpwever, from my reading of your descriptions and comments, there seems to be no use for the measurements except "to be aesthetically pleasing without being confusing". If this is indeed so, please confirm!

[ListingAndy]

There is nothing wrong with your design with headings spanning three rows. It's the sensible thing to do, and it has been done a lot in the past. But IMHO (I'm Dutch, always looking to comment on something well done ), row #3 (Loc. #1 Loc. #2 etc. is rather high; it should be as high as the headings above and below. There's no accounting for tastes though, and I mean that positively: it's up to you and the people for whom your output, in whatever format, is intended, to decide when it looks aesthetically pleasing without being confusing.

What do you mean by "high"? If anything it looks much thinner than the surrounding rows.

[floris v]

Lol, you're right. I meant the next row, but got confused after making that thinner.

[ListingAndy]

I appreciate the effort, I don't really need to do any analysis though. The purpose of my tables is simply to have values written into them, add the tables to a word document, and then store them in a PDF/Print them.

What is the purpose of having those values in the table?

These are tables for engineering reports. In this case, the two important things that need to be readily understandable are: How much does the coating thickness vary? what is the average coating thickness?

Now that I put it that way, I suppose it would indeed make sense to include a mathematical average for each row of coating thicknesses, I'm mostly just taking data from an engineer and trying to format it to look nice, and the engineer did not include any actual averages, I guess normally we would just have this big table of data, then in a separate section say something to the effect of "The average coating thickness of the flange on beam 7 was 3.45 mils" so including averages as part of the table is probably unnecessary.

These reports are used both internally by our engineers to form conclusions, and externally by our clients who are not engineers to get an idea of what's going on, those clients likely would not actually be looking for specific information in the tables but may skim over them to get a general idea of what the measurements looked like.

So for purpose I guess I could say:

1. It needs to look aesthetically pleasing as part of an expensive report we give to non-engineer clients.
2. It needs to be readily understandable for engineers who may need to use the data.

I should add that our main engineer is a dinosaur who strongly dislikes technology, so he would rather just look at a printout of the spreadsheet and then manually add the numbers up himself instead of having a powerful spreadsheet to do it for him (I cannot personally relate to this sentiment at all.) I do agree that it is pretty decent looking and understandable as is though, he asked me to revise them to look nicer, which I should be able to do simply by using pre-made table formatting.

[ListingAndy]

More aesthetic questions: Keep all the data cells nice and square, but the table will not take up the full width of the MS word page? Make the data cells rectangular just enough to fill the width of the word page?

For the top 3 heading rows, should I make them all the same distinct heading color? Multiple colors?

This is not important at all, just curious about what you guys think would look nice.

[MrProgrammer]

I don't really need to do any analysis though.

Really? Then why do you now say?:

How much does the coating thickness vary?

My pivot table with minimum and maximum gave that exact information. It could have shown standard deviation too.

What is the average coating thickness?

A pivot table can easily show the average.

Even if you didn't need to do numeric calculations, a pivot table make it quick to display the data in different ways with categories and sub-categories in horizontal or vertical directions. But a pivot table needs a simple table as its data source.

My Max/Min pivot in my first post showed the maximum and minimum with the three locations underneath.

DataLocation.gif (45.52 KiB) Viewed 2593 times

But in seconds I can change that to show the three locations with the maximum and minimum underneath.

LocationData.gif (53.85 KiB) Viewed 2593 times

[floris v]

Make the A column wider, so that each description fits on a single line. Decrease the row height. You can play with the borders - if you have sufficient room in the cells, you might do without most of the borders without causing confusion. The important thing is that the numbers in the columns that belong together are just far away from each other that you can tell them apart, but not so far that they don't seem to hang together anymore.
What you really want, though, is a graphic designer, and one who accepts that sometimes readability and intelligibility take precedence over a designer's idea of how things should be formatted. (S)he can show you a trick or two to make that tale look wonderful that we would never think of. I expect that you have a budget for that production.

[ListingAndy]

Even if you didn't need to do numeric calculations, a pivot table make it quick to display the data in different ways with categories and sub-categories in horizontal or vertical directions. But a pivot table needs a simple table as its data source.

Sorry for leaving that out, it's a weird way things are done at my job. In my personal life I use lots of elaborate tables and graphs, even my spreadsheet for keeping track of books I've read has 4 charts with lots of averages and statistics. After doing this job for 2 months I'm only just now starting to understand what the purpose of these tables is myself, when I first got here it was just endless numbers of uncertain importance and priority

I guess the way my boss does things could be neatly summarized as "I make a testing procedure, I do the tests, I write down the data, I do the math manually, and I give the client conclusions based on this data and my engineering knowledge". It's easy for me to miscommunicate this because I'm new here myself and everything is done in an oldschool way here. For the most part this data is just in the report as a supporting argument more than anything, he tracks his data mostly manually or with printouts of spreadsheets like the one I posted.

In the case of this specific report the standard deviations or exact averages for each beam etc. are not necessary because the coating thickness is supposed to be 10mils at least on all surfaces, and it is clearly far below that in every single cell on the table, so as long as the table looks nice and is readable without being confusing about which cell represents what, it's doing its job.

[ListingAndy]

Make the A column wider, so that each description fits on a single line. Decrease the row height. You can play with the borders - if you have sufficient room in the cells, you might do without most of the borders without causing confusion. The important thing is that the numbers in the columns that belong together are just far away from each other that you can tell them apart, but not so far that they don't seem to hang together anymore.
What you really want, though, is a graphic designer, and one who accepts that sometimes readability and intelligibility take precedence over a designer's idea of how things should be formatted. (S)he can show you a trick or two to make that tale look wonderful that we would never think of. I expect that you have a budget for that production.

The data in each row is actually unrelated to anything above or below it, essentially. I suppose it might arguably make more sense to swap my X axis with my Y axis, but the data for each part of the beams is so much longer, so I'm not really sure which way to do it. Strictly technically speaking, all of this data is essentially the same thing, just a ton of coating thickness measurements of beams, but sorted by beam and location for the sake of readability and to see if there was any issues with some specific parts of beams, which there turned out not to be. It's not considered weird to have all the directly related data across a row instead of down a column?

We are a very small company, currently it's just me and the engineer/owner, so hiring a graphic designer is currently off the table, we already have all of our marketing stuff designed. As long as this table looks decent and makes sense It's fine.

I rebuilt the table, and also attached some screenshots of a page of a report with the old table design as well as the new table design (the actual tables are not the same data). These tables were pretty much copy and pasted directly into Word. Tell me what you think.

Looks like my other tables were formatted a with the headings in the wrong order now that I'm paying close attention. Weight % should definitely be above spot #s to make sense. I'm also not really sure how to get a consistent look when I'm using tables of wildly varying width as in the screenshots. Maybe I should mash some of them together so they are about equally wide.

[floris v]

Right, looks almost good enough. The most important thing for readability and aesthetics is that all rows have the same height. If you can't get Beam # Connection on one line, you can better have all rows break into two lines or force the same height for all rows.
How you have your X and Y axes probably depends on habit. For me as a newbie in your field, it doesn't matter a lot.

[ListingAndy]

Right, looks almost good enough. The most important thing for readability and aesthetics is that all rows have the same height. If you can't get Beam # Connection on one line, you can better have all rows break into two lines or force the same height for all rows.
How you have your X and Y axes probably depends on habit. For me as a newbie in your field, it doesn't matter a lot.

Here's the same page with the table stretched slightly. It just compressed to fit the margins when I pasted it into the document initially.

Everything else looks sound? White borders on an all-the-same solid color triple row header is nice? Size 10 font probably good enough? Reasonable amount of space for everything? The information is presented in a clear way enough way where you could quickly find the second measurement for Beam 6 Web Loc. #2 Coating thickness?

I would say this looks pretty satisfying to me, just curious what someone who is really into spreadsheeting would have to say about it. Seems like MS word likes to ruin the formatting a little bit. Maybe I should set up my spreadsheet tables to the exact same margins as MS Word so this doesn't happen as much.

Also any ideas on what to do about switching between tables with 2 columns vs tables with 6 columns? I could definitely get away with just leaving them all like that, centered on the page, I want things to look nice and consistent but at the end of the day it is just data on an engineering report so I'm not going to die if they look a bit funny.

[floris v]

Keep in mind that you are displaying this as a screenshot in a web browser, which is not the same as the original. Also keep in mind that spreadsheets are tools for making calculations, not for formatting the output in an aesthetic way. So, MS Word has better lay-out tools than Excel.
The font size is tricky. When you view the file on screen, you can zoom in if the font is too small for your comfort. You can't do that with a printed book. So, print a few pages and study them long enough no make sure that the font isn't too small. 10 pt is probably good enough though.

[ListingAndy]

Keep in mind that you are displaying this as a screenshot in a web browser, which is not the same as the original. Also keep in mind that spreadsheets are tools for making calculations, not for formatting the output in an aesthetic way. So, MS Word has better lay-out tools than Excel.
The font size is tricky. When you view the file on screen, you can zoom in if the font is too small for your comfort. You can't do that with a printed book. So, print a few pages and study them long enough no make sure that the font isn't too small. 10 pt is probably good enough though.

I'm skeptical that MS Word actually has better options. Certainly the quick tables seem to fit the document more naturally than tables copy and pasted in from excel or calc, but there is only a few options for stock tables, this is what they look by default and after a few minutes trying to get decent formatting. It's not too bad I guess. When I started this job the previous admin assistant said to always use spreadsheet tables, but she also didn't really seem to have a strong grasp of this stuff.

edit: turns out I missed a huge section of built in MS-Word tables. I guess these will be actually perfect for what I need.

[ListingAndy]

I manage to condense those 5 inconsistently sized tables into this one table, I'd say this is a massive improvement.