Olga

Saved 5/2/16 8:35 PM to Microsoft Excel

Creation of the summary table in Excel  See details »

 
Video subtitles:
  • 00:00: Hello my name Nikolay Pavlov I coach and expert of microsoft office products and excel in a particular also author of the site x elle.ru planet in this video tutorial, I would like to demonstrate and tell how use one of most powerful and beautiful Tools microsoft excel PivotTables consider the following example suppose you there's such type table in wherein each row represents a
  • 00:30: bargain sale or mug note note that the line fully describes the the whole process of a goods from a categories when what quarter of what month in what city and who we have shipped as in Table contained Information about who of our managers of sales managers responsible for this deal and naturally for what amount was shipments down table very, very
  • 01:03: because the volume Contains information of all sales of whole year if I click on keyboard kontrol the down arrow well we will see that the table almost fifteen hundred lines in real naturally could be much larger objective is to build on This table annual final report from which it would be which can be clearly seen Products in which city
  • 01:33: for what amount and to whom we sold the there's this here table note note that the lines in our table go Products grouped in category and category possible wrap the columns are city ​​where we traded in intersection of row and final column
  • 02:03: or the amount of revenue all transactions of original table by this product in the town except intermediate totals and the grand total across the table here also have ability to choose customer that is table actually 2a is not a three-dimensional It can be done b1 cell using drop-down the list can be selected as one customer and few if put this one
  • 02:34: check and resolve multi choice Here is an example of this a simple summary let me on the table steps slowly and detail tell how you can create and so it all starts with source table note that table enough simple in design has simple 1 lowercase
  • 03:05: cap without breaks go blank rows and columns and without merged cell it is necessary condition for the successful construction of the pivot table microsoft excel additional bonus will opportunity convert this table in the so called dynamic author contracting sowed smart table for In order to then not think about its size and monitor quantity adjustment in rows and columns in this table the future it is possible to
  • 03:35: easy to do if you set active a cell within the list and on the Home tab choose formatted as table option you design choose the role is not plays, you can choose and after pressing any on ok Our table will be dynamic or smart that is at writing down less new lines and columns they are automatically will be included in database and get out of the final please report
  • 04:06: note that designer tab which appeared after conversion tables in clever you there is a table name Table 1 it is will be used for the report PivotTable of course they can be vary as desired can be used without any name gaps and beginning with the letter let's call it let trite true Now
  • 04:37: go to the tab insert and create a summary table pay attention to what in English versions it sounds like pivot taybl click on button summary table appears create a summary box table draw attention to the fact that excel he caught on our named dynamic range if sales though it is not the way
  • 05:08: It could happen to dial it to him here and manually asked where unload the final report really better to do it on new separate sheet click ok and get a new one blank sheet on which will be here Such is the design in the left half will be built with our you pivot table and right now we We see the list of fields PivotTables I hope you will learn
  • 05:38: All headlines from hats here for this We need a simple way and a cap area 4 below that represent four main or four element components of which, in principle, may consist summary table lines in this area I in the original table that you He demonstrated there It was the name search area columns if there remember were the cities the range of values here just calculated costs or profits
  • 06:08: and filter report allowed to choose all of our customer further work It is simple mouse dragging field from the list in the four field summary table to be this is based fully automatically We drove name throw to lines note that It occurs on leaves city thrown into battle column sum throw
  • 06:39: in the range of values the range of values the way it only area where possible and mathematical calculation so usually here just throw some monetary field such as the amount of revenue weight gains the quantity of goods and etc. but also a filter reports can throw the customer for completeness pay attention to what we turned on the lines are all
  • 07:09: names then I no repetitions of sorted alphabetically the columns will leave all city ​​where we traded in intersection is total revenue this city this product plus to all I further filter and goods and the city and customers using simple filters to quickly lead the numeric part in
  • 07:39: in order in particular remove the penny and lead of customary and pleasant sight can use a simple function clicked right mouse button in any number and choose not This format cells the more familiar path but it requires allocation certain range will tied to the range and it is better to choose numerical command field in such a format we immediately one movement another format the entire field summary tables for field the sum of all the numbers in
  • 08:09: PivotTable immediately take a pleasant view select number format will continue to usual window select number penny format and we want to share in bits group It will allow us to share number three for visibility at large amounts of it very good press on ok as you can see all summary table immediately I catch on decent appearance and for completeness pictures on the can
  • 08:39: add some additional parameters in particular goods grouped by category fruits and vegetables yeah hitherto so on for clarity display these groups PivotTable and you can throw category to the same place where already is name in the region rows wherein note It plays the role of higher or lower than it would be throw for example if having thrown category below name
  • 09:09: the appearance will be is far from ideal but if you change them places because category more global concept on the should go higher than 1 name of the the appearance of the table immediately change it It becomes an order of magnitude better same can be do out of columns say city, I am free migrate into the filter report and abandoned columns quarter and for the quarter month get
  • 09:40: the details of months with intermediate the outcome of the quarter for each product and each category nice report clear and vivid how such a thing done manually frankly scary think for completeness paintings and Laying Out species can be use the tab designer which It appears when you create PivotTable
  • 10:10: It has a large of ready formats for summary tables can be e.g. use any nice and formatting and visual display numbers can be used Histogram for this I will highlight a few cells with numbers and Primary tabs using the button conditional Formatting de 100 gram and this team It allows to apply dedicated children com fill whose width
  • 10:40: It depends on the number than greater number of wider Fill a kind mini chart or micro graphics directly within cells to apply like formatting is not only to selected and immediately to the cells the entire PivotTable can use here this icon parameters formatting it It allows you to apply The new rule does not only to selected to all cells source table except for the results of it on the procedure adds clarity and immediately clear where we
  • 11:10: the number of large and where small it certainly is not all that can pivot tables but for now I think It will help enough when you know all just