# Microsoft Office Application Help - Excel Help forum > Excel Charting & Pivots >  >  Alternative way to chart data

## joemontenaro

Can I recreate Chart 1 in the attached image (generated as a stacked area chart from the Format 1 data), from the Format 2 data?

----------


## MrShorty

It maybe depends on exactly what you mean by "create the chart from the format 2 data". I do not see any way to create the chart directly from the format 2 table. I could easily see using formulas based on the format 2 table to create the format 1 table, then the chart is built on the format 1 table. If I don't like seeing the format 1 table, I can hide that table.

Does using formulas to build the format 1 fit within your goal of building a chart from the format 2 data?

----------


## joemontenaro

It does and I could maybe work through generating those formulas on my own, but I was curious to see if there was any way to do it directly from my source data.

If you've got any pointers on the formulas, I'd appreciate hearing them!

Thanks!





> It maybe depends on exactly what you mean by "create the chart from the format 2 data". I do not see any way to create the chart directly from the format 2 table. I could easily see using formulas based on the format 2 table to create the format 1 table, then the chart is built on the format 1 table. If I don't like seeing the format 1 table, I can hide that table.
> 
> Does using formulas to build the format 1 fit within your goal of building a chart from the format 2 data?

----------


## MrShorty

The formulas should be fairly simple IF() functions. The basic logic is If the time in row 2 is between the start and end times in columns L and M, then return the staff value from column K, else return 0. Assuming the upper left of your screenshot is A1, this formula might look like =IF(AND($L3<=B$2,B$2<=$M3),$K3,0). Note the mix of relative and absolute references. Then copy/paste/fill into the rest of the format 1 table.

I note that your screenshot shows the times left aligned in the cells -- which is default behavior for text strings. Numbers/times/dates are usually right aligned by default. If your times are really text and not true date/time serial numbers, then you will need to add a "convert time stored as text to real time" step.

----------

