How to present data correctly in bar chart

The workbook is full of #REF! errors because all the data is being drawn from an external sheet. Please copy VALUES to the sample workbook.

Which version of Excel for Mac do you have?

Will look, but please add version details to your forum profile. Is that the 365 version? If so, add that, too.

I'm not sure what your idea of a "correct" presentation would like. To pick the low hanging fruit, I selected A2:O22 -> insert -> column chart, and immediately got a "correct" chart showing the "#" values (y axis) against "time of day" (x axis) for each day of the week and type of number. The chart is "correct," but it seems to me to be unreadable because there is too much information muddled together into one chart.

To get a more meaningful chart, I select A2:C22 and insert a column chart. This gives me a nice, readable chart showing # staff and # covers against time of day for Monday. I could easily see repeating that 7 times to get 7 charts -- a separate chart for each day of the week.

We should be able to help build a "correct" chart, once we understand what you have in mind for this chart. At this point, all we have is a bunch of numbers with no idea what you want the chart to look like.

Administrative Note:

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not told us about this. You are required to do so.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important: https://excelguru.ca/a-message-to-forum-cross-posters/

(Note: this requirement is not optional. As you are new here, I shall do it for you this ONCE: https://www.mrexcel.com/board/thread...ation.1241810/)

I don't know if I can help much with the overall data analysis goal here; I don't have enough information about the analysis. What part does the bar chart play in this analysis?

It might be helpful to summarize the data by "mealtime" as you suggest, I can't be sure, because I don't know much about the analysis.

As far as the spreadsheet programming with an eye towards chart creation goes, one would usually calculate/summarize/manipulate the data in the spreadsheet until you have a nice table showing the desired values, then create the chart from that summary table. At this point in the conversation, if I had to guess, it seems like you are not yet certain how to summarize the data. Your latest post suggests you are looking for some "optimum" relationship between number of staff and number of covers. Perhaps a calculation based on the ratio of staff to covers or a difference between staff and covers would be helpful? Combined with what you believe is the optimum value for that parameter, and highlight in the table places where that ratio/difference is very different from the optimum value?