Creating a one-dimensional summary in Excel

Creating a one-dimensional summary

Now that we have created our Pivot Table, let's start by creating a basic summary of total sales by customer. Firstly, add the Customer field to the summary. You can do this job, by either clicking the checkbox next to the Customer field in the PivotTable Field List, or by dragging the Customer field into the Row Labels section.
Once we have done this, notice a few things: first, the Customer field has appeared in the Row Labels section of our sidebar. Second, a list of customer names has appeared within our Pivot Table. A list of customer names is very nice to have, but not particularly useful for data analysis purposes. Let us make things more useful by dragging the Total Price field into the Values section of our Pivot Table:
Now we are getting somewhere! Our table has summed up the values in the Total Price column, segmented by the customer type. Notice that Excel has also automatically generated a Grand Total row that shows the total sales in our dataset: USD 730,000. Let us take our table one step further by dragging the Quantity field into the Values section as well:
Like magic, we now have a summary of total price paid and total quantity of products ordered by the customer. We do not need to write any formulas or copy and paste any values — our Pivot Table has done all of the work for us!

Creating multi-dimensional summaries

The value of Pivot Tables does not stop at one-dimensional summaries. We can also create multi-dimensional summaries that cut our data based on the two values rather than one. Here's how: With our data set above, let us first remove the Quantity field from the summary by dragging it from the Values section back into the field list. Then, we will take the Item field from the field list and drag it into the Column Labels section like so:
Take note of the fact that Excel responds by creating a two-dimensional summary table. Now, customer names are displayed on the left-hand side of the screen, and item types are listed along the top. At the intersection of each customer name and item type, we can see the total amount of the given product ordered by the customer in our whole data set. Excel automatically creates Grand Total rows to sum up totals for both rows and the columns. And the grand total for the entire table aligns with the number we saw while working with a one-dimensional summary: USD 730,000. Our multi-dimensional summaries do not stop with just two columns. Try dragging some of our other columns into Row Labels or Column Labels sections. Or, drag the Quantity field back into the Values section. Our Pivot Table is infinitely customizable depending on how one likes to summarize values!

We at BeXpert Advanced Excel offer state of the art Advanced Excel Coaching at Home. Even corporate professionals comes to us, for Advanced Excel Training in Gurgaon for the same state of the art database management education.

Websete : www.bexpertadvexcel.com For Any Inquiry call us : +91-8802329198

Comments

Post a Comment

Popular posts from this blog

What is Pivot Table Example?

Image
So, how does using a pivot table to perform large scale enterprise business calculations look like? Check out the below example, in which we have used the Excel tool named Pivot Table to complete the task mentioned in the earlier blog i.e. Summing sales in dollars by customer. We were able to prepare the report in a matter of few seconds based on the input data tab. Take Note of a couple of things: it has captured the data we wanted (the sum of the Total Price column, segmented by customer); it has included a comprehensive list of customers on our input sheet; and it is ready for flexible adjustments and modification (see the Pivot Table Field List dialogue on the right-hand side of the screen). We have solved our analyst's problem. Potential uses of Pivot Tables At higher levels, Pivot Tables are more effectively used to summarize the data. Beyond that, they are infinitely flexible, and applicable to a huge variety of situations. Here are some of the more example scenarios
Read more

What are the various Formatting options available in Excel?

Image
We can have a look at the various formatting options available in Excel by selecting a cell that contains a number and from thereon heading towards the Number formatting drop-down on the Home ribbon tab: Following are the most used formatting options that are available in Excel: Number. Excel would format your input as a generic number with commas in-between the thousands digits. Currency. Excel would format your input with a currency sign in front of it and two trailing digits after the decimal point. Accounting. Similar as the Currency format, but currency sign will align to the left-hand side of the cell. Date. Excel would format the given input as a date. Note that dates in the Excel are displayed as numerical quantities. Time. Excel would format the given input as a date or time. Times are also displayed as numerical quantities in the Excel. Percentage. Excel would format the given input as a Percentage. Like, an input of 0.76 will be read as 76%. It is important to not
Read more

How to do Sorting or Alphabetizing in a custom order?

Image
The traditional sorting methods that we've been using so far present a small problem: what happens if we want to order our list chronologically month wise? Our month names have been entered as text strings rather than dates, so alphabetization will not work: if we sort from A to Z, February will come first, then January, followed by March. And if we sort the other way, March will come first, then January, followed by February. How can we get Excel to sort chronologically month wise without alphabetizing? The answer is simple: with the use of a Custom list. Try selecting your data table and opening the Sort dialogue once again: In the Column section, we will select Month, and in the Order section, we will select Custom List. When we do that, a new dialogue opens: This screen allows us to specify our own custom order of columns by typing them into the List entries section like so: Once you have done this, hit OK to confirm our custom sort and then OK again to perform the s
Read more