How do I add data from a report to a chart on a dashboard?

Michael Hair

I am trying to add data from a report to a pie or bar chart on a dashboard. I am trying to sort the data on this chart by the color (added through conditional formatting) of the row based off the result of the form that was filled out. I do not have any numbers on my sheet but would like the chart to show the number of each color that is shown on the sheet.

Ben Goldblatt

Hi @Michael Hair,

If I'm understanding this correctly, you're looking to be able to create a chart on a dashboard based off of data in a report and you would like the reporting data to be sorted by colors applied via Conditional Formatting rules that have been set on the source sheet. There isn't currently a way to sort data based off of formatting but you may be able use similar criteria to apply a sort and generate counts for each color. You want to consider inserting a separate column in the source sheet and using a number for each color, Blue = 1, Red = 2, Green = 3, etc). Once the numbers are populated you could then use a COUNTIF formula to count the #1 items, #2 items, etc that represent each color and once you have those counts in place, the numeric data could then be used in chart widget on a dashboard.

If I sort by the numbers listed in the "Color Number column in ascending order, the sheet would look like this:

The formulas used to reference the "Color Number" column in the sample above would be:

Blue: =COUNTIF([Color Number]:[Color Number], HAS(@cell, 1))

Red: =COUNTIF([Color Number]:[Color Number], HAS(@cell, 2))

Green: =COUNTIF([Color Number]:[Color Number], HAS(@cell, 3))

Yellow: =COUNTIF([Color Number]:[Color Number], HAS(@cell, 4))

I used these on row 13 under each color header to generate the counts. With this numeric data you could either pull the information into a report and create your chart(s) from there or use the sheet itself as your source data. Note that for your chart, you'll likely need to manually apply the colors as the formatting on the sheet won't necessarily correspond the default colors applied in the chart widget.

Click here for more information on working with Charts in dashboards.

I hope this helps!

Thanks,

Ben

Ben Goldblatt

Hi @Michael Hair,

If I'm understanding this correctly, you're looking to be able to create a chart on a dashboard based off of data in a report and you would like the reporting data to be sorted by colors applied via Conditional Formatting rules that have been set on the source sheet. There isn't currently a way to sort data based off of formatting but you may be able use similar criteria to apply a sort and generate counts for each color. You want to consider inserting a separate column in the source sheet and using a number for each color, Blue = 1, Red = 2, Green = 3, etc). Once the numbers are populated you could then use a COUNTIF formula to count the #1 items, #2 items, etc that represent each color and once you have those counts in place, the numeric data could then be used in chart widget on a dashboard.

If I sort by the numbers listed in the "Color Number column in ascending order, the sheet would look like this:

The formulas used to reference the "Color Number" column in the sample above would be:

Blue: =COUNTIF([Color Number]:[Color Number], HAS(@cell, 1))

Red: =COUNTIF([Color Number]:[Color Number], HAS(@cell, 2))

Green: =COUNTIF([Color Number]:[Color Number], HAS(@cell, 3))

Yellow: =COUNTIF([Color Number]:[Color Number], HAS(@cell, 4))

I used these on row 13 under each color header to generate the counts. With this numeric data you could either pull the information into a report and create your chart(s) from there or use the sheet itself as your source data. Note that for your chart, you'll likely need to manually apply the colors as the formatting on the sheet won't necessarily correspond the default colors applied in the chart widget.

Click here for more information on working with Charts in dashboards.

I hope this helps!

Thanks,

Ben

Michael Hair

Thanks for the help Ben! I was able to complete what I needed to using your information.