Tip #6: What are aggregated charts? How do they improve visualizations?

Charts, as we know, are vital data visualization tools. Pivot tables, on the other hand, are useful for giving you a summary of your entire data set. Aggregate charts, in turn, allow you to create visualizations of your pivot tables, giving you the best of both worlds!

What's the difference between an aggregated chart and a normal one?

Consider sales data from across three Regions, by three agents, selling three Items. Here's a regular chart on units sold by item.

Here's the same chart, after an aggregation.

Though both charts were based on the same range, the second chart has been aggregated based on the total number of units sold for each item. The chart is more insightful, and represents data better than the first chart.

How do I build an aggregated chart?

Zoho Sheet provides an option to aggregate charts based on different series and different formulas in the contextual side pane. To create an aggregated chart, you must first answer these two questions:

Which series would you like to use to aggregate the chart?
What function would you like to use to aggregate the series?

For instance, in the sample chart above, the series of Units sold was aggregated using the SUM function. It would also help to place a series in the Axis, and the Legend part.

It's up to you if you want the other series in the data to be represented as either the Axis or the Legend of the chart. In our earlier example, the Item series, was used as the Axis of the chart, so each item was taken as entries in a series, and the corresponding SUM of Units sold has become the value of the entry.

What exactly is the difference between making a particular data series the Axis of the chart, or the Legend of a chart? Making one series the axis of the chart will mean that you want to represent the different entries in the series (Pen Sets, Binders, and so on) as the different values of a single, collective series.

Making a series the Legend will mean that you represent every unique entry in the data set as a different series. (Pen Set as a separate series, and Binder as a separate series in this case). No series can be named as both the Legend and the Axis of a chart.

What do I do if I have more than two series of data?

Here's an extension of the sample data. Now, in addition to the Item and Units sold series, we also have Sales and the Region. We've decided to add the Region as the Legend, and make the Item series be the Axis, aggregating the data based on Sum of Units sold. The result? A chart that provides almost all of the insights you would need from the table alone!

Here's a variation of the same chart, but aggregating the data based on Sum of Sales.

Play around with aggregated charts to get more insights out of your data! Follow our community, and our Twitter handle for more such spreadsheet tips and tricks.

Access your files securely from anywhere

Zoho Developer Community

New to Zoho LandingPage?

Access Zoho LandingPage

Zoho LandingPage Resources

New to Zoho Survey?

Access Zoho Survey

Latest Feature Updates

Explore our Pricing & Plans

Zoho Survey Resources

Android Mobile Surveys

Insurvey Blogs

Insight Blogs

Tips & Tricks

New to Zoho CRM Plus?

Deliver unforgettable customer experiences

ACCESS ZOHO CRM PLUS

New to Zoho CRM Plus?

Deliver unforgettable customer experiences

ACCESS ZOHO CRM PLUS

New to Zoho Marketing Plus?

Everything you need to run your marketing

ACCESS ZOHO MARKETING PLUS

New to Zoho Marketing Plus?

Everything you need to run your marketing

ACCESS ZOHO MARKETING PLUS

New to Bigin?

Signup Now

Access Bigin

Latest Feature Updates

Zoho Desk Resources

Desk Community Learning Series
Digest
Functions
Meetups
Kbase
Resources
Glossary
Desk Marketplace
MVP Corner
Word of the Day

Tout forum

France ZUGs

Zoho SalesIQ Resources

Topic Participants
Selva Sundaram M

Sticky Posts
Tip #18: 6 Trendlines and when to use them in your spreadsheet data?
Charts are a great tool for visualizing and interpreting large chunks of data in spreadsheets. Zoho Sheet offers you 35+ chart options, along with AI-powered chart recommendations based on the data set you select. There are various chart elements that
Tip #16: Ask Zia your data-related questions and get powerful insights
Zoho Sheet provides a variety of tools and functionalities to help you analyze your data from end to end. But what if you could analyze all your data sets with a single AI-powered tool? Meet Zia, our smart virtual assistant who will answer all your data-related
Tip #17: Easily share spreadsheets with non-Zoho account users
With efficient, collaboration-friendly options, Zoho Sheet is a popular choice among users. Sheet's external share links help you collaborate more effectively by sharing your spreadsheets with anyone outside your organization, including users who do not
Tip #7: Four things you didn't know you can do with Conditional Formatting
Conditional formatting helps you highlight and visualize data in your spreadsheet based on provided rules. Zoho Sheet offers three types of conditional formatting: Classic, Color Scales, and Icon Sets. If you're already familiar with how Conditional Formatting works, here are some bonus tips for Classic formats: Customize rules with formulas Zoho Sheet allows you to create personalized rules based on formulas. For example, in a task list you can use customized formulas to highlight tasks which are
Tip #15: Stop guessing and start estimating with Zoho Sheet's Goal Seek
Goal Seek is an ideal spreadsheet tool for devising estimates, as it helps you retrieve your desired output value when the input values are not known. In other words, Goal Seek does back-solving to find input values once you've provided an output value.