Sign Alaska Banking Lease Template Myself

Industry sign banking alaska lease template myself

hello and welcome today we're working on the two topics of pivot tables and pivot charts in excel now you might think well i don't really know what a pivot table is or a pivot chart and why do i need to know it well what happens is a pivot table let's look at some data for a second so let's say this is our data set and what a pivot table does is summarize by some label like this could be by product or by salesperson or by state or industry or whatever so this one is by industry so it summarizes put all the healthcare numbers together and then you can do a chart based on that and that's a special kind of chart called a pivot chart so let's get started my name is jeff from finally learn where i help you finally learn financial skills like excel so let's go back and look at this data set this data set has about a thousand rows so it's about a thousand different so it's at row one thousand and one so this is a data set that has different transactions so let's see look at our headers we have date we have salesperson the customer contact the customer the corporation that's our customer the segment that they're in the department that we worked with the market uh this is the stock market that they're on nyse new york stock exchange or the nasdaq this is the sales price this is the state that they're located in the time zone and then the industry so one thing you can do is to set up for a pivot chart and a pivot table then you need to have clear headings and they need to be in columns and you don't need an extra row so you you don't need a blank row or anything like this so this is perfect one thing you can do to look at the data you can go to the data ribbon and then filter and you can filter by you can do by dates so you can do a sending date so then it sorts everything by dates so you can see we have the sales person the customer contact the customer the name of the company the segment the department that we're working with the stock market the sales price the state the time zone in the industry so let's do four different pivot tables and four different pivot charts so let's get started with the very first one let's summarize by industry now if we'll click somewhere in this entire table then we're going to insert and then insert ribbon and then pivot table and it says the table range is a1 through k1001 that's what we want and we're going to put in a new worksheet the pivot table so immediately pivot table it goes to another contextual ribbon it's called pivot table analyze and so what we have is we're going to work with rows and values and columns and filters so we want sales by industry so we're going to grab our total sales and slide it down to our values we're going to do this and it does sum of sales and we can do several things some the count of how many sales we make or the average the max the min so on but we want the sum that's the total of our sales and we want it by industry so we're going to take the industry find that and drop it in rows so this makes let me make it a little bit bigger here so this makes it where we have in rows all the different this is by industry now if we click on this we have our pivot table fields we could put this in columns conceptually it's the same thing but sometimes generally we put things in rows more than we put things in columns for example so let's go back and put it in rows that's an easy table to read so let's clean it up just a little bit let's do if we click on somewhere in this column we right click and we can do number format we were going to use the accounting format so it puts in dollar signs and to the pennies do we really want the pennies so we can let's go back and change that to accounting and no pennies the pennies are not going to be helpful for us so what we have is we have the by industry here's the totals and we'll we'll sort it here in just a minute but let's go ahead and do a pivot chart so we get over to the insert ribbon and all the way over here it says pivot chart so that's a special type of chart called a pivot chart and it kind of gives us one kind of automatically so let's just make it a little bit bigger so we can see it easily now we want to clean this up a little bit we know this is total we don't need this little legend here on the right we probably don't need these horizontal lines we want to make this a little bit thicker so if we double click and click out of it it's selecting all so it now says format our data series and let's do a gap width let's do a gap width down to say 50. let's do a gap with and make it a wider column there we can edit just a little bit let's make these a little bit easier to read so go to the home let's make it a 12 point font and let's make it bold we can make it bold that way we can make this a 12 point font and make it bold and then the chart title will do sales by industry so the general industry is consulting financial so on and we need to make this a little bit larger so let's make it an 18 point font and let's make it bold so that's easy to read now the one thing we want to do this pivot chart is tied into this pivot table i'd love to make it where health care is number one and then consulting and so on so if we do this little drop down arrow it says we want to sort by industry and it basically did this alphabetically it's sorting by industry and it's doing alphabetical we don't want to sort by the sum of sales and let's do it ascending would be from smallest to largest if we did descending it would be from largest to smallest so here we have sales by industry we have healthcare number one we can see that on the pivot table and then the pivot chart all right so that is our first pivot table and pivot chart so i'm going to slide it over here and i'm going to change the label of this tab to sales by industry now what i'm going to recommend that you do is let's do the second one let's do sales by state so i'm going to right click move or copy move to the end create a copy and it's going to be an identical tab and it relates to that first data set the only data set we have here and let's call this sales by state so i'm going to change this to sales by state so if i go in to my pivot table i need to select pivot table fields i'm going to take out industry and i'm going to put in state so i'm going to drag state into the rows now what's the problem all these states have completely different numbers we have and then the the chart updates but then we don't see alaska we don't see all the state names so let's simplify let's do a top 10 the top 10 states so we go here and we can sum of sales we'll sort by sum of sales we're going to choose a value we'll do a top 10 and it says by the way how many do you want and we can select 10. let's do the top 10 states and so we're finished here and it has still alphabetical right so let's go back and sort this by sum of sales and count it descending so we'll put the largest state by revenues first california texas florida you can see how it works and these top 10 states represent 4.4 million out of our total 7.9 million so that's a big large percentage of our total sales are in the top 10 states so this is sales by state we need to edit this a little bit and we're happy with this we can stick with this we can change the color if we want to we'll do that on the second one the third one that we do after this one so we've done the sales by industry we've done sales by state and you see everything gets updated all right the third one we want to do let's do sales by sales person so i'm going to take this right click move or copy move to the end create a copy and it's identical sales by state so we're going to change this to sales by sales person so sales by sales person so if we click on the pivot table i'm going to take out the state and put in the sales person so let me do salesperson first and get rid of the state and i can just drag that take it and drag it out where it's outside that box and there we have it so now we have these are all the sales people and so here is this is a column chart let's change it around a little bit so this is going to be sales by salesperson so sales by sales person we want to sort so the sum of sales and we can do descending okay that works but let's change it we have sum of sales let's do the average sales by sales person so here where it has sum of sales i'm going to go down to the values and i'm going to click on that little information and i can do average so now we can see instead of total sales we can do what is the sales average and you see that is a different number now we have the average of sales it's still sorted from top to bottom we have all these the same color we might want to go back and look at different colors so let's format the colors here and the fill we can vary colors by point and you see we have different colors now that may be helpful and then we can inside the table i'm sorry inside the chart we can change the chart type so let's change it to a bar chart column is horizontal sorry vertical and the bar is horizontal so here we want this we have helen hayes is the largest average sales by sales person so let's put average sales here now how do we make it where it is we maybe want the top person at the very top and the smallest at the bottom well we can go back and change this so it's descending let's do a sending and that changes it to we can see the average sales by sales person the largest to the smallest and this is the entire group of people and so the grand total is this is the average the average sale is 79 22 and the hat we have helen hayes is 86 83. all right let's do one more and this is sales by customer so i'm going to right click on this move or copy i'm going to move to the end create a copy so now we have sales by salesperson i'm going to edit this to be sales by customer we'd like to know let's say the the the top 10 customers sales by customer we're going to go back and look at this one we want not only the average of sales now if you need that back you want the pivot table fields back if you'll click here and go to pivot table analyze and it has the field list it brings back the field list we have filters columns rows and values so let's do the sum of sales this is the sum of sales and we want to do this by customer so i'm going to add the customer drag it down here and then get rid of the sales person salesperson we got rid of that now there's so many customers this is not going to work we need to have just like the top 10. so here on the row labels on the customers the sum of sales we're going to choose the top 10 and i want it to be 10 10 would be great we can summarize this by 10. so what we have is here's our sum of sales here is our top 10 and it it goes back to the labels and alphabetizes the labels so we want to do the sum of sales the top 10. let's do it in ascending order so then we have the largest is at the bottom of the table because it'll make it at the top of the chart so here is our total sales by customer so our sales by customer and here's what we have we have a way to format with pivot charts pivot tables first and then pivot charts and you see that anything we do here if we said well we want the top 15 then it immediately ads now 15 is the pivot chart so the pivot chart is based on the pivot table let's go back and change that so we have the top 10 customers here's our top 10 customers and that's how we do pivot tables and then pivot charts on top of the tables all right thanks for watching you