Excel Data Analysis Full Course Tutorial (7 Hours)

[Music] welcome to learn it training the exercise files for today's course are located in the video description below don't forget to like And subscribe welcome to Microsoft Excel introduction to data analysis you're learning with Mo Jones IT professional and educator and this compact course we'll be learning all the essentials for data analysis here's what we'll explore Today we'll convert lists into a table and take a look at the table tools that are available we'll use excel's aggregate functions to conditionally apply calculations to our data we'll use pivot tables to summarize our data set and then

we'll take it a step further we'll create pivot charts to create visuals from our data set go ahead and open up the practice file for this course and we are going to Dive Right In looking to support our Channel and get a great deal become a member today to unlock AdFree videos that's right your favorite courses without a single ad interested in a specific video purchase one of our adree courses individually looking for even more gain access to exams certificates and exclusive content at learnit anytime.com more information can be found in the video description below

on our creating tables worksheet Tab we have some basic employee data so notice that we have well- defined headers we have employee ID last name first name Department email phone extension location hire date and pay rate so that's a good first step we want to make sure that we have well defined headers right so remember our headers they are the key when we are analyzing our data because the headers represents the name of the field in Which we want to analyze or in this case the column that we want to analyze so we want to

make sure that they are named well that they're meaningful and they accurately describe the data below the next thing that we want we want to make sure that we have unique labels now this is not always the case but it's best to have unique labels when you're working with data so in this case our unique labels we can see that we have unique employee IDE Numbers and this will give us some better opportunities to kind of just filter our data down to what we're looking for as well the other thing that we want to make

sure that we have complete records so as long as our list has well-defined headers unique labels and complete records meaning no blanks then we are good to go we can see that this data set we don't have any blank cells in here we have complete records on row Five we have a complete record for Joe Gonzalez all of the information is filled out if we do have blank cells in our data set we'll run into some problems one of the problems that we'll run into is that our data analysis will be incorrect or incomplete because

we're actually missing data so it's usually a good idea just to kind of scroll through your data set make sure that you don't have any blanks and there is well you can always use the count blank function And this will kind of help you to count the amount of blanks that you have in your data so I'll just enter the count blank function here and I'm going to go ahead and highlight all of my data so Excel is going to count all the blank cells that it finds in the range A3 down to I40 if

I press enter I have zero blank cells and I'm good to go now if I were to delete a cell tells me right away that have one blank cell right so those are some good first steps some Other tricky things to kind of look for especially when you're working with dates and and numerical values you want to make sure that they are formatted properly so we can see if we take a look at our higher dates here we can see that they are properly formatted as a date this is not always the case sometimes you

have data that's extracted from one program to the next you may notice sometimes even though these look like dates they say something else up here it May say something like custom or general so you want to make sure that you make those adjustments here we're all good here for the pay rates as well we can see that that's using the currency format and so we are pretty much ready to go ahead and analyze this data in a table let's convert this list into a table and then we'll have some tools that we can use to

analyze the data now there's two Ways that we can convert this list into a table here's the first way um we can actually use a shortcut so if we press control plus T that will convert this to a table for us as long as we're selected inside of the list the other way is from our ribbon so from the home ribbon if we head on over to the Styles command group we have the format as table command so those are two ways that we can convert this so I'll go ahead and Click anywhere inside of

my list here and I'll use the first method which is control T So if I press control T it just basically asks me you want to create a table where is the data for your table and does your table have headers my table does have headers so I'm good to go and if I scroll down I can see that it's is highlighting all of my data I'll go ahead and press okay and it converts it to a table for me so here's the table form we know that this Is a table because if we take

a look at the ribbon we now have the table design Tab and this is where we'll find all the tools that we need to be able to analyze the data we can filter our data we can turn on the total row we can insert a slicer we can also go ahead and take a look at the different table styles that are avail available as well going to go ahead and undo that so I'll press contrl Z and let's try method number two I'll go ahead and click on Format as table from the ribbon here and

the only difference is we can choose our table style ahead of time so there you go so here is my list that's now converted to a table well now that we've converted this to a table we can do things like sort by columns we can also do some filtering as well we also have another tool it's called the total row so we'll take a look at these three tools here as well notice for all of my headers now I have A filter so if I click on the last name filter here several things I can

do at the top of the filter I can go ahead and sort and then down here I can use a logical filter I can filter by a search term or I can go ahead and select a unique value from down here so there's actually one two so four different opportunities for us to use this filter dropdown to analyze our data well I want to go ahead and sort so I'll click on The I'll sort by last name here and I will sort A to Z and notice how all of my records now are being sorted

by last name the entire record moves we're not just sorting the column explicitly the entire record is moving as well so if I were to go ahead and maybe sort by employee ID number we can see that the entire record travels here as well so we can do some simple sorting in here as well but what if I want to do some more complex Sorting we would need to click on the data Tab and click on the sort icon here in order to do some complex sorting because at this point we can only sort on

one column at a time you saw that when I sorted by employee ID it removed the sort from my last name here well what I would like to do is maybe go ahead and sort by let's see I'll go ahead and sort by last name and department so how can we sort by last and Department well we need to click on this sort icon here and this will allow me to select the different columns in which I want to to sort by I'll go ahead and click on sort now here we can see that my

data has headers is already checked off now I can select the column that I want to sort by so I want to sort by last name well maybe I'll do Department first department is a higher level so let's sort by a department and then by last Name well for me to do that I need to click on ADD level so I can add another level here so that I can go ahead and sort by last name so I'll click on ADD level and I'll choose last name I'll make sure that they both say A to

Z and I'm good to go once I press okay we can see it's sorted by a department so here's AC and inside of that department is also sorted by last name here's the ad department and that's also sorted by last name so That's some basic sorting that we can perform on our table let's take a look at the filters that we can use I'll go ahead and click on a filter for the higher date let's see what filters are available for the higher date we'll also take a look and see what filters are available for

the pay rate so we're working with dates and numbers now for the date filters we have a lot of date filters I can look for a higher date based on an exact date Before a certain time after a certain time and between a certain time then I can be more specific right l last year last month last quarter so those are different things that I can do so if I click on between what if I want to know how many hires we had between two dates so if I click on between here here's my little

logical operator I want to find out how many hires did we have let's say after 2000 so I'll say January 1st 2000 and but before December 31st 2006 I could also click on the date picker here and choose my date from here as well let's see how many hires did we have between 2000 and 2006 if I press okay here are my five records if you take a look at your status bar tells you that five of 37 records were found we can see that these all fit that description they all fall between those Two

particular dates so that's one way that we can filter using that logical operator going to go ahead and clear the filter from the higher date so we can see the filter is active here I'll go ahead and click on it and I'll click on the clear filter from higher date this will reset my table display my 37 records well let's go back to the higher date and this time let's go ahead and enter a value in the search box what if I just want to find out how many hires Did I have in 2010 so

if I type 20 2010 here notice how it collapses for me for 2010 so it looks like I had a couple of hirers in here if I press okay here are my three hires for 2010 so we can see the filters really nice tool excellent tool for doing Simple data analysis I'll go ahead and clear this filter as well and then we'll run one more so this is the other level so so we use the date filter we use the search box now I can Just choose an individual month day or year down here as

well so what I'll do is I'll unselect all and I just want to find out how many highers we had in 2006 and 2007 if I press okay here are my four records so either or in 2006 or 2007 I'll go ahead and clear the filter from there let's take a look at the pay rate so the pay rate offers us the same Opportunities not as many filters as the date filter we have some basic ones in here so if I wanted to take a look at the pay rates that are greater than or equal

to a certain amount let's say we can see we go from 20 to 3676 let's show all of the numbers that are greater than or equal to 30 here's my simp simple dialogue I'll type 30 in here I'll go ahead and press okay and there we go so here are my 13 records right so 13 employees are Earning 30 or greater per hour so those are some basic filters the good thing about filters as well we can run multiple filters on our data set so in this case the pay rate is $30 or greater per

hour and what if we want to filter by the Department as well I'll go ahead and click on the department filter here I only want to see from let's say my HR department I'll go ahead and press okay and this is a very nice tool so we're able to narrow it down to the HR Department where the pay rate is $30 or more per hour here are two records it's a really nice way to isolate the data so that you can work on it and once you're done we can just go ahead and move on

to the next department so move on to MF here and there we go a really nice way to filter we can run multiple filters together as long as we understand logically what we're trying to accomplish sometimes we need to kind of just stop and pause and think about What we're trying to accomplish here as well I'll go ahead and clear my filters I do have two filters running so what I can do from the data tab I can click on the clear filter icon here and this will clear all the filters that are running on

my table it's going to clear my department filter and my pay rate filter I'll go ahead and click here my table is reset to display my 37 records now there's another tool that we can use which is called the total row so If we click on the table design here and the table style options the filters already turned on let's go ahead and turn on the total Row the total row is going to place a row at the end of our table and each cell will will provide a dropdown and on that dropdown will be

a list of basic functions that we can use to summarize the different columns on our table I'll go ahead and turn on the total row here we can see the total row has been added on row 41 and it's already has a number here the cell on the bottom right so it says 1,030 well let's go ahead and click on that cell if I click on that dropdown it's giving me the sum the pay rates well that doesn't make sense if I want to find out what is the average pay rate here I'll click on

average and it automatically calculates that column so the average pay rate is $27 31 now one of the nice things is so we Don't really have a lot of columns that we can run formulas and functions in here but if I were to add in you know maybe um some hours in here and the gross pay then then we will see more options that are available to us as a matter of fact I'll go ahead and do that just going to insert a column here so I'll go ahead and put in the hours and then

I'll go ahead and put in um the gross pay so I'll just go ahead and put in Some random numbers here I'll start with 20 I'll start with 15 16 I'll continue this pattern down till I get to say 40 right and let's see then I'll just come back here and continue this pattern down as well why not then I'll simply just add a add a calculation here now this is a major benefit of analyzing data in a Table look what's going to happen once I write a formula here on the on this top cell

as soon as I write the formula it's automatically going to fill down the rest of the formulas for me so that's the benefit of working with the table so for the gross pay I'll say equal pay rate times The Hours worked once I press enter we can see it's automatically calculated all of our gross pce which is a nice feature of a table now we have three other columns That we can run numbers on so what are the total hours worked I'll use the sum function for that 1,155 hours what is the total gross pay

here's the sum of the gross pay 31,8 4351 so this allows us to quickly answer questions on our data set average pay rate is 2731 total hours 1155 resulting in gross pay of 3184 351 the other thing to mention about This is if I filter my data these numbers will update as well based on what I'm filtering by I'm going to go ahead and filter by Department I just want the AC Department if I press okay notice how the numbers have changed the pay rate has changed the hours have changed and the gross pay has

changed based on the AC Department conditional formatting is Another typee of filter so here we just have some houses we have the listing price the town the square footage the bedrooms and the bathrooms as well well sometimes when you're filtering instead of hiding some of the data that does not meet that filter criteria you may want to still be able to see all of the options sometimes we just kind of feel like we're missing out on something so what we can do is use conditional formatting and conditional formatting Can be found on the home Home

tab and here's some of the different conditional formatting options that are available so here's conditional formatting so we have cell rules we have top and bottom rules we have data bars and we have color scales as well the great thing about conditional formatting there's a lot of rules that are built in and we simply just need to go ahead and use them the first step in using conditional formatting is you first need to go ahead And select your range right so step one one you need to go ahead and select your range and then step

two you want to go ahead and choose your formatting so we'll take a look at a different a couple of different types here we'll take a look at data bars first then we'll take a look at color scales then we'll kind of double back and take a look at some cell rules and some top and bottom rules as Well so let's see how much data we working with here I'll go ahead and press control end so we go down to row 133 so quite a bit of data here going to press control home to go

back to sell A1 well there's a lot of numbers here for the listing price I could sort this but I just want to go ahead and see which ones stand out I'll go ahead and highlight all of my listing prices here now that they're all highlighted I'm going to go ahead and click on Conditional formatting and I'll use one of the data bars here so here's a data bar that I can use I'll click on the I'll go over the solid fill so notice what this does if I kind of make this column wider so

this gives us a clear indication in terms of which values are higher than the other I can clearly see on row 22 this house represents one of the higher prices and If you scroll through this gives you a really good visual in terms of which ones are cheap cheaper or which ones are more expensive now we going to go ahead and apply another one here let's take a look at color scales so here are different color scales here's the green yellow red color scale I'm going to go ahead and remove the other conditional formatting from

there so I'll click on manage rules and I want to remove this one here for The data bar I'll click on delete I'll click apply and I'll click okay so here's the color scale so the color scale we can see the green represents the higher values and the red represents the lower values so as you kind of scrolling through here keep your eye on all the greens and that gives you a really good visual in terms of which ones are standing out as well so very good let's take a look at some other conditional formatting

what about square Footage I'll go ahead and highlight all of my square footage range here and for this one I'll click on unconditional formatting well this time I want to let's say I want to find a home where the square footage is greater than less than or between a certain value well I'll say greater than so as long as it's greater than in this case it's giving me 2373 I'll just change this to 2500 right so we can see it's already Applying that light red fill of dark text maybe I want green fill with dark

text and now I can go ahead and press okay so I can already start to eliminate some of the houses that I don't want because they're not meeting my square footage requirement here well what about bedrooms we'll go ahead and run another one for bedrooms and maybe I want exactly right so maybe I want equal to I want exactly three Bedrooms I'll go ahead and press okay so now I'm getting a match a couple of different places here I'm good to go all right I'll go ahead and do one more for the bathrooms so maybe

this one let's say I'll go for greater than say greater than two greater than two bathrooms I'll go ahead and press okay and it looks like we have a nice m match here on row 121 all of my Requirements are being fulfilled so that's what conditional formatting can do for us we do have another type of conditional formatting in here which are top and bottom rules so these really give some really nice analysis we can highlight the top 10 items on a list based on value top 10% bottom 10 items bottom 10% whatever is above

the average so to do that I'm going to go ahead and clear the conditional formatting for my listing Price here so I'll go back to manage rules and for this entire sheet I'll go ahead and clear that graded color scale I'll delete that I'll press apply and I'll press okay let's take a look at one of the other type of rules which is the top bottom rules so top 10% which one of these listing prices are in the top 10% I'll go ahead and click here and we can see it's already highlighting them for me

I can change the number here Maybe the top 20% I'll go back to top and bottom rules what about the bottom 10% so some quick analysis that we can use there as well so conditional formatting is another type of filter let's take a look at the IF function now the IF function is is a really nice tool it's kind of the base or the standard for all of our logical functions so in this case we'll ask a particular question here is we have a Few salespeople s Sam E Brown W Danny M Meyer s Sandberg

and a John we're tracking their sales for February for example in week one s Sam had sales of 9550 in week 48965 for a total of 36245 so the question that we want to ask is well was the goal met so that's the number one question we need to ask the next question we need to answer here is well is the 36245 is that greater than or equal to the monthly goal which is 34,000 so we need to compare these two cells and see if that is true or if that is false FSE so that's

the second question we need to ask the third question we need to ask is well if it's true what value are we going to place in this cell right so we're either going to say yes or no so that's it what's the goal met in Order to answer that we we use the cell reference here is 36,2 245 greater than or equal to 34,000 yes it's true so s Sam did meet the goal we'll put a yes in here as well well that's exactly what the IF function will do for us right we already know

the cells that we want to compare here we're comparing cell F7 to cell B4 we already know what we want to say if that logical test is true we or say yes the goal was met if it's not true We'll simply say no let's go ahead and insert our IF function I'm going to click on the formulas tab here so I'll start off by clicking on the cell where I want to place my answer I'll click on the formulas Tab and I want to go ahead and insert my IF function so I'll click here here

is my insert function dialogue I'll type the IF function up top I will press go and on the bottom I'll press okay Welcome to the function arguments box really nice tool because it gives you the name of the function tells you what it does and it also shows you what it's asking for so the first thing we need to fill out is our logical test well our logical test is remember we're comparing the value and cell F7 so I'll go ahead and click on F7 and I'll use my operator I'll say is that greater than

or equal to the value That is in B4 Excel has already evaluated that statement the value in F7 is indeed greater than or equal to the value that's in B4 because 36245 is greater than or equal to 34 it's telling us that it's true so if it's true I'll simply say yes right I'll say yes uh gold met and and if it's no or if it's false in this case rather I'll say no go not Met we can see our answer is going to be yes gold is met because that condition is true if I

press okay we get yes the gold is met as well now I don't feel like writing this formula another five times here so remember our little friend the fill handle we can grab the fill handle here and pull it down and that will copy the formula for us but we're going to run into an issue and let's do a little bit of troubleshooting to see what happens Here I'll go and grab the fill handle and I'll fill down and it's copying the formula for me but we can see we're running into an issue here we

can see that E Brown did not meet the goal right 31475 is not greater than or equal to 34 so that does not compute neither does let's see neither does s Sandberg did not meet the goal so let's figure out what's going on we can double click on the cell To gain some insight to see what's happening with the formula if I double click here on the cell so we can see what's happening it's moving away from the monthly goal which is 34,000 so it went down one row because is using relative referencing so if

I go down to row 9 we can see it's locking on to the correct total in row nine but it's already moved away two spots from our monthly goal so what we need to do is we Need to Absolute reference the monthly goal so to Absolute reference we're going to write instead of writing B4 we're going to say dollar sign B dollar sign 4 and that will tell Excel to lock onto that cell don't keep moving down down uh one row at a time here I'll go back to my original formula and right here in

the cell I'll place the dollar sign right in front of the B I'll place the dollar sign right right In front of the four I'll go ahead and press enter and now if I autofill down we can see that this is working properly if we do a quick test if I double click I can see it's still locking on to cell B4 if I go to the last calculation here it's still locking on to B4 it did not move down several rows as well so that is the IF function it evaluates a statement to see

if it's true returns One value if it's true another value if it is false let's take a look at a few useful database functions we'll take a look at a few we'll take a look at some if we'll take a look at average if and we'll also take a look at some ifs now here is our peer company q1 expenses we have several divisions East North South and West we're also tracking different categories such as tech support telephone copying overhead Software and we're tracking the expenses so basic basically in the east in January we had

$800 worth of expenses for tech support in March we had $700 worth of tech support in the East total for quarter 1 for tech support in the East was 2,150 so we already have our data set here it goes down to row 61 so we can see it's row 61 1 so let's go ahead and answer some questions well what are the total expenses by category So if I wanted to go ahead and create a small worksheet over here to summarize what were the total expenses where the category was software so what I would need

to do I need to go down the category range here and every time I see software I need to go ahead and add that to the running list so here's one on row eight another one down here on row 23 and let's see another one on row 37 pretty sure I may be missing one here Another one on row 53 and we need to go ahead and add the totals from our last column here so we'll add this 3785 to the running total this 3750 this 3805 and so forth and so on so we add

all those numbers up and we'll go ahead and get our total for software well we don't need to do that there is a function that will do that for us and that function is called the sum IF Function let's go ahead and take a look at sum if so basically what we're saying is we are going to summarize the total expenses if the category is equal to Software so we're going to go ahead and summarize the total expense column if the category is equal to software so there's a function that's already programmed that's going to

ask us for these things it's going to ask us in particular well what is the Range what is the criteria and what is the sum range now we already have all the information we just need to go ahead and point to these and the sum of will take care of the rest for us let's go ahead and insert our sum if statement I'll click on Cell j7 I'll head on over to the formulus tab I'll insert a function and I'll look for Su if I'll press go I'll press okay here is some if and as

I mentioned ask us for the range the Criteria and the sum range well our keys are right here we want to search the category range so I'll highlight all of my categories from C4 down to c61 the next one is well what is my criteria well I could type software in here but I'd rather refer to the cell reference because then I can use this as a little input field so I'll refer to that cell instead of software I'll go ahead and click here so so i7 whatever is an i7 will perform Our calculation two

down one to go the last one is the sum range so which column do I want to summarize is it the January expenses February March no I want the total expenses I'll highlight G4 to g61 we already have our answer it's already found all those instances where the category is software the to is 1,215 I'll go ahead and press okay and there's my answer I'll just go ahead and give that the currency format and I'm Good to go now the average if it works the same way it's going to ask us for the same things

right range criteria and the average range so for this one to answer this question we'll use the average IF function so the question we want to ask is well what is the average per rent expense so we'll count the amount of rent expenses we had we'll summarize the total expenses and divide that by the amount Of entries that we had for rent we already have all the information that we need the range is going to be the category the criteria is going to be the rent we want to go ahead and run the math on

our total expenses column so I'll go ahead and insert the function here I'll look for average if I'll press go I'll press okay what is our range well category so same thing go ahead and highlight that range C4 to C61 the criteria needs to be rent in this case it's sell I13 the average range needs to be the total expenses highlight all of my total expenses and I'm good to go so the range is our category range right the criteria is expressed right here in this cell and then finally we want to run the C

the numbers on the total expenses column So there we go our total is 17790 I'll go ahead and press okay and we are good to go I'll also apply the same format to there I'll use my format painter 17790 now the benefit of using these cell reference as opposed to typing it in here now what I can do is I can use this as a dashboard what were the total expenses for supplies if I type supplies in here and press enter it gives me the Updated answer right away I'll change this from rent to supplies

as well and we get our instant answer as well so that is the sum IF function and the average IF function let's write a function that will give us the expenses by division category so what were the total expenses where the division is east and the category is software so for example we need to go ahead and find we can see That there's a match right here on row number eight here's the division is East the category software here's the total expenses here as well there may be some other matches let's just go ahead and

check so to do this we need a function that will allow two logical tests so two ranges and two criterias Su if only allows for one so we need to use sum ifs which will allow for multiple I'll go and click on my formulas tab here let's insert the sum Ifs function here is my sum ifs so again it's going to ask us for the same thing what is the sum range what is the range and what is the criteria it wants to get the sum range out of the way right away we already know

we want to summarize the total expense column so I'll go ahead and highlight all of these here G4 to g61 here's a preview of the array now criteria range one well it's going to be division so I'll highlight all of my Divisions criteria one which division are we looking for well we're looking for the East Division represented in cell i19 now that we finished criteria range one and criteria 1 we now have room for criteria criteria range to I'll go ahead and click here the second one the category needs to be software so we'll highlight

all of our categories criteria two needs to be software and we're Done the total 5875 so apparently there was only one entry or one instance where we had a software expense in the east region 5875 so that is the sum ifs function now it did not matter I could have used the category range first and then division second does not matter it would still make the calculation for us here as well I'll go ahead and press okay and I am good to go now because we're using the cell Reference I can go ahead and change

this so what about um instead of software in the East What about rent in the East I get an instant answer 24,000 What about rent in the north so now we have a really nice dashboard that we can use to summarize all of our data so this one is the sum ifs function really nice tool takes away the manual work of highlighting several columns and several cells and adding it all up Manually let's take a look at recommended charts here we just have some sales figures here is our sales rep we have R Smith H

James S O'Brien l Cary and K dun here are their weekly totals so week one R Smith 4520 week 2 3620 week 3 2560 and then week four 2750 well we can create a chart from this I just need to go ahead and click on the insert tab here now when we're inserting a chart I don't have to highlight the entire data I can just Click anywhere inside of my list here because it's a well- defined list no blank cells rows or columns so I can go ahead and insert any type of chart that I

want but what I want to do is insert a recommended chart because Microsoft Excel is going to give me a customized set of charts that it thinks will fit best with my data so I can get an automated chart here which is exactly what I want if you like shortcuts we press alt plus F1 on Our keyboard we can get the default chart for this data set here I'll go ahead and press alt F1 on my keyboard and just like that here is our chart for our data now I'm going to go ahead and undo

that and go ahead and delete my chart here so the traditional way is to click on the insert tab here is recommended charts if I click here here's that clustered column chart there's different charts that we can choose from here's a Stack column here's another clustered column it just kind of changed the data around we have the weeks in here instead of each of the sales reps if we scroll down we have different charts clustered bar line chart so lots of charts that we can use I'll stick to the default one here which is the

clustered column I'll go ahead and press okay and here is my chart so pretty cool I have a few buttons over Here in the top right of my chart these are chart elements so what I can do is I can turn some of these features off or on for example if I wanted to add an access title I can go ahead and do that I'll click on access title adds a little text box of the horizontal and vertical plane but I just want one on the horizontal so I'll remove it from the vertical here and

now I can just go ahead and type some data in here maybe I'll just say sales [Music] reps I'll go ahead and add a title for my chart I'll just call this February sales I'm good to go now some other things I can show the data labels at the top of the columns if I want to I can show the data table on the bottom of the chart which is pretty cool and then I can turn off the grid lines I can also turn off the legend or I can move the Legend So if I

click on The small Arrow here I can show The Legend at the top or I can show it over at the right I'll leave it over here on the right now another option is to click on the paintbrush here so maybe we would just want to go ahead and use one of the predefined chart Styles so here's style number two so style number two in my case here it's showing the data labels at the top of my columns here it's basically Removed my vertical axis over here if I click on style number three it's incorporating

the the data labels as well and if I keep scrolling down there's just different types that I can use right now the other styles my data labels are just kind of overlapping each other I can resize the chart just going to make it a little bigger see if that looks any better still having some issues they're still kind of overlapping So I'll turn off the data labels and I'll go ahead and choose another Style style number eight is a dark theme so that looks pretty good I'll go ahead and stick with that one I can

also change the colors so if I click on the color tab here I'll have different colorful paletts to choose from here's colorl palette two three and four on the bottom I have some monochromatic color palettes to choose From different shades of blue and so forth and so on I'll go ahead and stick to the original one here that colorful palette and I'm good to go if I want to change the chart type I can always do that notice that we have two contextual tabs here for our chart design if I click on chart design I

can do a couple of things I can change the chart type I can also move the chart to another worksheet if I want to I can switch the rows and Columns and I can also go ahead and select which data I want to be displayed on here as well I'm going to go ahead and switch the row and column and just a really subtle effect but if I click on switch row and column it basically switches it so now instead of my sales reps being in these different clusters it's showing week one so week one

here's the totals for R Smith James O'Brien Carrie and dun so just a subtle switching the row and Column can just kind of give you another look for your chart here as well so that's how we insert a recommended chart let's go ahead and add some data to our chart now if I were to add another record here on row number 10 my chart will not show that data right away what I would need to do is I would need to come back here and click on select data and go ahead and reselect all the

data that I want to be displayed on my Chart but what I prefer to do is just to convert this list here into a table so remember we can press contrl t convert this to a table and now the benefit of doing this is if I add another record my chart will update automatically so charts are dynamically linked to a table so look what happens when I start adding some data in here notice it's already reserved a space for my new entry so pretty cool I'll go ahead and Put some values in here and there

we go so that's a nice simple way to be able to have your chart dynamically linked to your data set as well now we can change the chart type I'll click on chart design here I'll change the chart type maybe I want something else like a 3D clustered column we'll go ahead and press okay also go ahead and change it to something else maybe I want a line chart go ahead and press okay so Whatever chart you change it to it's still going to incorporate your data into that chart so we're good to go I'll

go ahead and move this chart I'll go ahead and move it to its own sheet I'll use the new sheet command here I'll press okay here's the chart on a special sheet we can notice that the grid lines have been removed and we have the capability just To go ahead and display our chart here even though we move the chart to another sheet it's still dynamically linked to our original data set so if I go back and make any changes it will update automatically for my chart here as well another type of chart that we

can take a look at is a small chart so what if we do not want to have a large chart we just want to have a chart in a Cell maybe for some Trend analysis well let's take a look here we have a few Sales reps here down to row 31 our first salesperson is Kathy Albertson so we're we're tracking Cathy's sales for May June July August and September and October as well so if I were to ask you if you can just kind of give us an analysis to see how Kathy's sales are trending

well we can see that from May to June we went down in June we went up in July went back down in August went up significantly in September and also went up in October as well so this is my attempt at just kind of showing how Kathy is trending here as well well we'll appreciate the trend line better than my failed attempt here at trying to manually create a trend analysis let's insert what's called a spark line right so I'll go ahead and put Trend analysis right here so here's my Trend analysis so click on

the insert tab now When you click on the insert tab you'll notice that we have a group that's called spark lines there's three different types of spark lines that we can use we have win loss we have column and we have the line I'm going to go ahead and insert a line spark line we can always change it to column and wind lost later let's go ahead and insert a line here so I'll click on the line and it tells us right there spark lines are many charts placed in single cells each Representing a row

of data in your selection well that's exactly what I want it's going to ask me two things where do I want to place the spark line and where is the data set that I want to analyze so pretty nice so here we go the location I want the spark line to be right here in this cell i12 it's already selected well my data range well I want to go ahead and analyze the data Kathy sales from May through October so I simply need to go ahead and Highlight this range so we're good to go here

are the numbers that I want to analyze here's where the spark line will be placed I'm good to go spark line is going to do the work for me there's a lot happening in the background that we're not privy to really appreciate this tool I'll go ahead and press okay and here's our Trend analysis so we can see went down went up went down went up Significantly in September and kind of stayed the same for October as well now I'm going to go ahead and make the row a little higher so I'll make it 28

there we go so that allows us to to kind of see it a little better here we can also do things like change the background color of the cell to make sure it's kind of standing out but we're good to go now once we insert the spark line I can click on the spark line right Here and I can do some pretty nice things so I can kind of highlight the high point and the low Point here as well I can also go ahead and give it a particular style really just changing the color of

the spark line here so I'll go ahead and notate the high point notate the low Point as well we can see the low point is here for June high point is for um October I'll also go ahead and change the Style right so lots of different styles here again changing the fill color may help for these to show a little better I'll look for go for orange and maybe I'll change the fill color if that cell to that's probably too dark May I'll go for a dark blue here and so there we go now for

the high point I'll change the color To if I go with white here so we can do some pretty nice things with the spark line here as well right going to go ahead and remove the fill color from there I'll say no fill and need to go ahead and put the marker color for my high point back here as well so there we go now what's really nice about the trend analysis if I change the values here this is going to update automatically right so notice how it Updates automatically here as well so pretty pretty

cool so another object that's dynamically linked to our data set here as well now I want to go ahead and create lines for my remaining sales reps here I don't have to come in here and and create individual spark lines what I can do is just grab our little friend here the fill handle go ahead and pull it down and this will create the individual spark lines for the rest of my sales reps as well before I do that I'm just going to go ahead and change this to the column so if I click back

on the spark line here I want to go ahead and change this to the column let's see what that looks like so here is a small column chart you can see high low high low high and then High we can also see that the high and low points are being indicated here as well we also have wind loss wind loss only looks for positive or negative values so positive values appear at the Top of the cell negative values appear at the bottom so it does not really apply here because we only have positive values to

work with I'll go ahead and put this back to line and then I'll go ahead and autofill down pull it down to row 31 and here are individual spark lines I'll go ahead and adjust the row height for all of them so we can see them a little better so right click change the row Height to 28 and there we go right so that is how to insert and manage spark lines here we have some order details for December of 2023 if we scroll down this goes down to row 68 so quite a few entries

here now what if we were asked to be able to answer some questions on this data set so we need to go ahead and present this data in a way that our audience will be able to decipher the information Understand be able to ask questions as well so we can see that we have a few things here we have the order ID we have the order date customer ID but what if one of the questions was well what were the total sales for our products based on city what about by state what about by region

sales rep what about by product or by category so we can see that we have several options here we have the revenue That's being calculated here and column U we have quite a few columns as well so in terms of categories here are the distinct categories that we have so we want to be able to run some numbers based on categories we have several products lots of products as well how can we find out what the total sales were for green tea and New York now in terms of let's say region we have four regions

east north south and West what about sales by sales rep we have quite a few sales reps here as well so this is a daunting task because again this is a lot of data and we would need to be able to manipulate this data sort filter run some calculations right use a lot of the table tools but it's going to be a lot of work just to answer one question we're going to have to make space and then move things around that's where a pivot table comes in so the pivot table already has a lot

Of built-in functionality for us we simply need to go ahead and convert or extract this data into a pivot table and then we'll have a lot of analysis tools um available to us I do want to check out the quick analysis tool though so for the quick analysis tool [Music] or I'll just say quickly analyze so this is something that I use often here if you press uh control+ Q on your data set this will open up the Quick analysis dialogue so I'll press control q and down here in the bottom is giving me some

some quick analysis tools so remember I want to insert a pivot table so I'll click on table tables here and let's see what pivot tables Excel can create for me so look at this very first one this is giving me the sum of Revenue by region so I can see that the highest revenue was in the North Region 26912 so pretty Cool sum of let's this one sum of Revenue by shipper name so we can see company C we had the most Revenue through that shipper and then this one here is the sum of the

shipping fees that one doesn't really help too much so we can see that we can get some quick analysis and this is basically what we want to do we want to condense all of these rows and columns into a a nice simple table where we can Quickly answer some questions now if I click here this will actually insert that pivot table for me so if I click here here is my pivot table right out of the box and I can see all the data right here as well but let's go ahead and create it from

scratch I'll go back to my data tab here and to insert a pivot table let's go ahead and let's first convert this to a table so I'll press contrl T convert this to a Table when you insert a pivot table from a table you simply need to right click on the pivot table and refresh if you want to update the pivot table to show any new values or records that were added to your data set so it's much more efficient than working with a list okay now one of the table tools that's available is actually

to summarize our data with a pivot table so I can click on here and this will open up a new worksheet for me and I'll have the a Blank canvas to work with I can also click on the insert tab and choose the pivot table command they both do the same thing I'll do this the traditional way I'll click on insert and I'll click on pivot table just make sure that you clicked on any cell inside of your data set here I'll go ahead and click on pivot table here's my dialogue box as always it's

always asking us what we want to do before we actually proceed so it's Asking me well where is the table or range it's locking onto this table which is now named Table Six I want to go ahead and and place my pivot table on a new sheet I'll go ahead and press okay and so here we are here's our blank canvas here's our pivot table over here to the right here are all the fields from our data set and then we have these four areas filters columns rows and values and we'll see just by Dragging

these fields to these boxes it will start to generate our pivot table over here as well but the first thing I want to do I want to go ahead and rename my pivot table right so I'll go ahead and right click here and I'll choose the pivot table options command now right at the very top it says pivot table name right now it says pivot table 5 I'll go ahead and change that I'm going to call this December sales so one more thing I want to do if you take a look at our our Fields

here we can see that they are not sorted in alphabetical order we have quite a few Fields so it'll make sense if we can sort these a toz to do that I'll go ahead and click on the display tab here and on the bottom I'll be able to sort A to Z so I'll sort the fields A to Z I'll press okay and there we go all right let's go Ahead and start building our table let's just add a few I want to go ahead and get the total revenue so I'll look for Revenue I'll

drag that to the values box and right there it gives me my answer my total revenue $ 81,1 785 so pretty cool now if I want to go ahead and change that I want to give the accounting or currency format what I can do is click on the drop down right here in the values Box I want to get to the value field settings here and from here I can go ahead and change the number format to accounting I'll go ahead and press okay and then I'll go ahead and press okay again there we go

so that's how we insert a pivot table and that's how we add a feel to the box to get our first answer now let's go ahead and try and answer some questions on this data set Here now what we want to do we're summarizing data we want to add numerical values to our values box here now we can build the table either by dragging for example the revenue to the values box here or we can ask the pivot table to kind of construct itself for us so if I were to just click on the check

box here for Revenue it places it into the values box for me because it's a numerical value it's also summarizing the revenue as well right so but I can Change this so what if instead of summarizing the revenue I want to count how many transactions we had let's say by region so because we have a order ID let's see here's my order ID I'll go ahead and click here so it's giving me the sum of the order IDs well that's not what I want I want to count how many orders I had so to do

that instead of the sum I want to change this to the Count so to do that I'll click on the drop down here I'll go back to Value field settings and notice right here I can change it from the sum to the count function so I'll go ahead and change that to the count and up here for the custom name that appears instead of saying you know count of order ID I'll change this to something else like I'll just say orders I'll go ahead and press okay and let's see what Happens so we can see

it now it says orders and so I have 65 total orders and that's correct because we had 65 records on our original data set so it's nice to know that I had 65 orders but now let's go ahead and figure out how many orders we had let's say by region so I'll click on region here here and so now I'm getting some more information so here's my grand total for all of my orders is 65 but now I'm getting some more specific information So 16 orders in the East 20 in the north 12 in the

South and 17 in the west so as you're beginning to see the pivot table there's a lot that it's doing behind the scenes that we're not privy to a lot of calculations a lot of logical functions right that's happening here as well well now that I have that I'll go ahead and reset my table and let's go ahead and focus on the actual Revenue right for here I have the let's see I have the by region let's go ahead and add Another detail in here let's put the region into the columns instead and let's put

something else a lower level into the rows maybe I'll put the ship state in the rows so now just by doing that we can get some really specific answers here as well right so we can see that for example in California and the west region we had four orders submitted in that particular Region so we'll start to see as we start moving these fields into these different boxes our table is going to get more advanced and provide more information for us as well but for now that's how we change how our values are being summarized

let's go ahead and add the revenue to the values let's go ahead and apply the accounting format as we did before now we have the sum of Revenue let's go ahead and get the revenue by region let's place the region into the Columns and let's figure out what else we want let's go ahead and grab the sales by category I'll place the category into the rows and let's just pause for a moment and take a look at the data that's being displayed here so this is quite a bit of data to take a look at

right but think about this in tabular form I'm actually going to go ahead and click on the design tab here and I'll change the report layout to more look Like an Excel table right we don't have the grid lines in here I'll change the layout here to tabular form there we go so now we can see that we are getting our totals so let's go ahead and identify all of these numbers here so here's our grand total of all of our Revenue down here in the bottom here's the total for the east region north South

and West so we're summarizing that column here as well and over on the right here Here is the totals for baked goods and mixes so just by having the region and the columns the categories and the rows and the revenue and the values we can see we can answer a lot of questions in condensed form here right we can also sort so if I wanted to sort this this total column here here I can click on the data tab I'll sort Z to A or largest to smallest and we can see that beverages we had

the highest amount of sales for beverages 19630 oil generated the the smallest revenue 1,46 and 15 now we have a lot of fields in here that we can use now when you're building a pivot table you don't have to use all the fields right you don't really have to do that you can just keep it simple go ahead and use the fields that you want to answer a particular question I'm going to go ahead and change my view instead of having the region and the Columns I'm going to go ahead and move the region into

the rows and let's see what happens when we have two fields in one of these boxes so now that I have two fields in the rows I have two levels so region is level one and my category is a level two item so therefore we're going to see that indicated on on the pivot table so here's the region that's a level one item and then we can see that our Categories are level two as well so they're kind of indented over so we can see that detail here I if I switch them I'll get the

opposite so here's beverages for the east north south and west dried fruit for the east north south and west so that's the nature of the pivot table it just automatically updates all the data for us changes all the numbers B Bas on what we're trying to answer now this is not the best example you want to use higher level Items at the top of each of those boxes so you can get a you know better view of your data here as well I'll go a and put the region back into the columns and I am

good to go I'll remove the category here so I'll remove that and I'll go ahead and click on City I'll move the city into the rows and now we have a breakdown for our cities here as well let's go ahead and filter our pivot table we can filter a few ways we can Filter notice that we have a row and column label filter we also have a filter box and finally we have on the pivot table analyze tab we have what's called a slicer so if I click here here is my slicer let's take a

look at the built-in filters here here's our first filter so we can filter by city so because we have the city and the rows if we click on this row label filter we can filter by City And this is nice because if I want To go ahead and filter let's say I just want to filter by I'll go for I'll unselect these here I'll just choose Chicago and Denver if I press okay notice how my table is only showing the data for Chicago and Denver well I can also go ahead and filter by region I'll

go ahead and reset this filter what if I want to just filter by the east region if I press okay it only shows the States that make up the east region here and my data as well we can use filters together just like with a table to drill down to find what we're looking for let's take a look at another filter while what if I want to go ahead and filter by product name I'll go ahead and drag the product name to the filters box here now that I have the product name here we can

see that we have a filter at the very top of our Excel document and now I can go ahead and filter by product Have several products I'll go for just the first three so I'll select the first three here and if I press okay we can see all the numbers have been updated so for those particular items total of 5916 so that is using the filter box here I'll go ahead and clear that filter we can see the filter is active right here and we can also clear it from here I'll go ahead and make

sure that Everything is selected I'll press okay everything is back to normal so those are just some different ways that we can filter our pivot table let's take a look at the third filter which is a slicer let's go ahead and use the slicer to filter our data I'll go ahead and click on insert slicer here now notice we have a slicer for each of our Fields right one for City product name so forth and so on Let's go ahead and insert just a few we have a lot of fields again we'd want to keep

it simple I'll go ahead and insert a slicer for the state I'll also insert one for the the region and one for the salesperson I'll go ahead and press okay here are my three slicers I can resize them so I can see all the values so I'll just resize them so I can see the bare minimum here now our slicers they come with a contextual tab So we can change the colors we can also change the amount of columns that's being displayed I'll just just give them each a different color so there we go now

if I wanted to filter by a region if I just wanted to answer a question based on the East if I click on the East button here on my slicer it's only showing for the East I'll go ahead and clear that if I Want to get sales revenue based on a sales rep let's say Michael there Michael had some sales in Milwaukee $917 what about by state I'll go for California here's California here as well if you want to select multiple you can hold down the control key and select multiple so I'll go ahead and

select [Music] Colorado Florida New York Oregon and there we can see that that works as well so those are the slicers my favorite way to filter data either on a table or a pivot table as well just remember to clear your slicers before you move on click on that clear filter icon in the top right of your slicer that will clear the slicers for you and put your pivot table back to normal it's time to create a visual in the form of a chart based on the data That's being displayed on our pivot table I'm

going to go ahead and delete a few of these slicers here so I'll just click on it and press the delete key I just want to see my region slicer I'll move this over here and I'll go ahead and click on pivot table analyze and I want to go ahead and insert a pivot chart now what's really cool about the pivot chart is it's dynamically linked to my pivot table so whatever is being displayed on my pivot table is going to Be dynamically being displayed on my pivot chart even if I were to remove some

of these fields move them around they will be displayed here as well I'll click on my Pivot chart and here are the different types of charts well I'll stick to this clustered column chart here and I'll press okay now here is my Pivot chart I'm going to go ahead and close the fields over here so I can see My chart going to make it a little bigger I'll give it the the dark style here make it a little easier to see right so here's my pivot table here's my Pivot chart and here is my slicer

now I can filter from the table from the slicer or the pivot chart so for the pivot chart if I want to go ahead and filter let's say by product name and I'll just choose a few here and press okay notice my Pivot chart has updated and Also my pivot table has updated as well so any change you make to either of these objects they will update if I click on the east region button on my slicer we can see that everything has been updated here as well I'll go ahead and clear the filter from

my slicer I'll also go ahead and clear the filter from my Pivot chart go ahead and select all items and there we go now I'll go ahead and turn back on the field list Here and I'm just going to make a few changes so I do want the revenue um but instead of the city and the rows I'll go ahead and remove the filter I'll remove the region I'll move the city as well so right now we're just getting the grand total what about grand total by let's see what have we not used it well

what about bu let's say shipper name I'll put the shipper Name into the rows right and here's a basic pivot chart so we can see that Christina Lee most of the revenue went through Christina $159 And1 cents if I want to go ahead and add the region here in the columns let's see what this will look like so it does make the chart a little harder to read but we do have the details there as well so we're good to go I'll put the product uh I'll put the category The Columns there we go right

so just like With the recommended chart we can change the chart type to display another one maybe I want a line or a pie chart I can go ahead and do that if I want to here as well so that is how we insert a pivot chart to display our pivot table data I'll go ahead and simplify this I'll put this back to my clustered [Music] column make it 3D and I'll just go ahead and do a simple instead of I'll move the categories here I'll move the shipper name and I just want the state

in here there we go so nice simple pivot chart just as a recap we learned data analysis some of the basics converting list into tables for analysis a lot of table tools are available to us that helps us to efficiently analyze our data we're able to summarize data with Logical functions the IF function average if and SU if we're able to convert a list into a table and summarize the data with a pivot table we're able to filter the table move Fields display different values using the sum function or the count function as well and

then we're able to go ahead and display data with a pivot chart so just giving a visual representation of our data as well congratulations on completing the course And I look forward to seeing all of you in a future learn it course welcome to excel pivot tables you're learning with Mo Jones IT professional and educator in this course we'll learn about the highly sought after tool for data analysis which is pivot table in this course we'll explore kind of the first step just preparing data for pivot table analysis what do we need to do to

our data before we convert into a pivot Table also take a look at inserting and managing a pivot table now managing means a lot means constructing your pivot table pivoting the table querying the table we'll go ahead and insert and manage a pivot chart from our pivot table as well so we'll take it one step further and create a visual and then we'll take a look at how we can connect different data sources by using the popular X lookup function so go ahead And open up the practice file for this course and come right back

and we'll Dive Right In welcome back so I did open up my practice file here and we're taking a look at a data set so here's just some company payroll here we have some headers we have the employee number the employee we have several divisions California New Jersey Georgia and Washington we have a few departments as well sales Development staff and research we have the date of hire for each employee the amount of hours that they're logging per pay period their hourly rate and a formula over here to calculate the gross pay we also have

a status column here indicating whether they're full-time or part-time by using the IF function so if I were to present this data to you and ask you to present this data in about 30 minutes or so to our team well naturally you would first kind of take a look at The data set it's always a good idea to do that we can see that we have 94 records here and we need to anticipate some questions that may be asked so some of the questions that may be asked is well what is the sum of the

gross pay what's the gross pay by Department maybe gross pay by division and something like what is the average hourly rate well as you can see there's a lot of other questions that we can ask here and so if we were To try and answer these questions what are some of the things that we can do well we might use some sorting we may do some filtering we may need to use some functions right like the sum function the average function we may need to have some basic calculations in here as well that we can

use so there's a lot of different things that we would need to do here we would need to go ahead and just kind of move things Around and kind of reshape our data here as well so for the sum of gross pay we could you know enter the sum function and then go ahead and grab all the gross pay but think about this this is not something that we want to be doing while we're presenting this data we want this data to be you know readily available with just a simple click of a button we

don't want to have to go through the process of writing formulas and that's exactly what the pivot table will do for Us the pivot table is going to give us a nice condensed table with all the information that we need just a simple table and it's going to condense this large set of data into a nice compact list with some totals on the bottom and some totals over here on the right as well well let's take a look at a fully functional pivot table and let's see what we can gather from this here so here's

a fully functional pivot Table this is based on our data set and as you can see the pivot table is in a condensed form and if this is your first time looking at a pivot table this may be overwhelming so I'll go ahead and annotate some of the things here we can see that we have some totals down here on the bottom so this is the total for California New Jersey Georgia and Washington over here's the grand total So this is the sum of all of the gross pay for all of our divisions all of

our departments there as well so if I were to ask you well what's the sum of the gross pay for Washington we can say the sum of the gross pay for Washington is 18,154 52 well what about something else what if we wanted to grab the gross pay for a department such as the development department well then we'll take a look at the column over here to the right and We can see that this is the development sum of gross pay for the development team so we can see it's kind of three-dimensional here we're getting

a lot of nice information in here as well so we can already answer some questions sum of gross pay gross pay by uh division gross pay by Department as well now if I wanted to to answer some deeper questions here we can see that the total gross pay uh the highest gross pay is attributed to the sales Department so now what I can do is I can go ahead and expand the sales department and now I can get a breakdown of full-time staff inside of the sales team 15 46590 as opposed to part-time 10,962 81

so this is the pivot table condenses all the information for us notice we don't see any formulas in the formula bar so everything is happening in the background for us we can see it has a nice layout it's visually appealing it's More efficient to work with and if I wanted to just kind of move things around I can simply just if I just want to focus on maybe the gross pay by Department I can do that now I can have a nice condensed view just to focus on what I want to use here as well

so think about it go ahead and write it down what do you consider to be a major benefit of working with a pivot table take a few minutes write down your answers come right back and We'll actually insert our pivot table welcome back so let's go ahead and get this table or this data rather ready for a pivot table so the question we want to ask is is this data ready for a pivot table so we are working with the list so we do have our 94 records here we go down to row 98 and

we can see that this is sorted by division so California and then Georgia and then New Jersey and then Washington here as well so the first thing to ask is remember when We're working with a list we want to make sure that we have a couple of items here we want to make sure that we have meaningful and unique headers we also want to make sure that we have unique labels so in this case our labels are the employee ID number and they're unique and they're unique to the entire record and then we have complete

records now remember a record because this is a column based table we have our record is A full row of data here as well so we already have a unique and meaningful headers they describe the data below low so for example the gross pay we expect to see gross pays and no dates or text for the employee we have the names in here as well so we're good to go right the next thing we do have unique labels so we can check off these first two items here as well the next one we want to

make sure that we have complete records and we have no blanks now this Is a really big deal because when we create a pivot table when you have Blanks on your data set they will actually show up as Blanks on your pivot table it will say blank plus it will be incomplete so for example what I do is I use the count blank function this is a good first step this tells me how many blanks I have in my data set I have the function right here it's a simple function we tell Excel to count

the amount of blanks from The range A4 to j98 so if I were working with this data set and I deleted a couple of cells here it tells me right away that I have two blank cells right so right here we can see it's telling us that so that's a good first step to make sure so once I see that I can simply just go ahead and rectify those blanks and as I Rectify them it goes away so we want to make sure that we have zero blank cells in Our data set the other thing

to do is you want to check formatting especially dates now if we take a look at the date of higher field we can see that these look like dates to us but if you take a look at the numbers command group this tells us that this is not using the date format this is a custom format now this is a big deal because a lot of our data is time is you know it's based on time or dependent on time quarterly reports monthly reports so forth and so on if You do not have a properly

formatted date field you will not get the benefits of the date tools that are available in your pivot table so I'm going to go ahead and fix these dates I'll go ahead and highlight all of them and I'll just come back up to the numbers command group here and I'll use the short date format and we are good to go right notice one of the things that we'll kind of discuss this later in the course but One of the things that we can always ask about our data is how we can how can we improve

the data so that we have more reporting features notice in column J that I've included a simple IF function to determine whether or not the employee is a full-time or part-time employee so if they're working less than or in this case greater than or equal to 40 than they're full-time less than or equal to then they are part-time so this is a great way to just improve the reporting Because now we have a status field that we can go ahead and run data analysis on as well so those are just some simple cleanup techniques we

want to make sure that this checklist is complete now that we have a nice list here let's go ahead and insert our pivot table there's several ways to insert a pivot table we'll explore some of the other other options as we go through the course but for now let's go ahead and do This the traditional way we need to click on the insert Tab and when we're on the insert tab we can see that to the far left in the tables command group we have pivot table we also have recommended pivot tables we're going to

go ahead and click on pivot table here now the first step is just to select the data that you want to add to your pivot table I don't have to select the entire data set if I just click anywhere inside in this case I'm just clicking on Cell C6 Excel is going to automatically select all the data until it gets to a blank column or a blank row so it's automatically going to select all of this data for us as a matter of fact it uses the regional command so if I press control a notice

how it selects all of my data until it gets to a blank row or a blank column as well so I'll go ahead and click anywhere Inside of my data set I'll click back on C6 here and on the insert command I'll click on pivot table when I click on pivot table and presented with this dialogue box it's just asking me where is the data that I want to add I can already see it's already selected all of my data for me and I do want to place this on a new worksheet sheet you can

place your pivot table on an existing sheet for example one of these sheets down here the only Problem is when your pivot table touches other data like text you get some errors as well so most of the time we want to go ahead and insert our pivot table on a new worksheet I'll go ahead and do that I'll go ahead and press okay and then we'll take a look at the components of a pivot table so I just inserted my pivot table here and I'm just going to reset this here there we go so immediately

we see two things on the far left here is our Actual pivot table it's blank right now we don't have anything to work with so that's the first thing we're getting a little Q here it says to build a report choose fields from the pivot table field list well that's over here so that's the second component we have our pivot table Fields right here and then finally down here at the bottom we have four areas for filtering our data for displaying a field in the rows for displaying a field in the Columns and for displaying

an overall value which is the the big ticket item here as well we also have two contextual tabs so we have one that's called pivot table analyze and we have one that's called design so right now I'm on the pivot table analyze and from here we can do things like insert a pivot chart we can refresh the table we can change it to connect it to another data Source we can insert a slicer a timeline right here we can rename the pivot table as well we have access to the pivot table options so this pivot

table analyze is going to give us all the tools that we need to manage this pivot table we also have a design tab which is right here and just like with tables and charts we have styles that are available and then there's other layouts that we can choose from and then there's other kind of sub features that we can turn Off or on we'll visit these later on in the course but for now let's just go ahead and rename our pivot table so to do that I can either click on pivot table analyze and I

can go ahead and type the name right here and that box right clicking on the pivot table gives you access to a lot of these commands that are available under the pivot table analyze as well let's go ahead and rename the table I'll click right in here and I'll just call this Payroll press enter and there we go so it is called payroll now over here on the far right there's two things I like to do before I start building my pivot table notice that the fields are not an alphabetical order they appear in the

order of the data set so what I want to do is I want to go ahead and sort them A to Z so if I click on the gear icon here there's actually two things I want to do here I want to go ahead and sort my Fields A to Z I also want to change the view to side by side right now it's using stacked I don't want to do that so I'll go ahead and sort A to Z first there we go but as you can see I can't really see all of my

Fields I have to scroll through so I'm going to change the view to side by side it's going going to give me a taller window where I can see all of my feels and over here I can see my four area boxes as well so I'll change this to side by side and Then I'll be ready to start building my pivot table so here's side by side so this makes it more efficient if I have a lot of feels I can see them here and I still have access to my areas here as well so

it's really up to you go ahead and toggle back and forth either stacked or side by side I'll stick with side by side go ahead and play around with both and then come right back welcome back now that we have the Basics set up here let's go ahead and start building our pivot table now to build our pivot table it's a matter of taking a particular field and dragging them to one of these actual boxes here so I like to start with the values box first and so what's the big ticket item here that we're

focused on well it's the gross pay so I'll go ahead and drag the gross pay right into this box or I can simply check it off so either one will allow me to do that I'll do both I'll go Ahead and drag the gross pay to the values box and as you can see let me zoom in just a little bit here we can see we already have our answer so the sum of GRA gross pay is 8436 right and some decimals there as well so it automatically runs the calculation for us it's already summarize

that column to get rid of that I can either uncheck it or drag it back this time I'll drag it Away and our pivot tables back to normal so I'll go ahead and click on the check box here and because it's a numeric value it automatically places it into the values box as well so that gives us our answer well we do want to play around with the gross pay here but let's take a look at something else What if I click on the employee number well let's see what the pivot table gives us here

so it shows all the employee Numbers and the rows doesn't really help me I'm going to go ahead and drag that to the values and what it does is it inserts the count function for us because this is a alpha numeric character it's giving us the count of employee numbers well why would we want to do that well remember the employee numbers are unique so we have 94 unique employee numbers because it's using the count function here what I can do now is Figure out how many employees I have by division so if I click

on division here look at what it does so it places the division into the rows for me and now I get some nice information so I have 15 employees in California 27 in Georgia 30 in New Jersey and 22 in Washington right what about my department if I click on Department here it also places the department in the rows well I'll put the department in The columns instead so look at how many questions we can answer just by having the employee number in the values Department in the columns and Division in the rows I can

answer a very specific question such as how many employees do we have on our staff team in California three right here so we can isolate the data as you can see the pivot table is taking care of all that for us we don't need to enter any formulas or any logical Functions like that as well so I'll go ahead and reset the table I'll go ahead and clear this out here right I'll go ahead and put the gross pay back in here again right so by default it gives me the sum of the gross pay

but let's take a look and see how many other ways we can express the gross pay I'll click on the drop- down here and here I have what's called value field Settings I'll click on here here's my value field settings dialogue and now what I can do is I can change it I can suiz the value by count and also by average well let's try the count and let's see what happens so don't summarize that just count it for me I'll press okay and look at that we also get 94 because we have 94 records

to work with so this is another way to kind of give us the number of employees by division Right so we can always change that value the way that it's being expressed let's try one more I'll go ahead and grab the average here right and so the average gross pay is 89687 what about average gross pay by division well the average gross pay in California 853 average gross pay in Georgia 936 so those are just some of the basics of how we can build a table and how we Can change the way that the value

is being displayed for us as well go ahead and play around with the different values here click on the value field settings go ahead and use these different summaries here and familiarize yourself with them and come right back welcome back so we can already see how intuitive our pivot table is just by simply clicking on a field we can help the pivot table built for us as we interact with our Fields here as well Well let's go ahead and answer some of those specific questions that we asked earlier what is the sum of gross pay

gross pay by Department also gross pay by division so I'll go ahead and drag the gross pay back to the values box and we'll focus on the sum of the gross pay here as well right so right now it's giving us the answer but I don't really like like the way this looks I don't have the currency or accounting format in here so let's go ahead and apply the Accounting format I'll click on the drop down here and I'll click on value field settings we were here before this time what I want to do is

I want to go ahead and click on the number format down here on the bottom left it's going to give me the format options that I simply just want to choose the accounting format so I'll click on number format here and here is the accounting so we can either choose accounting or currency I Like to use accounting because it kind of spaces out the dollar sign and the number we can always change it if we want to so I'll choose accounting and I will press okay and I can also press okay as well notice right

here if we want to change the name that's being displayed right here we can type in our custom name right here so instead of saying sum of gross pay we can say something else here as well right so maybe what I'll say Here is maybe I'll say pay per period then I'll go ahead and press okay so we can see the name has changed more meaningful for me at least and now we can read the number more correctly so as we continue to build the table all of our cells with numbers will have the accounting

format okay so we have the paper period here well now we're concerned about the division so let's go ahead and click on The division let's put the division into the rows and there we get a simple condensed list here we can see the pay per period for each of our divisions here we can see New Jersey has the highest gross pay well what if I put that into the columns so I can see it's in the columns here looks a little different so placing it in the columns gives us a different view I'll go ahead

and leave It there and I'll go ahead and add the department to the rows so now that I have the division in the columns and the department in the rows this is a nice view and we can answer a lot of questions here so what is our move over here so what is our grand total 85 4 30661 here's our total for Washington New Jersey Georgia California and then over here we have Our totals for the development department research sales and staff so look at all these different questions that we can answer here and all

we did so far was we place the department in the rows division in the columns and the gross pay into the values as well now now I can switch these if I want to so for example what if I want the department to be in the columns and the division to be in the rows I can switch them and the pivot table will just make The adjustments for me so I'll go ahead and move the division to the rows and I'll drag the D Department to the column here as well so this is just another

view we still get our totals but now our subtotals down here on the bottom there are for our departments and the totals on the right column four our divisions so it's just another way to display the data so it's your pivot table you're going to go Ahead and display the information in any form that you want now one thing that we can do as well is we can put the division and the department in the rows so if I drag the department here into the rows now this is pretty cool now because we have two

fields in one of these areas we have a hierarchy so because division is on the top here division is going to be a level one item and my department is going to be a level two item and we see That over here as well we can see that we have our California is the level one item and then we have the level two items down here so development research and sales so it's indented so it kind of just organizes the data for us so we did not have to do that the pivot table took care

of that for us last thing to mention here we have different buttons so you'll notice that we have a button here that allows us to Either expand or collapse this list so if I click here I can go ahead and collapse all of them and this gives us a simple View and now this is on demand so if someone says well why is New Jersey so much we can expand New Jersey and we can show the breakdown by Department here as well we can also right click on any of these buttons and when you right

click you'll notice that we can towards the middle of that menu we can either collapse the Entire field or we can expand the entire field I'll go ahead and expand the entire field and then I'll collapse it so there we go and then I'll go ahead and collapse everything as well and there we go so just different ways that we can work with our pivot table notice when you click away from the pivot table the pivot table feels goes away Also our pivot table analyze and our design goes away so it is contextual we have

to be interacting with the pivot table to get both all three create these components here as well so go ahead and play around with your pivot table just move things around and go ahead and set it up the way that you like it and come right back now that we have the basics covered for constructing a pivot table the next thing that we want to do is we want to Go ahead and filter our pivot table so let's talk about filtering there's several ways that we can filter a pivot table we'll notice that we have

two filters already built in we have a row label filter and we also have a column label filter now depending on what we have in the columns because we have the department in the columns I should be able to click on that button and filter what is being displayed by department now because I have the Division here in the rows for my row label filter I should be able to filter by division so let's go ahead and answer a question well maybe I want to just figure out well what is the sum of the gross

pay for California and Washington so I'll simply just select California and Washington from the list here and I'll press okay notice how all of the numbers have updated my grand total represents only The grand total is for California and Washington so all of these numbers here so all 15 of these numbers have been updated so in a fraction of a second the pivot table wrote These formulas for us and gave us the answer now there's a lot that's happening in the background for the pivot table we're not privy to the professional that wrote this tool

or the professionals that created this tool there's a lot that's happening in the Background now if you think about it there's a lot of logical functions that's happening here as well for example if the division is California and the department is development summarize the data from the gross pay column so a lot of logical functions Su if count and it will take us a very long time to construct a table like this but the pivot table is giving us instant analysis for our data as well let's go Ahead and take a look at the column

label filter so the column label filter we have our departments because we have our departments in the column here so if I just want to focus on the research and the sales team I can simply do that and I can go ahead and press okay and now we're able to drill down to find exactly what we're looking for research and sales department and California and Washington and there's our data so that's one way That that we can filter now remember when we're running our filters it's very important to clear our filters before we move on

otherwise this happens a lot the information will be incorrect so I can clear the filter right from here we can see that the filter is active so once I'm here I can click on it and I can choose the clear filter option here so here's clear filter from Department you'll also notice the filter Is available or active on your pivot table Fields as well so for my Division I can come over here and I can clear it from here as well this resets my table everything is back to normal let's go ahead and take a

look at another way that we can filter and we have the filters box well let's go ahead and filter by status so I'll grab the status here and I'll drop it in here I want to be able to filter simply by part-time or full-time staff so I'll go ahead and Drag the status into the filters box we can see right away that it places that filter box right here on the top left so there is our filter there's only two options if I click on there it's a simple filter we don't have all the bells

and whistles like the other ones but I can go ahead and filter either for full-time or part-time staff if I want to so I'll go ahead and click on there and I'll go ahead and filter for Part-time let's take a look at that filter so here we have part-time so I'll click on part-time here I can also search up here as well I'll go ahead and press okay and now all of our information is being displayed based on our part- timers which is pretty cool so we can see up here it's part time and notice

how all the numbers have changed well notice that we have some cells here that does not have any data in here for example we don't have any part-time Staff in Georgia we also don't have any part-time staff in Washington that's why we don't see any numbers these are not necessarily blank data we just don't have any values there so it's pretty cool we can see the pivot table is very very intuitive so those are two quick ways that we can filter we use the row or column label filter we can also use the built-in filter

box here as well but my favorite way to filter is by using a Slicer now we'll click on pivot table analyze and once we click there we're going to see over in the filter command group we have a couple of options here we're going to be using the slicer so let's go ahead and insert our slicer and then we'll go ahead and move on here as well so we took a look at some built-in filters let's go ahead and insert our slicer here so again on the pivot table analyze tab let's go ahead and insert

Our slicer now the slicer is another way to filter but we have a little more control and we can filter our data set more efficiently notice that we have a slicer for each of our fields so we have one for status we have one for the gross pay hours worked so forth and so on let's go ahead and insert a few slicers here we can insert all of them if we wanted to but not all of them Are a good candidate I'll go ahead and insert one for the Department one for the division I'll also

insert one for the status and let's insert one for the gross pay as well right I'll go ahead and press okay so here are my four slicers now I can move them around and I can resize them so for my department slicer I'll just make that a little smaller here here's my status I only have two buttons on There so I'll just make this short or shorten that and then we have one for the gross pay we can already see the gross pay is going to be problematic because we have so many unique values that

it's going to be very difficult to use this and then we have division so I'll go ahead and move this over here and I'll go ahead and shorten that as well so what exactly do these slicers do well I'm going to go ahead and click On the development button on my department slicer and we can see what it does I clicked on the development button on my slicer and you can see that it is basically filtering my pivot table to only display the information from my Development Department pretty pretty cool now what if I wanted

to focus on the development department in California so as we can see the slicers By clicking on the buttons we can control what's being displayed on our pivot table so we're basically filtering our pivot table but we're just doing it in a cooler way we have a little dashboard over here that we useing to control what's being displayed well let's go ahead and clear all of our slicers so on the top right you'll notice that we have that clear filter icon on all of our slicers here so let's go ahead and make sure that those

are All cleared now we good to go quick tip here if you want to clear all the filters that are running on your pivot table just click on your pivot table and instead of kind of unchecking them individually if you click on the data tab you'll notice that this clear filter icon is available this will basically clear all the filters that are running on your data here as well so I'll click clear and notice how it resets my table I'm back to the total of 84306 61 okay but this is about slicers so let's go

ahead now if I wanted to go ahead and run a slicer for the gross pay well there's too many values here and so this is not really a good candidate so not all slicers will be a good candidate to use it will work if I click here it shows me my one record there for Washington but doesn't really help me too much so I'll go ahead and delete the Slicer I'll click on it and now I'll just press the delete key there we go so now I have three slicers to work with now when it

comes to the slicers the slicers have their own contextual tab so if I click on my department slicer here and I locate the slicer tab well I can go ahead and give it a slicer style which I will do and I can also change how many columns are being used for my slicer Here so I'll change this to two columns and I'll go ahead and maybe choose this uh orange style here as well right so I'll change it to two columns first now I can see I have a two column orientation so now it gives

me a little more room I can make it a little wider and now I can make it shorter now I can move up my division here as well I'll also give that a quick color that light Orange for my division I'll do the same thing I'll make this two columns make it a little wider as well make it the same width there we go and now I can just kind of stack them like this now we good to go I'll make my division another color and my status another color so here are my three slicers

and I can use these to basically control what's being displayed on my pivot table as well now there's another option here on the top right of Your slicer we'll see a like a little checklist here that is the multi- select tool and if you hover over it tells you this is the multi select you can press the alt plus s key to toggle that well what this allows me to do is to select more than one button at a time right now I can only select one button at a time so if I wanted to

select multiple I can either hold down the control key and select multiple like That or if I just toggle the multi select tool here I'll put that on for both of these now I can go ahead and select sales and research and I can go ahead and select California and Washington so that is the multi- select tool so you can toggle that on if you want to be able to filter your data here as well I'll go ahead and clear my filters I'll turn off the multi- select tool and I'll just answer a question that

what is The sum of gross pay for the part- timr in New Jersey on the development department and there we go so very very specific so those are our slicers always be ready to clear your filters from your slicers I'll go ahead and remove a few of these for now I just want my division and my status so go ahead and play around with your with your slicers and come right back welcome back now that we know how to filter let's go ahead and take it a Step further let's go ahead and do a few

things what we want to do we're going to add data to our original data source and once we're done we're going to insert a pivot chart so let's go ahead and add some data here let's go back to our data cleanup worksheet tab now let's go ahead and add a record on row 99 so if you want to go ahead and add yourself there there that's perfectly fine so I'll go ahead and put in another employee number Here I'll just go with a a 40 and I'll go ahead and enter my name here so this

is going to be Jones and I want to introduce another Division and another department because I want to be able to catch the change on the pivot table so I'll go ahead and put uh let's see I'll put Texas in here and I'll Put tech for that as well for the higher date I'll just put uh I'll go with January first 2024 benefits not really running any analysis on benefits but I'll put it here on part-time all right and there we go so here's my record in row 99 but specifically I introduced a another Division

and another department so I want to go ahead And go back to my pivot table and let's see what happens here so I'll go back to my sheet one and I need to rename this as well so I'll just rename this as payroll just like my pivot table so as we can see when we come back to our pivot table we're not seeing the new Division and or the new Department as well so let's take a look and see the behavior here I'll right click on the pivot table and I'll click on pivot table Options

now we'll discuss some of these options a little later but for right now if I click on data notice that we have the option to refresh the data when opening the file right so we can do that so it's not going to just update automatically we need to do something special here so to do this I'll click on pivot table analyze and if I click on refresh that doesn't do anything right because right now this pivot table is based on a specific range So what I need to do is click on change data source so

if I click on change data source it brings me back to the original data source and we can see it's locked onto the range A4 through j98 well I just added another row for 99 so I can either highlight the entire data set or I can just come here and enter a nine here as well now this is pretty cool as well because maybe I don't want to display all this information what if I just want to Display the data down to row 25 well my pivot table will re analyze and recalculate everything here as

well but in this case I just want to include that new record that I added here so I'll go ahead and press okay and there you go so we can see that we have our new department and we have our new division here as well our number is updated to reflect the total gross pay based on the change take a look at my slicer here we can see that my Division slicer has updated to show the new division here as well well let's try one more thing I'll go back to my data clean up here

this time what I'll do is I'll go ahead and insert so I'll just insert a row I'll insert a record as opposed to adding a new record to the end so I'll just insert a row here and I'll put in another so maybe I'll say 8 it 55 I'll add a friend here and I'll give for Tech as well higher date same Thing and I'll fill out the rest of this information okay so let's go back to our pivot table here and let's see what happens so this time if I right click and refresh let's

see if my number changes from 8496 to something something else and it does we can see that it has been updated here here is the new division for Florida and here is the new update here as well so the thing to learn here is That when you're working from a list which we are so this is a list this is not a table the pivot table is locking onto a specific range however if I were to insert a row or a column the range will expand for me it's automatically going to expand right so I

can go ahead and do that even take a look at my count blank function here it is now expanded to 99 as well so if you insert a record you Simply need to refresh the table but if you're adding a new record to the end of your data set you need to click on the pivot table analyze Tab and change the data source and just select all the data that you want to add as well right so go ahead and add a new record and then go ahead and change the data source to include that

new row and when you're done go ahead and insert a record anywhere in the middle and refresh your table just confirm that it Works for you and come right back now that we were able to add some data let's go ahead and insert a pivot chart chart to insert a pivot chart we can simply click on our pivot table click on the pivot table analyze Tab and then to the far right under Tools we'll see our pivot chart now the great thing about the pivot chart is is directly tied to our pivot table so we

simply need to choose the type of chart that we want to be displayed and we'll take a Look and see as we change the data that's being displayed on our pivot table our chart will d dynamically recreate itself so I'll insert my Pivot chart here now here is our insert chart dialogue so I have different charts that I can use LINE charts Pi Bar area so forth and so on some will not be available I'll keep it simple I'll just stick to the clustered column one here and let's see maybe I'll go for a little

3D clustered column go for this one I'll go ahead and press okay I'll close the pivot chart fields for now so I can see my chart here so here is my Pivot chart so we can see it's displaying whatever is currently on our pivot table we get some really nice filters here so what if I want to filter by let me move my slicers down here I want to show you what happens when we filter either using the chart or the pivot table or the slicers here so we Have three filters for our chart we

have status full-time part-time we have our departments and we have our division well I want to remove Texas and Florida from the division so I'll just go ahead and remove Texas and Florida I'll press okay right so there we go so we can see that now because we filtered our chart take a look at our pivot table our pivot table has also been filtered take a look at our slicer so our slicer has also been filtered so the one thing to note Is that these are all connected so our pivot tables connected to our chart

is connected to our slicers so they're all connected to each other so running a filter on one will filter the others as well if I click on the fulltime button here on my slicer we can see that the information on my chart has also changed as well as on my pivot table so we're good to go now when it comes to our pivot chart we can move it And we'll move it in just a little bit but let's take a look at just some basic customizations that we can make up here on the top right

we have the chart elements so we can turn some things off or on so we can add a chart title if we want to we can display the data labels at the top we have the data table so but what I like to do when it comes to a pivot chart is keep it simple just to choose one of the chart styles that are available so here are the Different styles that we can use here's style two if we scroll through here is style three this style has the data labels at the top of the

chart now we have different types here as well here's style number nine nice Dark theme and then we have 10 and then we also have 11 I'll go with 11 here as well and we're good to go so now what I can do is I can go Ahead and move this chart but before I do that let's go ahead and just kind of reconstruct our table here and let's see what happens to our chart well first I'll clear all of my filters here and let's reconstruct this so what if I don't want to show my

let's say go back to my pivot table what if I remove the department from The Columns so now as you can see I'm only showing the payer period for my divisions and we can see the chart is Reflecting that current cury here as well if I were to change this to department remove this here and there we go so now here's the sum of gross pay by department so as I move things around here I'll get some different answers as well for the pay period what if I want the hours the hours worked per department

and per division well there we go so we can see we don't need to do anything special to the Chart It's dynamically linked to our pivot table I'm going to move my chart here so if you click on pivot chart analyze we have a lot of things that we can use here here's the move chart command on the pivot chart analyze tab let's go ahead and move our chart we can also rightclick and use the move chart command here but let's move the chart to a new sheet and then we'll press Okay so here's the

chart on that new sheet all by itself now this is still linked to my pivot table right so right now I'll remove that department I don't want to show the tech I'll press okay so that looks a lot better right so I am filtering to remove the tech Department I'll go back to payroll here and you can see that we're still linked to our pivot table it's only Showing development research sales and staff but not Tech so even though we move our pivot chart it's still connected to our data set which is good because typically

you want your pivot chart to be on another sheet you don't want it to be on the same sheet as as your pivot table well that's it so go ahead and play around with your go ahead and move your pivot chart go ahead and turn some of these features on or off go ahead and choose a Style and come right back welcome back so we've covered all the basics with regards to a pivot table so you now have all the tools the essentials that you need to build a pivot table well let's take a look

at the X lookup function for our project for module one here or part one what we want to do is on the joining data sets worksheet here we have three different data sets we have a Products list we have a region list so sales rep and region and then we also have an order form over here as well but we can see that the order form is missing some information such as what is the region where the transaction took place what is the price of the actual product so rather than doing this manually we want

to write an xlookup statement that will automatically populate the region based on the sales rep Right so for example Dan Peterson is in the Midwest Division so we want to be able to ask Excel to put Midwest right in here for us the sunshine product over here is $19.95 we want to be able to put that price over here manually and Excel will fill out all the prices based on the product here as well so in order to do that we're going to use x lookup but before we do that we need to understand How

X lookup works so here here's a small product list over here we have the product ID the product name the quantity the amount that's in stock and then we have the actual price of the product we want to create a little kiosk here so that if we put in a product ID number let's say 105 what it's going to do it's going to go ahead and take this ID and it's going to go ahead and go down this column the product ID column Once it finds is a match that 1050 it's going to head on

over to the right find the product name and place that over here for us it's also going to find the price from the price column and drop in the price over here as well so basically we'll be able to enter a product ID and have Excel automatically populate column H and I with the corresponding product name And the corresponding product price so let's go ahead and do this together so I'll click on Cell H5 and I will insert my X lookup we'll use the function arguments box for this so I'll click on the formulas tab

here and on the formalist tab we're going to click on where it says insert function we'll get the dialog box and we will search for the X lookup function so we'll click here I'll type X Lookup I'll press go here's X lookup I'll go ahead and press okay so here's my function arguments box for X lookup and it tells me the name of the function tells me what it does so it searches a range for a match and Returns the corresponding items from a second range or array well that's exactly what we want to do

we have arguments here the lookup value is the value to search for the lookup array is the array arranged To search the return array is the array arranged to return I already have this kind of annotated for us here as well so basically in my words the lookup value is the unique ID so the unique ID is basically whatever value we enter in this cell that is the lookup value the unique ID column so the lookup array is going to be the unque ID column so this is where basically column A where we can find

our IDs the return array is going to be the Product name column and then we're good to go these other ones down here if not found these are optional we don't even need to enter anything here as well so let's go ahead and plug this in so my lookup value I'll point to cell G5 so whatever value is in G5 that is the look up notice it's already recognized that the value 1050 is in G5 so the next part the lookup array is where can we Find where can we find those product IDs well right

here column A I'm going to highlight all of those from A5 down to a21 now because this is a table it's giving us the table name and the table column so table one one product ID column here's the preview of the array here are the ID numbers 1050 1051 1052 so this lets us know we're locked onto the correct array two down one to go which column do we want to return Well we want to return the product name so I'll highlight this and that's it we're good to go the function in the background is

doing a lot of things that we're not privy to we simply need to point to the lookup value which again is G5 we need to point to the lookup array which is basically all of our product IDs and then we need to specify the return array which in this case is the Product name and so we are good to go change the color here right so pause the screen take a look if this is your first time using X lookup it does take a little pausing to kind of think logically what we're trying to accomplish

here but again the function does the work for us we can see it's already giving us our answer it is the Aspen which is correct so I'll go ahead and press Okay and there we go there is our Aspen product if I change the product ID to 1054 and press enter it gives me the doublers which is correct well let's go ahead and write our X lookup statement for the price and then we'll move on to our final project for part one here so I'll go ahead and insert my function here's X lookup lookup value

same thing it's going to be G5 the lookup Array the product ID column from table one the return array the only thing that's different I want to return the price so I'll highlight my prices here it's already giving us our answer $411.33 for our doublers with ID 1054 I'll go ahead and press okay and there we go so that is the X lookup function not as intimidating as it looks it does the work for us we simply need to identify the lookup value the lookup array and the return array I'll go ahead And test it

here I'll put 1066 now we can see this is correct it gives me the correct record here on row 21 very good go ahead and write your X lookup statements go ahead and enter a few product IDs just to make sure you're getting the correct answer and come right back welcome back now now for our final project what we want to do is we want to go ahead and make sure that this order Form has all the information that we need to create a pivot table to answer questions such as the sales by region sales

by product sales by sales rep and maybe even some sales by month or quarter or year as well so we have this is very common in the data world to have your data sets in different places so we have a product table here we have a region table here as well and then we have our order form so this is what we want to focus on we want to make sure That we add the region based on the sales rep this will allow us to run Regional reports we need the price of the actual product

so we'll grab the price based on the product and then we'll enter a simple calculation in column M for the sale we'll just multiply the units times the price and then this will be good to go now this is a really big data set if I press control end this goes down to row 10,000 and3 so this order form has 10,000 rows or 10,000 records I'll press control home to go back to cell A1 let's go ahead and plug this in so for the region we're going to enter X lookup now remember we already have

the information that we need we have our lookup value we have our lookup array and we have the return array so I'll highlight this first here so for our region this is our Target we want to return the region here to return the correct region we're going to use the Sales rep so in this case Isabel cross we're going to search the sales rep colum for Isabel cross so that's the lookup array and then once we're done what do we want to return we want to return the region from the region column so we already

have all that we need I'll make this a different color so here is our lookup value a bell cross here is Our lookup array and here is our return array so we just need to point to those different areas so let's go ahead and do that here now I have converted these to tables for you if you've never created the table before we'll go through the process of inserting a table but part two or module two so right now one of the benefits of working with a table is once I enter a formula or a

function in the top row it's automatically going to To fill down that formula for me so let's write our X lookup I'll insert X lookup here now remember lookup value we're going to take the sales rep where can we find the sales reps over here I'll just highlight them manually so two down one to go the next is what do we want to return we want to return the corresponding region from The table here so I'll go ahead and highlight all those as well and once I'm finished I can see that because I'm working with

the table Excel gives the table name so region table sales rep column region table region column here's a preview Alan Ramos Andrew Meer Midwest West East it's already telling us that Isabel cross is from the east region so as long as you have East there you should be good to go go ahead and press okay it Should automatically fill out all of the corresponding regions for us so let's go ahead and press okay and there we go well maybe we're skeptical let's check Cecilia Manning is Cecilia Manning from the south well let's take a look

and see where's Cecilia Manning here's Cecilia Manning indeed Cecilia is from the south so we're good to Go pretty cool now let's go ahead and grab the price from our product again we already have the data we need so let's enter our X lookup function lookup value is going to be the product where can we find the products right over here A4 to a15 AKA products table products column what do we want to return well we want to grab the Price so product table price colum and that's it 1995 for our sunshine and that is

correct I'll go ahead and press okay there we go everything is all correct here as well so now we have the region we have the price we simply need to enter a formula to calculate the sale so in this case I'm just going to say equal and it's going to be K looks like this is K4 so K4 times the value that is an M4 So that's what my formula is going to look like because it's a table it's not going to say K4 and M4 but we do have the target cells we want k4s

here and L4 okay so I'll click on Cell M4 here and I'll say equal and I'll click on the units here from cell K4 and I'll multiply with the asteris times the price right so units times the price I'll go ahead and press enter and there You go so just like that we have a fully functional order form now captured all the data that we need to create a pivot table so I'm just going to go ahead and insert a quick pivot table I'll click on the insert Tab and maybe this time I'll use a

recommended pivot table and this one says sum of sales by region I'll go ahead and grab that one I'll press okay here's my pivot table showing My sales by region as well again we'll just give this the accounting format and we're good to go so now we have sum of sale by region if you want to add the products in there we we can go ahead and do that well maybe not that's Overkill maybe I'll put the region into the columns and there we go so that is how that works go ahead and complete those

tasks and come back and uh we'll wrap Up that completes module one now we've learned all the essentials that we need to build and manage a pivot table module two will build on our learning and this module what we'll cover is we'll summarize a table with a pivot table so we'll convert a data list into a table and we will take a look at the tools available to convert that into a pivot table as well we'll take a look at some Advanced pivot table tools we'll also be able to customize Our pivot table by choosing

the design colors a lot of the Cosmetic aspects of the pivot table you can even brand your pivot table if you so desire we also are going to take a look at Power pivot so power pivot is a part of the Microsoft's Power Platform and this will allow us to connect multiple data sources and really work with big data so that's what we'll be covering once we upload our data into Power pivot we'll be able to create What's called measures and or calculated columns and we'll be able to create a cohesive pivot table from the

data model so go ahead and open up the module 2 practice file and come right back and we'll Dive Right In Here is our practice file for our second portion here and we have a couple of worksheet tabs here on the bottom well we have advanced tools which we'll be exploring we'll also take a look at pivot table design and then we'll go Ahead and introduce power pivot and then we also have some kind of a bonus we'll take a look at how to perform some data mining to improve our reporting capabilities on our very

first worksheet tab here what we want to do we have a list here so just our Pure creary sales we have the order ID the sale date we have the product sales region we're tracking the cells and the units as well so right now we have three cells Representatives we have Pullen Watson and Bishop we're selling in three different regions so Central north and west as well several products popsicles ice cream tasty treats frozen yogurt so we want to keep it simple that's why we using crey items here so let's go ahead and convert this

this list to a table and then insert a pivot table so remember we can convert this list into a table by pressing control+ t on our keyboard so that will convert our list Into a table so I'll go ahead and do that you don't have to highlight the entire list let's scroll down let's see how big this list is I'll press control end this goes down to row 112 so not that bad I'll press control home to go back to cell A1 and here we go so if I just press contrl T here's my create

table dialogue box and we can see it's already selected all the data for me A4 to g112 and that's because I don't have any blank rows or Columns in here my table does have headers so I'll go ahead and press okay so here is my table now I know that this is a table because a lot of things have happened here if you take a look at the ribbon we have what's called table design and the whole reason we're converting this into a table is because when you convert your list into a table you have

more opportunities to manage your data for example we have table Styles we have Filters that have been turned on we can also add the total row we can go ahead and insert a slicer as well we can remove duplicates and then another big thing that we can do is we can go ahead and summarize this table with a pivot table so just a quick rundown on what a table is let's go ahead and click on the table design and let's go ahead and click on the option that says summarize with a pivot table I'll go

ahead and click Here and just like before when we're inserting a table we have the ability to insert a pivot table here well I do want to add this to a new worksheet so I'll go ahead and press okay and there you go so we have our pivot table all of our fields are here for us and we're good to go so we can go ahead and start building this table now remember we want to take a look at some Advanced tools such as calculated Fields using the timeline drill down reports And Report pages so

I'm just going to copy this data over here and I'll put it far to the right maybe over here and these are the different tools that we'll be exploring here let's go ahead and some of the basics just to kind of recap I'll click on pivot table options I want to rename my table and I'll just call this one creary sales while I'm here I'll click on the display Tab and I'll go ahead and sort my field list a toz make sure they're sorted in alphabetical order so I'll go ahead and do that as well

and I'll go ahead and press okay remember you can also change the view from stacked versus side by side I'll leave it on side by side here well I'll go ahead and build out a pivot table so I'll go ahead and grab these sales I'll go ahead and apply the accounting format as we've learned Before number format accounting okay and then okay so here are some of sales going to zoom in just a little bit here and now what about sales by product put the product in the rows now put the region in the columns

and just like that we have a really nice table that we can interact with well what if I add some data to the original data source going to go back to my original data source here but let me rename this worksheet First so I'll call this crey sales and let's go back to the advanced tools now what I'm going to do is at the very end I'll press control end here and I'm going to add another transaction so I'll say 1109 and I'll go with the same date why not and what I want to do

is introduce another product so I'll introduce another product let's say I'll go for organic yogurt I add my name in Here and I'll introduce another region as well so I'll go for the East and for the units I'll go with 25,000 we already have the formula in column F so here is my new record on row 113 now what I want to do is go back to the pivot table we can clearly see that region has not been added and we don't have our new item here well here's the benefit of working with a table

I don't have to click on pivot table analyze and click on change Data source and then select all my data again well all I need to do is simply right click on my pivot table and we'll notice the refresh icon right here so I'll click on refresh and just like that my pivot table updates now this only works because I'm working from a table so here's my new region and here is my new product as well so this works because the nature of the table the table automatically Expands when you add data to it it

continues the formatting from above we get some nice filters in here as well and we can see our headers as we're scrolling through our document no need to freeze that particular row go ahead and convert your list into a table go ahead and insert a pivot table add another record to the original data set and refresh your pivot table and come right back welcome back let's go ahead and Take a look at one of the advanced tools which is a calculated field so what I want to do I'm just going to go ahead and reset

my table here and all I want to focus on are my sales reps so I'll add my sales reps to the rows and the sum of s and the values as well so maybe we want to go ahead and calculate the commission based on the sales here so let's say we're giving out commission and the commission is at 25% not bad so what can I do to be able To calculate the commission well I don't have a field over here that's called commission if I go back to my original data set I don't have a

field called commission so I have two options I can either come back here to my original data set add a field call commission go ahead and insert my calculation fill down my formula and then go back to my pivot table and then refresh the data well those are a lot of steps and a lot of Times you don't want to really modify the original data one of the benefits of working with a pivot table is the changes that you make on the pivot table they do not impact the original data the original data stays intact

and so we want to avoid having to come here and add another field so to remediate that what we're going to do is create what's called a calculated field going to click on pivot table analyze and on pivot table analyze over In the calculations command group if I click on the drop down I'll see that we have what's called a calculated field now once I click on the calculated field I'll be presented with a dialogue box I simply need to enter the name and then the formula and once I create that field I can add

it over here as a field that I can use pretty much anywhere in my pivot table it's also going to be displayed right here and all of my Values are going to be displayed here as well so let's take a look at how that works I'll click on pivot table analyze here is Fields items and sets I'll click on calculated field and as expected we're presented with this dialogue box I need to go ahead and add a field name well I want to call this field Commission because that's exactly what I'll be calculating here now

the formula we can only enter simple formulas in here we Cannot refer to a cell reference like F1 in this case if you click on the question mark here this little help button this will open up a Microsoft page for you indicating the types of formulas and functions that can be used here but I just want to keep it simple so what I'm going to do is my formula is going to equal the sum of sales times the commission at 25% so what I can do is I'll grab the Sales here I'm going to insert

that field into the formula and I'll enter my asterisk so equals sales times and I'll enter 25% just like that so the name of the field is going to be commission the formula is equal to the sales time 25% what I'll do is I'll click on ADD add and I'll see that this will be added to my field list here and then I'll go ahead and press okay and then we'll see it populated over here as well quickly Giving us the commission for each of our sales reps so I'll click on ADD and here is

my commission field now then I'll go ahead and click on okay and there you go so we get those two things we have the commission field we can use over and over and here we have answered our question we can see that because Watson had the highest sales Watson is getting the highest commission at 100, 1412 as well so that's the benefit of working with a Calculated field well now what's really nice is well what if I want to calculate the commission not just by sales rep what about the commission that would paid out based

on a particular product well look what I can do I'm going to go ahead and move things around here so I'll go ahead and click on the product and I'll go ahead and remove the sales rep and look at that detail that we're given so when you create a calculated Field we can use it and multiple instances in our pivot table as well finally I'll do one for the product let's see what we have to out based on our region so I'll click on region going to remove move my product and here we go so

now we can see the commissions that were paid out 75,000 commission was paid out in the central region so go ahead and insert a calculated Field go ahead and add it to your pivot table fields and go ahead and use it and get the commission by product sales rep and by region and come right back in this mini lesson we'll learn about the timeline I'll go and quickly construct my table here I just want the region and the columns and I want the product and the rows so here's my simple table I can see all

of my products and all of my regions here's my grand total well a lot of the information that we Analyze it is based on time and a lot of time we want something like a quarterly report or a yearly report or even a month monthly report so we do have a properly formatted date field in here which is the sale date so I can go ahead and drag the sale date to one of these boxes or I can just click on it but uh it's going to just kind of place dates in random places here

so what I prefer to do is to Use the actual timeline to use a timeline we'll click on pivot table analyze and right next to our could friend the slicer we can see that we have the timeline now this is why it's very important to make sure that you have a properly formatted date field if we do not have a properly formatted date field we will not be able to insert a timeline well let's get some reports here I'll click on insert timeline and as you can see it found That we have a matching date

field here for the sale date I'll go ahead and click on sale dates I'll go ahead and click on okay and here is my timeline so just like the slicer it comes with its own contextual tab so we can change the color I'll go ahead and just give it a little contrast here I'll give it this orange color there we go so here is my timeline the way that the timeline works it's Basically a filter just like a slicer but we're working with a date higher key so the first thing to note on the top

right of our timeline we'll notice right here if we click on this dropdown we can either look at our data from a year level quarterly level months or even days as well well I'll click on years and it looks like we had transactions in 2022 and in 2023 so if I were to ask well what were our sales for 20 2022 if I click here in 2022 I can see that we had sales of 519,000 378 notice how all of these numbers have changed because we're filtering just for 2022 so when I click on 2023

all of these numbers should update as well I'll click on 2023 and we can see that we have a different set of numbers here for 202 three so that's looking at it at a year level let's go ahead and take a look at It at a quarterly level so because we have two years we have eight quarters that are available so now we can be very specific what were our q1 sales in 2023 that is pretty cool so no need to write formulas the pivot table is taking care of all that for us here are

our q1 numbers for 2023 now I can also expand this timeline so what about the first half of 2023 notice when I move my cursor Towards the end of this little time capsule I can drag it to the right so now I'm looking at q1 to Q2 of 2023 here is my grand total so pretty pretty nice as well what about Q4 for 2022 there we go now let's change this to months so I'll click on months here and we have a lot of months to work with so we need to kind of expand our

timeline here and let's say I just want to focus on 2022 well what about January of 2022 look at that in January of 2022 we had sales of 68 to 44 February went down march went down April ail we're kind of going down here June pick back up it's the summer time everybody wants tasty treats here and so we can see how that works it even goes down to the days as well so this is very granular so we can go down To specific date June 23rd of 2022 look at that so pretty cool I'll

go ahead and clear the filter from my timeline and so that is the timeline a really really nice tool to help you to have more control over time-based data analysis sometimes when you're taking a look at your data you may want to just kind of get more detailed in terms of these numbers for example we can see that in terms of products we can see That ice cream had the highest sales 445,000 $80.50 here as well well what if we wanted to you know kind of use ice cream the ice cream sales as kind of

the standard so in other words how can we improve our sales for frozen yogurt popsicles and tasty treats and so forth and so on well what we can do is we can create a separate report for ice cream and all of the transactions that refer to ice cream will be included in that Report well we don't have to do anything special we don't have to build it I'm just going to go ahead and double click on this number and when I double click on this number let's see what happens well it's going to create a

worksheet for us with all of our details concerning ice cream so I'll go ahead and double click on this number and as you can see it has all the transactions here so these are all of my transactions regarding ice cream Here's the dates in here so we can see so this is a pretty pretty nice tool because we have all the detail so now we can break away from the pivot table and we can focus on maybe this will be the data for discussion for this portion as well so we have everything in here so

now we can answer the question how did we arrive at this 445.6 K here's the breakdown and the pivot table gave this to us we did not have to build this manually now if you Wanted to go ahead and run some filters on here you know we can filter by date because it inserted this data in table form and so we're good to go so maybe we can go ahead and do some sorting and so forth and so on so that is just being able to kind of drill down or drill through our actual report

so a nice little tool that allows us to do that we can double click on any of these numbers and we'll get that particular detail here as well for Frozen yogurt in the central I'll double click here and as you can see only frozen yogurt in the central region so pretty nice okay let's go back here to our creary sales the next thing we'll talk about is what's called report page filters so basically what I want to do is I want to go ahead and kind of create another pivot table based on this table and

specifically what I would like is I would like to have different pivot Tables one pivot table on a sheet that's for the central region another pivot table on another sheet for the North and of course one for the East and the West as well right so so they will behave independently and this gives me the capability this way I don't have to try and copy and paste the pivot table somewhere else I can just create individual report filter Pages for these here as well so how does that work well the first thing we need to

do is we need To go ahead and introduce a field into the filters so the first question is well what do we want to create the reports based on what well we want reports based on the region so I want Regional reports I'll go ahead and drag the region into the filters box here and now that I've dragged the region into the filters box let's go ahead and click on the pivot table analyze tab let's go ahead and find out where we can insert these reports all The way to the far left we have the

pivot table command group if I click on the drop down I can see that I have show report filter pages are available here right so step one we need to go ahead and introduce a field into the filter box step two we need to go ahead and click on the pivot table analyze Tab and step three we need to go ahead and actually show the report filter page so I'll go ahead and do that I'll click on The drop down here I'll click on show report filter pages and look what it's looking for it's looking

to see what field we have in the filters area if we did not have a field in here we would not be able to show a report filter page so that's why the first step is adding one of these fields into that filter I'll go ahead and press okay and let's see what happens we should get four new worksheets each one for east west north And south let's go ahead and press okay and here we go so here's the north well we'll start central so here's the report page for Central North West and East as

well so these are behaving autonomously so if I make any changes here it does not affect the original pivot table so I can go ahead and just focus on the Central here and I can go ahead and add my timeline if I Want to analyze the data here just for the central if I head on over to the West I can do the same thing I can kind of just you know move these around here so maybe I just want to go ahead and uh let's see maybe I'll put the sales person here in the

columns so we can go ahead and reconstruct these individual tables in any manner that we want so those are the report filter Pages we have one one for each of the region just be careful when You're creating these report Pages this works well because I only had four regions if I had 20 regions we would have 20 worksheet tabs to work with so just remember not every field is a good candidate for a Slicer in the same way not every field is a good candidate for a report filter page now when you're working with your

pivot table you do have the capability to customize the design and in this lesson what we'll do we'll take a look At how we can change the report layout we'll customize how the sub totals and the grand totals are displayed and we'll go ahead and just a quick demo on how we can create our own pivot table design if I click on my pivot table here these can all be found on the design tab so I click on my pivot table here's the pivot table design we can always choose a quick pivot table style let's

see what style are available so we have light Styles we can Also hover over each one we can see how it's impacting our pivot table we have medium Styles where we can see that we have some more contrasting colors in here especially for the headers and those subtotal rows if you scroll down we also have dark tables so it really depends on what you are looking for now if we take a look right now now what if none of these tables are working for us we want to go Ahead and create our own pivot table

once we do that we'll see another section up here at the top and this will display any custom pivot tables designs that we have created we can also make that the default as well but for now let's go ahead and take a look at the different layouts so by default we're using the compact form I'll go ahead and add some more detail in here so I'll show my field list and I want at least two Fields and one of these rows so maybe I'll go for let's see I'll put the product in the rows there

we go okay so here we can see this is the compact form I'll go ahead and change the report layout to outline form and then we'll take a look at tabular form and just go ahead and choose whichever one works best for you so here's compact we're just compacting as much data as we Can here's the outline form and we can see the outline form kind of just gives us a little more space to work with if we take a look we can catch the change basically what it did is it pushed our products over

into its own column it's no longer sharing the same column as our sales person here so if I go back to compact we can see it's going to push it over to column B and there we go so this looks pretty Good let's take a look at tabular form so tabular form looks really really nice and if you're showing this for the first time you may want to use table to form looks a little more simple to kind of understand what's going on here as well so those are the three different layouts that we can

choose so again here's compact here is outline and then here is the tabular form which more mostly represents an Excel table as well now we have some other options down here on the bottom such as repeat all item labels so basically what this does this fills out the blank space here and this is helpful if you have a really large you know a lot of data being displayed here and you may want to just add that little detail here so everyone can kind of read the data from left to right but I can say do

not repeat all item labels we're good to go now when you're displaying your pivot Table there's a lot of numbers here a lot of grand totals we have a lot of subtotals in here as well and this could be a little overwhelming for your audience so you can determine which one of these you want to turn off what you want want to leave on so we can turn off these subtotals these Grand totals let's just quickly take a look at some of the options that are available so for subtotals if I say do not show

subtotals Let's see what happens so we can see we don't have that subtotal row down here at the bottom for each of our sales rep so we only have the grand total column on the right and the grand total row on the bottom here as well if I go back to subtotals I can say show all subtotals at the bottom of the group so it shows down here here's Bishop Bishop gets a subtotal or I can say show it at the top Of the group now depending on the layout that you're using this will not

matter if I change this to compact we can see it showing at the top here I'll go back to tab form and so that's it so I'll go ahead and show the subtotals at the bottom of the group then we have grand totals so the same thing we can turn off our grand totals and so we can see we no longer have our grand total column we no longer have our grand total row so this just Gives you a little more flexibility in terms of what you want to display maybe you just want to focus

on the subtotals for now and not so much the grand totals so we do have those options here as well the other option is Grand totals on for rows and columns which is the default if I only wanted to be on for rows then here is the grand totals summarizing our rows over here as well and as expected we also have one on for columns Only which shows the grand total at the bottom summarizing our Central north and west regions here as well so those are just some of the different ways that we can kind

of improve the experience for our audience go ahead and toggle some of these options see if you can catch the change let's come right back and we'll actually go ahead and create our own pivot table style let's go ahead and create our own pivot table design so I'll go ahead and Click on the design tab here and I can go ahead and change it maybe I'll go ahead and change it to a medium style table so I'll go for this one here so light blue pivot style medium 6 so I can go ahead and choose

one from here if I want to or maybe I'm scrolling and I just not finding the one that I like here so what I can actually do is I can go ahead and create a new pivot table style maybe you want to go ahead and brand your pivot table to your Company colors or so forth and so on keep in mind that when we're building the table the colors that are available is based on the current theme that we have selected so for example if I click on the page layout tab here and I head

on over to the left I can either choose an existing theme the same themes that are available in PowerPoint and word or I can go ahead and choose my own colors I can go ahead and choose my own fonts here as well We'll keep it simple I'm just going to go ahead and take a look at some of the themes that are available here so by default we're all using the office theme unless you change it to facet or gallery and we can see that our entire Excel document is being updated the colors the fonts

and the effects are immediately being updated or changed we have access to different fonts and different colors here as well so here are just some different ones so here's Droplet so if you want access to different colors to work with go ahead and change change the theme or you can select your own color palette you can also go ahead and select your own fonts as well well I'll keep it simple I'll go with the basic office theme here I'll click on the design tab I'll click on this drop down and I'll go ahead and create

a new pivot table style now we'll just cover the basics here this will take a very long time to actually go Through this so I'll go ahead and call this one pivot table Style I'll call this pivot table custom one so the way that this works is you give it a name and then over here on the right we have a preview in terms of what the table will look like so we can see once we add the formatting to the header Row the first column the total row so forth and so on when you're

formatting the table you Need to choose what part of the table you want to form format so there's a lot of parts here the whole table report filter labels First Column stripe header row First Column so we can interact with all of these different elements here subtotal rows column subheadings grand total column so I'll go ahead and format the whole table the First Column the header row and then maybe the grand total row right so let's see so I'll start off With the whole table I'll click on the format tab here notice before you start

this or before you finish you can also click down here on the bottom to lock this in so that every time you create a pivot table this will be the default pivot table style I'll go ahead and click on format for the whole table and just like when you're creating a table design we have this simple dialogue box where you can choose the um How you want this to interact here as well so I'll go ahead and click on the fill color right so the fill color for my entire table let's say I'll go with

a light gray and I'll go ahead and press okay and I can see here it's already has that gray color I'll click back on the format tab I'll go to the font and for the color maybe I'll go with a dark blue I'll go ahead and press oh maybe I want the borders as Well I'll go ahead and add a border I'll say I want an outline border and I'll choose a solid line and I want the color to also be that dark blue so I'll go ahead and press okay and there we go so

I'll go ahead and format the header row now I'll just do the opposite here for the header Row the font is going to be gray this time I do want it to be bold I want it to stand Out and for the fill color it's going to be that dark blue so I'll go ahead and press okay as you can see up here here's the header row has the gray text on a dark blue background My First Column we go to to My First Column here I'll also go ahead and format that and I think

for this one I just want to make the text bold right so I'll make the text bold for the First Column I'll head down here to The grand total column I'll format that I'll give that a double underline and I'll also make that [Music] bold and maybe the fill color I'll give it a different shade of blue here maybe that shade there we go so we have that and then finally the grand total row so as you can see it's a lot of formatting in here again I just want to keep it simple I'll click

on the format tab for the grand total row and for the fill color I'll give it a simple fill here for the font I'll also give that double and I'll make it bold I'll go ahead and press okay so this is kind of what my table is going to look like here as well well I'll go ahead and press okay and let's give it a test and let's see what that looks like so I'll press okay so now how do we find that design if I click on the more drop Down here at the very

top in the custom area I can see that I have my custom table well let's go ahead and apply that to style here and there we go I could make a few Corrections maybe I want the grand total row here to kind of mimic the header row but doesn't look that bad right if we need to make any modifications to it we can just rightclick on the style on the design tab we can click on modify this brings Us right back to that box and this gives us the capability to make any modifications that we

need as well again just a quick reminder it's based on the theme and the colors that you're currently using so if you want to go ahead and choose change the theme and then go ahead and design your own pivot table let's head on over to the power pivot setup worksheet Tab and power pivot is not turned on by default we Need to go ahead and turn it on once we have it turned on we'll have an additional tab here on our ribbon that we can use this is what it would look like we can introduce

data into the data model we can create measures kpis we can detect relationships so we want to go ahead and turn that on we need to get to the Excel options here and we need to click on customize the ribbon on the left Pane and we simply just need to go ahead and Turn on power pivot the same way that we will turn on the developer tab going to go ahead and right click on any one of my tabs here I'll right click on the Home tab I'll click on the customize the ribbon option here

and this brings me right to the area where I need to be here's customize the ribbon over here on the far right here are my main tabs I'll scroll down until I find power pivot so here is power pivot I'll go ahead and select it then I'll go Ahead and press okay once I do that here is my power pivot on my ribbon and now I can go ahead and add my Excel tables to the data model and go ahead and create the relationships and do what I need to do to make a pivot table

as well now if you're not seeing the power pivot over here you can go back to the customize the ribbon and under add-ins you want to go ahead and search to see if the power Pivot adom is available so what you want to do is click on the down here under addin it's a Comm addin so go ahead and click on the drop down here change it to a Comm addin and then go ahead and press go so Comm addin and then press go we'll be presented with another dialogue box that will allow us to

add power pivot to Excel for Windows so I'll go ahead and click on go and here we can see we have Microsoft Power pivot for Excel so just go ahead and press okay and once you do that you should have access to power pivot I already have it I'll go ahead and press cancel and I'm good to go go ahead and add power pivot and come right back we'll start working with our power pivot data now here we can see we're working with a data set now it's very common in the business world or the

data World rather that we have our data in different Places so for example we can see that we have a product list here with have several products including the actual product prices we also have another data set here so this is our sales rep list and we have all of our sales reps and we have their corresponding regions and then finally we have our order form so this is the form that's kind of tracking the orders for example On August 31st Christy Olen sold two units of the doublers and so we want to go ahead

and figure out well what were the total sale so right now we want to focus on our order form we want to make sure that this has all the data that we we need and then we're going to go ahead and create a pivot table from this or order form here as well so it would be nice if we can add the region based on the sales rep so we can add the region here and We're missing the product prices so if we can add the product prices in here as well then we'll have the

capability to basically Run Regional reports and all of our prices in here as well finally we need to go ahead and create another column here so that we can grab the total sale right so this is going to be our sale well how can we do this there's several ways that we can do this but let's go ahead and use power pivot which In my opinion is the most efficient way to accomplish these tasks the first thing we need to do we need to go ahead and convert each of these lists to a table so

I'll go ahead and just press contrl T then I'll press okay and I want to go ahead and name these tables accordingly because once I upload these tables into Power pivot when I'm writing my formulas I'm going to be referring to the table by name so I want to make sure I don't have any spaces that it's just one word and that it accurately describes the table so right up here I'm want to go ahead and call this table table products or I could say TBL products I'll just call this table products I'll go ahead

and press enter one down two to go I'll go to my salesperson table here I'll press control T they're the 73 records I'll press Okay and I'll call this one table salesperson [Music] there we go and then finally for our orders I'll go ahead and press contrl T here as you can see we have 10,000 rows of data I'll go ahead and press okay and I'll call this one table orders so that's it so we have our three tables let's go ahead and click on power pivot so we have each table they're Named accordingly let's

go ahead and add each these to the data model so if we hover over it it says add an Excel table from the worksheet to the data model let's go ahead and click on our first table let's click on add to data model it's going to open up power pivot for us and add the details of that table into Power pivot so here is power pivot we can see it's open here on power pivot we have the Home tab design and we have advanced I'll go back to the Home tab here notice on the very

bottom here our first tab is our products table so I'll just go ahead and minimize this I don't want to close it I'll just minimize it I'll go back to my Excel I'll click on my second table here and I'll click on add that to the data model and so as you can see we now have our products table and our salesperson table included in the model here I'll go ahead and add the third Table so I'll add this to the data [Music] model and there we go so we have products table salesperson table and

then we have our order table here as well go ahead and add those to the data model and come right back now that we have our three tables into the power pivot for Excel data model we want to go ahead and establish relationships between these three tables Just a quick trip here we're on the Home tab in power pivot and we have a couple of command groups as expected we have our clipboard command group we have the capability to get external data so this is the benefit of one of the major benefits of power pivot

we can pull in data from several different sources as long as they're related to each other we can do that so we can actually pull in from a SQL server or an access database different services that we can Add here as well we have other sources so lots of different places that we can get data from as well while we're in here we can format columns just by clicking on one of these tools here now unlike Excel we just format a particular cell you can format an entire column with these different types of data here

as well we have sorting and filtering then we have calculations but what we want to do we want to create relationships between these three tables Once we create relationships we can pretty much return any field that we want to from any of those three tables so the first thing we want to do from the Home tab we want to go ahead and switch over to diagram view this will show us how three tables and we'll be able to create the relationships and connect them together so that they can all talk to each other so I'll

click on diagram view here and for those of you who have used Microsoft Access this looks pretty familiar here are our three tables here's the products table our two Fields salesperson table our two fields and then our orders table so I'll just go ahead and move my order table this has no bearing on the data this is just to be able to focus on this table here so here's my orders table so now the question is well if we take a look at my orders table and my products table what do they have in common

so what field do They have in common well we can see that it's the products they both have a field that's called products so what I'm going to do to create this relationship I'm going to go ahead and drag the products from my products table right to the products field on my orders table now I can go either way I can go this way I'll go ahead and drag it and connect it to the products here when I do that I can see what's called a join line so here's my join Line my join line

has a on to many relationship so this is telling me that we only have one let's say the doublers product it only appears once on this list but because this is our order form this appears many times on this list so you'll see this a lot mostly you'll have a one to many relationship and our second example here so what does the salesperson table have in connection With our orders table well the connection here is the sales rep right so we can find many instances of our sales reps on the order table but just one

unique value on our salesperson table so I'll go ahead and drag the sales rep right to the sales rep here and there we go so here are our two relationships we'll see why this is important in just a little bit but for now go ahead and create those relationships when we come back we'll See how we can use a Dax expression called Rel ated to basically borrow the region from the salesperson table and also borrow the product price from the products table we have three tables and we have established the relationship between our tables and

our order table I'm going to click back on the data view so I'll click back here now I can see the data for my order table here's my salesperson table and here is my Products table well I want to focus on my order table so I'll click here now what I want to do we're missing the region so I want to add the region based on our sales rep so I'll go ahead and click on the first empty cell here in that blank column and just like in Excel we have a formula bar right here

well I'm going to enter a what's called a Dax expression or a a data analysis expression so that I can pull in the corresponding region for each of our Sales rep so I'll type equal and I'll use the related now this is one of the nice things about using the Dax expression it uses common language and we get some help along the way so I just started typing the phrase related and as you can see related here it returns a related value from another table if I go down to related table this Returns the related

tables filtered so that it only includes the related Rows well I just want related so I'll go ahead and double click there and for the first argument is asking me because I created relationships between these tables I can either pull the price from the products table the product from the product table well what I want is the region from the salesperson so I'll go ahead and double click here and then I'll go ahead and close my parentheses so basically I'm using the Related function what's available I can return the region from the salesperson table based

on our current sales rep once I press enter it's going to populate all the regions here based on each sales reps region so I'll go ahead and press enter and there you go so Christy Olen is from the West Dan Peters is from the Midwest mayel Lindsay is also from the West Isabelle cross is from the East I'll go ahead and rename the column so I'll double click on it and I'll just call this region good to go next I'm missing the product price so I want to go ahead and return the price based on

the product here so I'll click on the first empty cell on the new column I'll click equal and again I'll use the related function now I want the price from the products Table I'll go ahead and close my parenthesis and press enter and it populates all of my prices for me my doublers Are $79.95 My Sunshine product is1 19.95 so forth and so on I'll go ahead and double click on this column and I'll go ahead and call this I'll just call this price and press enter and I'm good to go now one of the

nice things with power pivot here and the other power tools like power query We can move these columns so if I want to go ahead and move the region right next to the sales rep I can go ahead and do that can't do that in Excel what have to cut it insert it couple of different steps there as well for the product price I'll move that as well over to the get right after the actual product so there we go sales rep region product price there's one thing left to do we're going to go ahead

and enter a formula Here we want to calculate the actual sale now we'll keep it simple we won't add the discount in here here we just want to go ahead and multiply the price times the units it's going to be the price times the unit sold and that's going to go ahead and give us our answer so for this one when you're writing calculations and power pivot we are summarizing columns not just cells so when I write my formula here it's going To go ahead and summarize all of that for me so I'll click on

the formula bar I'll say equal and just like in Excel I'll click on that price and notice how it puts in the column name for me here and I'll click on the asterisk and I'll click on units right so let's multiply the price column times the unit column I'll go ahead and press Enter and as expected I now have my sale for each of my transactions here as well I'll go ahead and double click on that column I'll rename it to sale and I'll go ahead and press enter and there we go so we were

able to write three Dax Expressions to add three columns to our data set and we can see it's summarizing the columns for us we don't have to go step by step there's no need to autofill down and we're good to go here as well if I wanted to I can go ahead and apply The currency format to the sale here also to the price so just some really nice cleanup opportunities that we have in here that we can use if I click on design we have some more options in here we can freeze a column

right here's some more relationships we can manage the relationships if we want to break a relationship we can do that a lot of calculation options that we can use There's a lot in here that we can use but right now mission accomplished we have pretty much rigged up our order table so that we can go ahead and create a pivot table from here so the final step is to go ahead and click on the pivot table Command right here this is going to insert our pivot table into our current Excel document and now we're able

to combine all three of those tables into one and make a cohesive pivot table where we have some nice Reporting as well so I'll go ahead and click on pivot table here it's asking me where I want to place it I'll place it on a new worksheet I'll go ahead and press okay here's my pivot table I'll turn on my pivot table fields and here we go so notice how it pulls in all those tables for us but I'm only concerned about the orders table so now I can go ahead and build my pivot table

here based on Region right based on product and I am good to go so a really nice way to work with our pivot table so we've inserted our pivot table using power pivot now a natural question is well what if we were to add some data to our original data set so what if I go back to my power pivot data here and what if I were to change the prices here so let's say I'm just going to go ahead and double the prices here when I go back to my pivot Table what will I

need to do do I need to go back into Power pivot again and make the changes all over again or will all those steps that I created and power pivot will it run through those steps and update my pivot table well let's go ahead and take a look let's go ahead and record our grand total here so right now our grand total is 915 787 50 so I'll go back to my power pivot Data and let's just change some of the prices here maybe the Aspen is double to 42 maybe the quad is double to

68 maybe the sunbell it's now 50 and maybe the vrang is now 38 right so I just made some updates to the original data set I'm going to go back to my pivot table here and let's right click on it and let's refresh and let's see what happens look at that everything has been updated because I made those changes for the product prices we did not see it but basically what happened was it started from the very very beginning it opened up power pivot looked at the data model each table was refreshed to check to

see if any data was updated in that case it was and once it caught the changes that were made to my products list it recalculated the Sales in the sales column for me and provided the new information here as well and the best thing about all of that the original data set no matter what we do to our pivot table here or in power pivot the original data stays intact right but if we change it manually here we simp simply just need to refresh the table we can get some realtime results here as well so

that's another major benefit of working with the Power Platform so go ahead and go Back to the original data set go ahead and change some of the prices if you want to go ahead and even add another transaction in here or maybe edit one of these transactions or add a new transaction or two or three and simply go back to your pivot table refresh it and go ahead and locate the change for our data mining worksheet here well this sheet is dedicated to just giving us some tips and tools that we can use to improve

the reporting on a particular Data set so originally we had the first name and the last name in two separate columns so what we did was we realized it would be better would be more efficient if we had the rep's full name in one field here or one header we also had the city and the state in one column well that doesn't help us much so if we can extract the city and the state now we have more reporting capabilities such as being able to report based on city also report based on state we don't

have To worry about the date as long as they're properly formatted we can use the timeline to get quarterly reports and so forth and so on so to do this we're going to be using a tool called [Music] flashfill and flashfill basically looks to the left so it looks left and it tries to find a pattern once it finds the pattern it's going to offer Suggestions so for example I'll go ahead on click on the rep full name here so I'll go ahead and type maybe I'll do this maybe I'll say Olsen comma space Cara

I'll go ahead and press enter now let's see what happens when I start typing Anthony's last name so TS look at that so as soon as I start typing the last name it already looked to the left it already sens the pattern so in this case the pattern it's Saying it looks like you want the entire string value that's in column C you want to add a comma and a space and the full string value that is in column B well this looks pretty good I'll go ahead and press enter I'll go ahead and expand

my column here and maybe I'm skeptical I'll go go to the bottom here and I can see it all looks good and that's Silva Jerry rera and I'm good to go so Flash Fill will either run automatically for you once Senses a pattern if you want to run Flash Fill manually enter a few examples and then click on the data Tab and over in the data tab in the in the data tools command group we have Flash Fill little lightning bolt going through a table my favorite tool in all of excel I've Ed it in

so many instances to clean up data let's go ahead and run Flash Fill here let's go ahead and extract the state from column G um well the city sorry so this is Boulder I'll go ahead and press enter now on the next Blank cell I'll go ahead and run Flash Fill notice you can also run Flash Fill by pressing control+ e well I'll go ahead and run Flash Fill here and I can see it's extracting everything to the left of the comma Now what's really nice about Flash Fill is also intuitive sometimes maybe it only

puts bowling here instead of Bowling Green well if I corrected this and added Bowling Green in here this will kind of retrain Flash Fill and it will look for any city that has two names for example San Antonio and it will make the correction for me so it is intuitive last one I'll go ahead and extract the state So Co for Colorado this time I can either start typing Oregon or I can press contr e to run flashfill or I can run Flash Fill manually here as well now we're good to Go as soon as

you run Flash Fill you'll get the Flash Fill options here you can undo Flash Fill if it did not find the pattern correctly or you can accept these suggestions you can go ahead and select all 41 of those change cells as well I'll just accept these suggestions and I am good to go if Flash Fill does not work right away just keep on entering a few more examples until it finds the pattern and offers you the suggestions as well either way we were Able to improve this report and now we can go ahead and get

some reports based on city state rep's full name and use the timeline as well I'll go ahead and insert a quick pivot table from here I'll use the quick analysis tool I can either right click and right in that search menu here at the top it's one of the newer features I'll go ahead and type pivot table if I can click on that box here we go and here we have some recommended Pivot tables we also have summarize with a pivot table but let's see if I can find quick analysis here we go here's the

analyze data if I click here opens up my analyze data pane over here on the right and now what I can do is ask questions about my data I can also go ahead and insert one of the these pivot tables so this has sale by order city state and city uh what else do we have here sale By state here's a pivot chart that we can insert I'll go ahead and insert this pivot chart actually there we go so here's the pivot table and we also have the pivot chart we'll give that a dark theme

and and there we go so all set to go congrats on completing the course as a recap we're able to prepare data for pivot table analysis we're able to insert and manage a pivot table we're able to insert and Manage a pivot chart and then we're able to connect data sources with the popular X lookup function we're able to summarize a table with a pivot table we're able to customize our pivot table design we're able to use power pivot to connect different data sources then we're able to use power pivot to create measures with Dax

Expressions as well so congrats on completing the course and as always I Look forward to seeing all of you in a future learned course hello everyone and welcome my name is Joe and I'm going to be facilitating this course for you today today we're talking about co-pilot from Microsoft Excel now co-pilot has been a major topic lately and it's something that's going to change the way that we work with these applications in order for us to understand co-pilot for Microsoft Excel we have to understand What co-pilot is co-pilot is a powerful addin tool for Microsoft

Excel that allows users to automate repetitive tasks improve efficiency and also save time now it utilizes machine learning and AI technology to analyze your workflow and then suggest code Snippets based on your specific needs and with copilot you can automate data manipulation formatting calculations and so much more without actually needing extensive programming Knowledge now a couple things with co-pilot is the way that it comes out with outputs an input is when you ask it a question and then the output will be the result or the answer to that question and the way that it randomizes

this is known as stochastic process now stochastic process in AI models can lead to slight different results each time the model is run so even though we put in the same input right I can say hey how much does this equal out to my total Sales it might not be word for word the same exact output and this is often what is desired right we often want r Randomness so that none of us have the same answers so just know that if you're following along with this video that you will never get the same answer

as me unless it's something really straightforward like give me the total sales that I've made it will give you the total sales that's not random but if you asked it to give you insights it Might give you something totally different than the insights it gives me another thing is a couple of the pros and cons of using co-pilot so we're going to start off with Pros productivity boost right it aims to enhance productivity by automating various tasks such as writing emails or creating presentations or even taking meeting notes which is great we can also integrate

it with Microsoft Suite which is what we're doing today we are going To be looking at excel's version of co-pilot we also have to understand that is a natural language process which means we're able to just put in inputs naturally and then we get results and the best part about co-pilot is learning and adaptation it's always designed to learn from our user interactions so the more questions we ask over time it starts to understand how we think about our data now some of the cons of using copilot are first off Potential bias and inaccuracies right

of course this is a new technology so sometimes it might generate inaccurate information based on the data it was train TR on and you'll notice that throughout the course that sometimes I might not get the exact answer I want and I need to teach it what I want you also see here that there's an adoption and a learning curve as with any new technology you're going to have to get used to using the interface or even Certain input commands that you're going to askask it or certain prompts that you want to look at so that's

why this video is going to be super helpful for you because it's going to allow for you to understand those learning curves and last but not least ethical concerns of course you know it's an AI assistant so we want to make sure that we're not just using it to generate misinformation or plagiarized content now the last quick note before We actually dive into this is that Microsoft co-pilot is still in development and its final capabilities the pricing availability is yet to be determined so as with anything else that is AI powered it's crucial to approach

it with a critical mindset right we want to be sure that we're maintaining our privacy and security we're going to comply with any of our company's rules right and this AI is definitely not a replacement for human expertise or Decisionmaking so we are actually using it as a tool not a replacement so in order for us to actually use cod pilot for Excel we need to have it set up so I don't mean actually having co-pilot on your Excel that setup should automatically update after you purchase co-pilot and then you'll just click update and then

you should see the button but this button will be useless unless we actually set up our data for the use of co-pilot and The way that we have to have co-pilot set up for our data is that our data has to be a table and also you have to be connected to a cloud storage system which for me is going to be my one drive so if I click on Co pilot right now it'll even tell you that autosave is turned off you need to connect to autosave so that's the first thing even if you're

not connected or you don't have a table yet it'll remind you so I'm going to actually click on turn on Autosave give that a click and it's going to say how do you want to autosave I'll select my one drive and now it's saved to my cloud storage system but that's not it it's still going to tell me hey I only work in Excel tables are you ready to actually turn this into a table and it's really great because all you have to do is Select into your data set and it picks up that it's

from A3 to f105 so it's not including the title or My little bar that I made here it's only including the headers and the data and I'll click on convert so already you can see artificial intelligence is helping us we can quickly change this into a table without have to select our data now I am going to just change the table Styles here you could do this if you want to as well and let's just go with I don't know I'll go with something like this or you know let's do this one so now I

have my table it looks Great you'll notice that co-pilot pane is now on the right and if you exit out of it no worries you can always go back to the Home tab and then click on copilot to open up the pane again now when you first open up the pane you're going to see that immediately we're getting prompted right whether we want to add a formula column or highlight our data or sort and filter it or even just analyze it also in this pane we have show Data Insights show a Suggestion for formula columns

or can we highlight filter or sort the data and we can even prompt right The Prompt area where we can ask a question or make a request about our data but remember in order to even get this Paine you first have to set it up so that your data has to be a table and that your autosave is turned on so the paint itself is going to look very similar across the different Microsoft Suite so whether you're in PowerPoint or word or Excel you'll typically have this pane here and it works the same if you

click the little drop down you can either move or resize it or get help on co-pilot or even just close it out or click the ex to close out and then when you want to bring it back up you just click on co-pilot in your home tab now at this moment you'll see all the different options here once again and that typically comes with every single co-pilot so just a list of Different prompts that you can use and then underneath it you have those prompts as well so whether I'm clicking how can I highlight filter

short data it'll give me some information but that's the same exact thing as if I just click highlight or sort or filter so they give you multiple ways to do the same thing which that's Microsoft they always have done that you'll see once again we can ask a question or make a request but if you don't know exactly Where to start maybe you're thinking what sort of prompt should I do you can always click on The View prompts and it looks like a little book icon here and what it allows for is all the different

prompts and it'll give you an idea of what you can ask it so you'll see what kind of prompt do you want do you want to create and then you can CLI it and it'll showcase all the things you can create or you can say understand it'll show you all the different things like How many different teams are represented in this table or which items have the most remaining inventory so it has a great understanding of our data already we can click on edit where we highlight or sort or filter once again it's sort of

going through that same phase of what it has listed here and then we can can ask right how can co-pilot help so it's sort of the same four here that are here it's just marked a little differently another thing that's really Awesome is that we can use our microphone to ask questions so if you're going to be asking a long question and you just don't like to type it out you can use the little microphone now once you come up with a question and you're ready to go then you can start to use whether these

buttons you want to use or if you just want to ask a question you can quickly do that now remember that this is going to be different from whatever you put in so even though we May ask the same question if you're following along with the training always remember the randomness of the output right so let's take a moment and click on show Data Insights and see what sort of insights we get for our first command now throughout this training it is going to say working on it understanding your data finalizing your data so it's

going to be a little more time that we have to wait because remember this is AI it is newer Technology and it is going through all different ways to analyze our data you'll see the first thing it decided to do was get an Insight where it took my total items sold by price now I would say that that doesn't really make sense for me but maybe for some of you you might say yeah that works for us like which items but for me I would rather have it so that it shows me not just the

pricing but it shows me what the items are and then it shows me how many items We sold right that would make more sense to me so as you can see AI generated this content and it still might be incorrect you can actually rate it once you get the results you can either add it to a new sheet or you'll even say you know the content might be incorrect so you can give it a thumbs up or a thumbs down if you dislike the way that it pulled this I personally didn't like this because once

again I don't want to know my pricing like I Know the south is $1.99 but yeah but it'll also tell you right here it'll say here's the data Insight I found for you the total items sold by prices are $4.99 with 144 367 items sold and then it runs through how it made this but once again this is data that just makes no sense to me now you can quickly change the topic and it'll kind of refresh or you can keep asking it questions or ask more insights like can I see another Insight so if

I click on it again can I see Another Insight it'll show me total item sold increase over time so it's showing me now the years and the total item sold okay A little better this is something that makes sense for me so it looks like we are increasing in sales which is a good thing not as much as I want maybe but it's a good thing and I'll give it a little check mark and say I really like that now you'll see here that once you click whether it's going to be the down dislike or

the up like you can then say What did you like give it detail you can include a screenshot as well or even upload the file as long as you're you know keeping compliance with your company you don't want to upload just your file if there's some data that's confidential there you'll see share my prompt and my generated responses so Microsoft is definitely working to improve co-pilot every single day and the more feedback you give co-pilot the more better it's just going to for us in The future now once you're done with this of course I

won't submit this right now you can click the little X and it'll bring you back to your co-pilot preview here you can say that you can add all the insights to the Grid or once again you can get another insight and you'll see it's a little quicker this time now it's telling me the frequency of total items sold like you get another one now it's telling me for products notebooks the month in March has a noticeably Higher total item sold can see that in March we sold a lot of notebooks something I wouldn't be able

to just look at and tell right away so once again this is pretty amazing technology so take a moment and try some of these prompts out on your own get some insights and when we come back we're going to talk about being able to really visualize our data in a way that's meaningful to us always remember that you can change The topic which means it's going to erase everything else that you've done they'll say okay let's change topics what would you like to talk about and then it'll go through that same prompt before where you

could get Data Insights formulas or even highlight filter or sorting the data but let's say that we want to visualize this data we know that if we go to show Data Insights it'll work on it and it's probably going to give us a visual that Might not make sense for us so go through understanding our data almost there and let's take a look at what it comes up with so at this point once it shows cases are visual and you'll see total items sold by prices again it sort of repeats it so that change topics

is kind of like a reset button and you'll see that this just definitely makes no sense right I don't want to see the total item sold by prices I want to see the total item sold By product so what I'll do is I'll actually put that in the prompt here I'll say please create a chart that shows and then I'll name the columns and you don't have to put it in quotes or anything that's the best part about this total items sold per products or you can say by products whatever makes sense and you can

even tell it please Make it a clustered column and then I'll press enter so it's going to go through it's going to read my prompt I'm just simply telling it to instead of total items by pricing create total items by products and make it into specifically a clustered column chart and look at how amazing this is immediately it creates it for us now this is exactly what I wanted it tells me not only I created a cluster column Chart and it'll show me what the highest total items sold for binders was but it also creates

the chart for me now at this point I can add this to a new sheet by just clicking this button here add to new sheet and you'll see it creates a pivot table and then it creates a pivot chart so when it's talking about visualizations or it's talking about anything type of table it's typically talking about pivot tables and pivot charts because of their Unique values and unique ways of working now I have an awesome looking table an awesome looking chart and they put it all in a separate sheet now just because I'm in a

separate sheet doesn't mean my co-pilot's not there still you'll notice that over here yes we have pivot chart Fields but right above it we have our co-pilot so you can switch between the two so I'm going to just switch back to co-pilot and I'm taking a look at this it says done I added total item sold by Product to sheet one and I can even go back to the table and it switch me back now at this point it's going to give you more prompts so the more you put into co-pilot the more it prompts

you for other ways to analyze your data for instance it says you could get the percentage of total total items sold that comes from the division East or we can ask something like which is the division for months in February or months with total item sold higher than 8 800 or 81,000 right that's the best part now if I do ask it something like please create a pie chart with sales per Division if I do something like that it might not come back with the results I want and the reason it's doing this is because

you're saying division but you're also saying sales per Division and on our table we don't have a header Called sales so it's probably not going to understand what I mean by this and it's thinking and actually like I said there's always different results sometimes things will work sometimes it won't and let's see if it worked right so yeah it looks like total items sold by division C it is learning last time I did this it actually didn't work and if it doesn't work for you once again remember that that this is randomized outputs so sometimes

they'll say hey Co-pilot can't do this that's okay just rephrase your prompt and then it should work so this looks pretty great and let's say I created a pie chart by click copy copy to the clipboard you could actually paste that information in here right and then just change this in now I have the highest total sold so pretty cool now I'm not done yet I still want to continue with this so maybe I want to See the pie chart I'm going to add it to a new sheet here and then you can do your

editing now you might be thinking well is there a way to add to this pie chart now right and you'll notice that if you're on this sheet it's actually clicked off of it your prompt area is gr out because this prompt was created from a table so even though I'm clicking on the results I can't do any changes to them now that's not to say if I go back To my data that now it's available again I can't say something like please add percentages to my pie chart if you don't Click Change topic it knows

what you're talking about so it's going to keep a history of everything you've asked it so far so let's take a moment here to let it do its thing and here is a great example so it Says I'm sorry but I'm unable to add percentages to the pie chart is there anything else I can help you with so there are certain limitations to what co-pilot can do that doesn't mean I can't go in and change this myself the data is already here so you can always go to the analyze tab or design Tab and start

to add those elements in there so I'm going to add those elements I'll probably add data labels probably on the Outside and there it is and if I wanted to change these to percentages just double click to open up the format data labels and instead of value you just choose percentage so it almost got the work done not fully for us but at the same time it still looks great and then of course you can change the design the formatting whatever you like maybe you don't like this design I'll make this more of a blue

color well I don't want them all blue just Select one of them I'll make that one darker blue I'll make this one red let's go with a yellow let's go with a lighter green and there we go and if you wanted to add the percentages once again you can always change go back to design tab layouts or you could click the little plus icon and choose where you want the data labels to go maybe I want them to Be in the center and then also you know what I want to do here I want to

make these white except for this one this one yeah that looks better something like that so and maybe you want to make them a little bigger whatever works but now we have an awesome looking pie chart and even though co-pilot couldn't do the work for us it did most of it the next thing we're going to talk About is being able to highlight filter and sort our data and if you're ever wondering how to always remember you can reset by clicking change topic reset to the original Three promps so if you just click on change

topic it'll reset and then you'll see show Data Insights suggests the formula or how to highlight filter and sort data and you can click here and it'll give you a couple of how-tos on filtering and sorting I like filter to items that are due next week Or highlight the data and a couple other things you can do as well but if you actually want to sort you can actually click on The View prompt and go to edit and this is where you'll see sorting highlighting and filtering and also applying so these are the prompts to

actually do what we want we could say that we want to sort this and you'll see sort now it is important to note that you can only sort in ascending or descending order if I try to sort in any Other order uh for instance let's say I want to sort this sort by products in ascending order that's going to work perfectly so I'll go through its prompt make sure it's looking at our data and then we'll get the results and there we go you can now see that products say binders then notebooks then paper clips

and then pens and it'll even tell you that it's done and it's done that for You but what happens if I want to sort this in a specific order maybe I want to sort it by division right and I could say sort by division but I want it in this order let's do sort by [Music] division north east south west if I put in that prompt it's actually going to return a result that says it can't do it because co-pilot as Of right now can't do custom sorting so let's give it a second to finish

up here and you'll see here it says I'm sorry but I'm unable to sort the division column in the custom order you specified however I can sort the division column in ascending or descending order so right there it tells me that we can't do custom orders yet do I see this you know in the future of course it's always going to update remember I said it's a learning language So it's going to start to learn more and we should be able to do that sooner than later now with that said the next thing we're going

to talk about is highlighting and once again if you just click on change topic you can go back to the original prompts but anytime you ask it to do something it comes up with other prompts like how many products of binders or are there any outliers in my data so that's a really great thing that gives you just prompts that you might Have not thought of but let's go back to change topic the keyword prompt for highlight is going to be highlight so if you click on how can I highlight it'll tell you you can

say highlight data try saying bold the top 10 values in the sales column or I can click on view prompts go to edit and choose highlight the and then you'll see that it automatically puts that prompt in or you could just use regular English and say something Like please highlight the let's do top toal top 10 we'll say top 10 total items sold in a yellow something like that so hopefully we'll see this work very nicely where it's going to highlight the top 10 total item sold so this is the column that we want and

it'll do it in a yellow Color and there we go it's actually starting to work right now it'll say done I applied a yellow fill color and a black font color to the top 10 items in the column total item sold so I can now go through and see that that conditional formatting was done for me now what's really cool about this is even though co-pilot did this for me if you want to see the rule you can always just go back to conditional formatting and go to Manage rules and you'll see it's in there

now so it's applying this top pen and it's applying this format so that's a really cool thing to see that we didn't even have to do this artificial intelligence did so with that said since co-pilot was so good at doing this um let's just clear that out you can either go to conditional formatting clear rules and clear from entire sheet but I'm very curious to see if it'll actually clear it clear e Yellow color highlight or clear the Highlight see if it will do that one now remember it is keeping track of the ones before

but if you did close out this file and open it up again well then it's going to continue a whole new session for co-pilot and it's finishing up let's see if it's going to apply it done I cleared the formatting on the top 10 items and the columns so it looks like it still Didn't do it it says that it did but it didn't and this is once again one of those examples of how it's not fully there right the capabilities are great but it doesn't always work in our favor so I would say that

this is incorrect I actually didn't clear the formatting so I'll put a little down arrow and I'll just tell it didn't do what I asked but set it Did and then I include a screenshot if I wanted to and then I can say share my prompt and generate a response relative to this and then may we contact you and then I could submit so that's pretty cool once again this is something that I would do feedback always helps to generate a better application later on but as we're clearing this formatting once again we could just

clear it ourselves clear from Entire sheet and it's back to normal last thing I want to showcase is filtering so you can either just start by saying filter for this or I can say change topic ICS and go back to the prompts so if you want to go back to prompts you always can but in this one I'm not going to I'm going to say please filter for the month of January and let's see what it comes up with so we have our months it should be smart enough to see that in our data and

Look how cool that is immediately we see and it'll even tell us done I applied a filter on month column to show only things equal to January so this is working immediately for us now that we have this we can always just use the same thing where we click the little drop down and clear the filter but I am curious once again to see if it will clear the filters clear all filters on table and it's always fun to just play Around see what works what doesn't work and just see this technology grow so we'll

give it some time once again it's almost there let's see if it returns our data and there it goes that one actually worked really well and let's see if he'll say I removed the filter on the table is there anything else you would like me to do so since we made this a table this is actually a good point to look at it does say table one it is a Good idea to click on your table and rename it that way you just have a better way of calling out to it and referencing it but

yeah it's not necessary but I just think it'd be nice to do the next thing we're going to be talking about is how we can have co-pilot create formulas for US based off of colums that already exist for instance we have here our total item sold and we also have our prices and Maybe what I want to see is what is the profit from that now we know that we can do that by taking the total item sold and multiplying it by pricing so I'm going to go over here and ask a question and ask

to please create a new column called profit by taking items and prices and multiplying them So we're going to take a moment and see that work and we'll see what it comes back as the result and there we go so when you're asking fors to create a column it'll calculate the profit for each row in the table by multiplying the total item sold you'll notice I only said items you don't have to actually write total item sold it knew what I was talking about and It's going to multiply it by the respective prices it'll even

show you what that looks like and it's naming the entire column by header so at total item sold this multiplied by prices here if you need to explain the formula a little more you can click the little carrot icon and it'll even showcase it again calculates profit it tells you the following formula multiplies the value by the value and prices and then it formats the results as a currency value With a th separator and two decimal places and any negative values would be displayed as red we don't have any negative values so that's great but

now what we can do is insert the column and just by hovering over you'll notice that right next to prices now we see profit which is pretty amazing now I can click insert column and there we go it says done I inserted column to column G and named it profit for us the one thing I might do Is just select these and merge and center again that way I can extend the line but this looks amazing now I can quickly see that it works and I can even get insights based off of this column now

for instance I can ask a question saying what is my top profit and it'll tell me what my top profit was which is binders from our May sales in 2023 North it'll tell us that it's 44810 or 8.40 and4 and that looks awesome we can even create more columns as well let's say I want to change the topic again to go back to the original I can show a suggestion for a column and let's see what it comes up with so it's saying right here that what we could do is calculate the profit margin and

we can do that for each row by Taking dividing the profit by the product of total items sold and then their prices as well so once again it's going to show us the formula if we need to explain it a little more it will and then we can insert the column and of course it's going to be at 100% because there's nothing that we didn't have a percentage wise so this works beautiful now I can decide not to do this because I don't think it works for me I I know that everything is sold at

100% so I Think we're pretty good but I can say that maybe I want to create a projection projected let's say um sales projections or profit projections by taking profits and increasing them by 3% so create profit projection column and it's going to take the profits and increase them by 3% and Let's see if they can do that and once again it's going to work on it it'll go through I know it's working because it's saying that it's generating the formula column and typically when it gets this far it means it's going to work if

it does return like a result that says it can't do it no worries and look at this it's actually going to take it by a 3% increase which would be 1.03 and that's What it's going to multiply by and we can insert that and that's what our profit projections would look like and there we go now if something like this happens just because of formatting you could just use the format painter and that looks good again once again merge and center merge and center and now we have an expanded table so adding formulas can be

very important And it's super easy to do it now we don't even need to know how to write formulas or what the syntax is for Excel formula writing co-pilot can do it for us so we've seen how amazing co-pilot can be with its prompts but let's actually start to explore some different types of ways we can analyze our data let's say for instance that I just want to ask at a simple question like how much profit did we make and let's say June of 2022 I'll ask it a simple question just going to go through

give it a second to work so how much profit did we make in June of 2022 once again this is you know co-pilot in its beginning stages so it's starting to create my information sometimes it takes a little longer depending on internet and bandwidth as well but you'll see here it tells me That the sum of profit the year 2022 132 642 and it's telling me in June of 2022 that was what the profit was so it created a pivot table now you can add this right away to a new sheet just like this and

you'll see it's now actually doing years and it's filtering for 2022 doing months and filtering so I love the fact that they do this and let's just click go back to table now at this point as always whenever you ask co-pilot to Do something it starts to come up with topics that are a little relevant to what we're talking about so for instance what is the products for month July or percentage of total profit projected for each product so let's take a look at that so we're going to see the percentage of total profit projection

for each product which I'm assuming is going to be about 100% on each we'll see what it comes up with it's finishing up so that's good news and then once again Even though it suggested this so this is a really good detail to Showcase in this training is that even though it suggested it itself and I clicked on their suggestion for the prompt it told me that they couldn't do it so that's the little bit of Downfall with co-pilot is you might get prompted to ask for something and it won't work for instance let's take

a look at another one that it prompted now it's asking us percentage of total profit for each product let's See if that's going to work so we'll give that a click and I can foresee in the future of the I mean it's continuous learning so it's going to continue to learn and understand the things that we're asking it and look right here we now get a nice little chart bar chart it tells us the highest profit by products it'll even tell you that binders have the highest profit with not books being followed and It'll even

create a pivot chart to get the answer for that and then let's keep going with this let's say that I want to ask it something else let's do this did I make more profit in let's say January months or April months let's see if I can figure this one out now as I said throughout this training have fun with this explore you can write whatever you want there's no right or wrongs here We're going to take the time right now to explore some things that will work and some things that won't and once again it

is going to be different for everyone's outputs but it looks like this is looking pretty great it says you made more profit in April with a total of 215,000 as opposed to January where we made 143 so and there it is it creates a little pivot table for us and we can continue to add those to new sheets but Let's keep going uh total profit by division as a column chart or we can show total item sold by years as a line chart that might be something nice or distribution of division by months let's do

that one let's see if that's going to be something that comes through and once again this is just amazing technology here sometimes it's going to work sometimes it won't but it's going to make it easier for us to create different content and analyze our Data differently look how awesome this one is here's the distribution of division by month East has the highest number of rows in June with nine followed by West in March with nine and they created a query result to get the answer so this looks great and I can add it to a

new sheet and let's see what they came up with and there we go so it's counting the rows of Divisions and how many entries were in each row so this is Something that is amazing now at this point I'm going to go back to the table again and what we're going to be talking about is the co-pilot lab next up but before we get into the co-pilot lab part let's actually click into view prompt and take a look at some of these as well let's do this let's say that we want to add a column

that calculates the profit per order okay that sounds like something pretty cool so let's see if we can do It profits per item sold and we'll see what it does here it might return something that just makes no sense add a column that calculates profit per the item sold so we can see if the price was $299 so we should get the same prices as the new column now and there we go it looks like it's doing price and if we enter so it's Almost there it's it's got a little bit of an incorrect there

so it's given us 209 but let's view what it's doing here it's telling us that and it's just going to explain the formula but it's doing a 70% so it's calculating multiplies the value and prices column it's taking our prices column and it's multiplying that by 70% to find the profit per item so I don't know why I would do something like that I don't think that's going to be something we need we could have just Taken the profit column divided by the total item sold and got the price because that's what it's multiplied to

be but as I said it's a learning process so for this one I'm not going to say anything but let's keep going let's do uh how much should we make in total profit how much did we make in total profits so now it's going to do c ation where it should take the Profits all of them and sum them together or do an auto sum once again we'll see if that works then it does sum of profit it says the total profit made is 1,733 15 right so that looks great but let's keep going with

this how much should I make total for each year how much profit did I make total for each year so what I'm assuming they're going To do is probably create a pivot table and then they will filter for each year so they'll do 20 I think we have 2020 2021 2022 and 2023 and there it is they actually created a line chart instead which is pretty awesome still and we can clearly see and then it gives us the answers here so once again you can copy this information put it into report if you wanted to

you don't have to just simply stare at the chart here you can actually Take the information and copy it maybe put it into a report put it into one note whatever you like Okay so this looks really great now what I want to do is change the topic I'm going to just take it all back to the beginning which means it's not going to feed off of this so when we ask it a question it keeps feeding into those questions and making things that are relevant for us relevant prompts but if we choose to change

topic it goes back And it erases all that history of data which is good to understand otherwise if you click this by accident you're wondering why it's not giving you those other requests like it did here those other prompts it's because of the fact that we've changed the topic to put it back to its original state all right so try out a couple of these on your own with this data have fun with it ask questions that we didn't even ask here and see what you come up With we've seen co-pilot pain in our application

here but we can actually go even further with co-pilot if you'll see here the little view prompts area we can click on that little book icon and then once we click on it it brings us to those prompts that I showed you before but we can click on view more prompts and it's going to open up this dialogue box and it's going to show us some of the prompts from our co-pilot lab now co-pilot lab offers a Collection of prompts that will teach users how to create learn and use co-pilot and the co-pilot prompts are

the instructions or questions that you're going to use to tell co-pilot what you want to do you'll see here some of the ones that they have pre-created for us and we can even save those prompts by clicking on the little save icon or tag and then you'll see all your saved prompts by clicking here you can also go through the Different categories create understand stand or edit and filter through them but if you truly want to get the most out of co-pilot lab you can see all the prompts in your co-pilot lab here and when

you click on this this is where it's going to bring you to that co-pilot lab in your web browser now that we're in co-pilot lab this is where we can explore the in products so where co-pilot exists for example 365 word teams Outlook PowerPoint Excel even in OneNote or Windows Loop whiteboard or forms you'll see all the prompts to try and remember that co-pilot lab is here to teach us what sort of prompts exist now in the prompts to try we can save prompts by clicking the little ribbon icon and we can quickly save a

prompt so if I want to save this one just click it and now I have all my Sav prps here so that's a nice little flag System right next to it you'll see we can filter for whatever app we're using this for whether we want only Excel or word or even Outlook prompts and then what sort of category do you want what prompt do you want to create something or edit it or understand it ask questions about it or even just catch up and get summaries you'll notice that when we're looking at the prompts it'll

explain what the prompt is a nice little Category what's new and then you can see an example of what you would write for the prompt you'll see in the bottom left hand corner what the prompt is coming from so which application in this case this would be Microsoft 365 this one's a one note prompt this one's an Outlook prompt a loop prompt you can go through and find Powerpoints and this is also what's really cool about this system is that you're learning right so if you're like How do I add an image to a slide

it'll show case add an image of and then you can say for example a puppy dog to the slide so if you don't remember that you can always just save this and then use them later on in your applications once you're there and there's so many different ones like that shape that's a pretty cool one Microsoft pre-created a bunch of these for us we can click on show more show More and there's just so many different ones now once you go through the different prompts and you choose which ones you want to save and use

inside of co-pilot in those applications you can then go back to the app and start using them you'll see down here we do have a couple of articles for quick tips for better prompts so summarize this email in two bullet points you'll see that you're giving more detail and you're specifying that you want two bullet Points that's something that we're going to learn about for co-pilot is that we need to really be specific on what we're asking it because well it's still a computer right we're still talking to computer language underneath it we have FAQs

what is co-pilot prompt or what can I get done with co-pilot how can I get the best responses or results you'll see a bunch of different articles Here now going back to the top you can go into the get started and there's so many different ones they have for home users Business Leaders or for admins let's say that I'm a business leader and I click into that if you notice the servic is unavailable right now so these are services that are starting to come out slowly there are slow roll outs so just you know be

patient with it you'll see this one's still Unavailable and I believe admins is unavailable too oh no they finally got the admins up so so just wait for the Business Leaders that should be coming out soon and then we'll see here Microsoft co-pilot 365 documentation get ready for co-pilot with 365 get started with manage it get trained on they have so many different resources for you to take a look at and utilize another thing that's really important with co-pilot that I always Say is the plugins this is going to plug into different apps and Integrations

not only with just Microsoft with other Technologies as well so take a look at some of the things that are coming up and once again enduser co-pilot resource co-pilot lab is one of those resources this lab is where you meet co-pilot you understand what it can do you can see it in everyday apps and those are once again those prompt and you can click the little save Prompt so this is more like that main page here this is landing page for copilot lab but it's pretty much the same if you go to prompts to try

it'll just bring us back to where we were what's new that's always something that's important I'm always reading up on the latest updates for Microsoft co-pilot so you'll see here prompts of the month what were the most important ones or what were things that weren't working that they've now updated Transform a doc into a presentation that's a really cool one and you can upload document files actually talk about this in one of our other classes when we get into using co-pilot with word and also using it with PowerPoint but as you can see here improvements

based on our feedback before we talked about how you can submit feedback it is important remember this technology is Young still so we need to really Express what's working or Not working for us to make sure that co-pilot can evolve into the tool that we need to you can take a look at some of the previous updates as well our February updates January December just click on the little carrot icon you'll see all of them and I just love the way that they display like all of the icons you know that this is specifically for

word it tells you you know what it's for we're actually going to be talking about word for the web so transform your text Into a table this looks like it's only available for word for the web so not for the desktop app that's another thing that's really important when using copilot there's going to be different things that work in the online version versus the desktop version and there we go so always remember that this is still co-pilot lab we can have all the same tabs up top we can switch between prompts to try getting started

remember for business Leader is one that's not available right now but it will be rolled out sooner than later and there you go we've seen co-pilots capabilities inside of Excel for desktop we've even went into the co-pilot lab and saw all the different prompts that we have access to what we're going to do next is Showcase how we can access the online version of excel so in order to do that we do need to sign into our 365 account So I'm going to go grab my 365 account and we're going to then be able to

take a look at co-pilot for Excel online so now that I have my web browser open I'm going to just go to office.com sign in with your credentials and that will bring you to your 365 now as you can see here we do have co-pilot for 365 we're actually going to talk about that in another video but for now let's just go to all of our apps and what we're going to do is head over to Excel for online now at this point I'm going to just open up one of the ones we've been working

on that's sales data the same one because remember in order for a co-pilot to work for Excel it has to be a table the data and it also has to be saved to R 365 or one drive online account so now that I have it here I can quickly just open it up in the online version now there are a couple differences in the way that it looks of Course because this is the online version of excel but everything works the same way so I have my data here I can click on co-pilot and it'll

still open up that co-pilot paint you'll notice that the icon looks a little different but everything else is the same we can view prompts create understand edit or ask questions we we can also if we click on to like add columns or we want to see more prompts we can go over to the lab So let's say learn more about co-pilot we can open it up and then we're back in the lab here also remember that we can change topic to reset this and then it works the same way that we saw before let's ask

it a question about our data let's say how much should we we make in pen sales and it's going to go through the same way that we've been doing this in the Desktop and there it is so it tells us that our profit for products pens was $358,500 16 and here's a pivot table I created to get the answer we can automatically add that to a new sheet and there it is all right so online version you can use co-pilot desktop version you can use co-pilot you can access a lab co-pilot lab from both of

those everything's the same there's not a real difference Between a desktop version for co-pilot and the online version now if you do notice like I showed you before with the labs that there is something that says it is only pertaining to the web version well then you know that that prompt will soon carry over but for now it's only for the web version I haven't found too many of those for Excel but yeah just explore and have a lot of fun with this as you can see co-pilot is so amazing and there's so much you

can do With it I hope you all enjoyed this course and I'll see you all next time thanks hello and welcome to excel power user you're learning with Mo Jones IT professional and educator for our power user course it's all about conditional functions so we'll take a look at conditional functions that can summarize or aggregate data for us we'll take a look at lookup functions in particular the X lookup function which replaces vlookup hookup and index and match we'll Also take a look at text functions very nice functions for cleaning up your data or even

to improve your data set as well we'll also take a look at Advanced pivot tables so what are some other tools that are available to us after inserting a pivot table creating a pivot chart and filtering our data as well that's what we'll be covering in module one of our course today for module two we have another set of objectives we'll be taking a look at Some useful array functions we'll take a look at Advanced conditional formatting we can create our own conditional formatting based on formulas we'll also take a look at Advanced charts then

we'll finish up by creating some macros by turning on the developer mode so go ahead and open up the practice file for module one and come right back and we'll dive in I've opened up my power user module one file here and let's go ahead and Take a look at our very first worksheet so our very first worksheet we'll be covering the IF function and the IF function is basically the foundation for our logical functions so in preparation for our database functions we want to make sure that we understand the IF function so for our

functions we're going to be inserting the function you can either type the function here in the cell we can also type it in the formula Bar if we want to but we're going to be inserting it the traditional way from the formulas Tab and clicking on the insert function dialogue here as well we can also press shift plus F3 to get that done now in order to understand how this works let's take a look at the IF function and its most basic form in this case we have some sales figures for the month of February

a few sales representatives here for example for S Sam we're tracking the week sales Week 1 9550 week 2 9230 week 3 8500 and week 48965 so in the end the totals for S Sam for the month of February is 36,24120 and i12 we would say something like well is the value in F5 greater than or equal to the value that is an i12 so that's our logical test Excel will compare the values in those two cells and give us either true or false but in this case if it's true we just Want to go

ahead and say yes so obviously we asked the question here did Sam meet the monthly goal the answer is yes because 36245 is greater than or equal to 34,000 let's go ahead and express that so that Excel can understand our logical test I'll go ahead and click on Cell H5 and I'm going to insert my function so I'll choose H5 here I'll go ahead and click on the formulas tab I'll go ahead and insert my Function you can also press shift F3 I'll go ahead and type the IF function at the top here I'll press

go and then I'll press okay here's my function arguments dialogue box tells us what the function is what the function does and for each argument what is actually asking us for so we can see that this function needs three things we need to introduce our logical test optionally we Can specify what should be displayed in the cell if the value is true or if it's false notice how these are not bold as the first argument so these two are optional because by default they're going to return either true or false well let's go ahead and

set this up so our logical test we're concerned about two cells we concerned about cell F 5 that has s Sam sale we want to see if it's greater than or equal to the value that is In i12 Excel has already determined that the value here is greater than the value that's in here therefore it's already telling us that it's true but we don't want it to say true so if it's true we'll just simply say yes yes and quotes if it's false we'll simply say no here's our answer it's already telling us it's going

to be a yes I'll go ahead and Press okay and we should be good to go so there it is we have our yes now we want to be able to autofill down so if we grab our fill handle here we can fill down and end answer the rest of the questions here as well so what I want to do is I want to go ahead and absolute reference my monthly goal that is in cell i12 so to do that I'm going to rewrite it this way it's going to be F5 is it greater than

or equal to dollar sign I dollar sign 12 so this way when We write our formula the values in column F will move down row by row but we're going to go ahead and lock on to the monthly goal so I can just go back to my original formula here in the formula bar or if I double click on the cell I can see the formula and I can go ahead and right here I can just go ahead and press the F4 key on my keyboard and that will absolute reference the i12 for me so

I'll press F4 there we go and now I can just go ahead and press enter I'm good to go if I autofill down now as we take a look if I double click on that second cell I can see it's moving down in column F but it's still locking on to i12 if I double click on the next one here moving down as expected in column F but we're still locking on to i12 so go ahead and write your IF function for Column H answer the question was the gold net you should get three Nos

and three sells with yes and come right back now that we've answered the question what's the goal met for each of our sales reps let's move on to column I we're going to go ahead and calculate the bonus if our sales rep met the monthly goal so we do have a bonus opportunity here of 6% of the sales if the goal was met So in this case s Sam did meet the goal so what we're going to do is run the calculation we're going to multiply the value that is in cell F5 * 6% so

we're simply going to say if the value is true we'll say F5 times the value that is in I13 we can use the same statement as before so for example we can use our same conditional test here if the value in F5 is greater than or equal to I12 instead of saying yes though this is where we're going to perform our calculation so instead of saying yes we're going to perform our calculation we'll say F5 times the value that is in I 13 so that's the only change that we need to make here as well

so let's go ahead and set this up I'll go ahead and click on Cell I5 I'll go ahead and insert my IF function again it's already up here so I'll just Go ahead and press okay again I can use the same test I could have even copied the formula here as well but I'll go ahead and write this over so F5 is a greater than or equal to the value that is in i12 since I'm right here I want to go ahead and highlight the i12 and press the F4 key on my keyboard that will

absolute reference it for me now the value if it's true remember we want to perform a calculation so I'll multiply the value In F5 times the value that is in I13 I am going to Absolute reference i3 because I want to be able to fill down my formula so I'll press the F4 key on my keyboard again and there we go we can see it's already giving us the calculation because this scenario is true it's already performed the calculation for us so we should be getting a 2175 bonus for S Sam if it's false I'll

just say something like na And quotes and we're good to go so just to recap our logical test is the same as before is the value in cell F5 greater than or equal to the value that is in i12 so in this case because it's true we're performing the calculation I'll go ahead and press okay and now I can go ahead and autofill down because I have values to the left I can double click on the fill handle here and it will copy it down for me so there you go I'm good to go with

my IF Function here so the IF function we can either say true or false we can have our custom text or we can perform a calculation as well now this is dynamic so if I were to give Sandberg the extra dollar here to meet the monthly goal I'll go ahead and change the value here and notice how this will change to yes and we'll also get the bonus status updated right here as well so because we're using the cell reference everything is dynamic So now all we need to do is just input values here or

actually over here so for each month as these cells are populated these two columns will be updated automatically as well I'll give Sandberg the extra dollar so 34,000 I'll press enter and there we go right go ahead and write your second IF function to calculate the bonus status and I'm right Back let's go ahead and write one more function to complete this worksheet here so we have one more opportunity here in cell K5 so even though you did not meet the monthly goal we still have an opportunity for a department bonus of 1K so everyone

gets 1K but it's based on two conditions so the first condition is that the total sales needs to be greater than or equal to 200,000 and then the average sales Here needs to be greater than or equal to 40,000 so we can see that one is true so we're already at 213 so this is true however this is false so we need to go ahead and write a function to determine whether both conditions are true the keyword here is and there's actually two ways we can do this we can either write a single and function

the and function will just give us the default true or False if we want to go ahead and write a custom message however such as you know great job team or keep trying we would need to Nest the and function inside of our IF function well let's go ahead and write our and function first and then we'll just go ahead and Nest that in in our IF function so we're using the and function because the IF function only allows for one logical test so if I open up my if statement here I can see it

only allows for one Logical test but what about the end function I'll click on the insert function here I'll go ahead and insert my and function I'll press go and I'll press okay so here's my end function and as you can see it provides opportunities for more than one logical test we can actually go up to 255 logical tests as well well let's go ahead and enter this so our first logical test we're concerned about the Value that is in cell F12 is that greater than or equal to the value that is in l12 so

213,000 is greater than or equal to 200,000 so this first part is true now logical test two I'll go and click here is concerned with the average sales so is that greater than or equal to the value that is in cell L14 so we can see that that is not the case 35,000 602 is not greater than or equal to 40,000 so we end up having one that's true and one that's false now the N function both need to be true so because one is false we end up with false here as well so if

I press okay this gives me a simple answer letting me know that well it's false right we're not going to get that department bonus of 1K but what if I wanted to write Something custom in here such as you know maybe if the department gets a bonus we could say something like great job team or we can just say something like keep trying as a department well to do that we would need to Nest the IF function so look what I'm going to do I already have my and function here that's giving me the result

so I'll just go ahead and copy my And function so I'll copy that to the clipboard I'll press escape to get away from my formula bar here and maybe over here create a little space for my function so I'm going to insert the IF function okay so my IF function is only concerned with either a true or false value well remember my n function that I just wrote is giving me either true or false so I'm going to go ahead and copy And paste the end function in here right so my logical test is the

result of my n function and we can see that it's giving me false so mission accomplished so now if it's true I can say great job team and if it's false then you know we can say keep trying as you can see in this case our n function is returning false for us because both conditions are not met so Therefore the result will be to keep trying now one of the cool things here now that we have the function arguments Box open to simplify things what I can do take a look at the formula bar

is I can go back and forth between the IF function arguments box and the and function arguments box so if I click on the and function here notice how it's switch over to my and function so this is just a little tip helps us when we're nesting Functions here now I can toggle back and forth between if and and I'm good to go I'll go ahead and press okay there is my answer now remember all of this is dynamically linked so if these values were to change once we get to 40,000 average sales or more

our message here will be updated as well go ahead and write your and function and go ahead and Nest your and function inside of your IF function as well and come right Back on this worksheet let's take a look at several variations of the IF function in particular count if sum if average if and sum ifs let's take a look at our expense report we have some expenses from rows six down to row 50 if I scroll down it goes down to row 50 here so we have the date of the expense we have the

office either Uptown Midtown or downtown we also have the actual expense so technical support communication office supplies and transportation and so forth We're also tracking the amount so for example on July 1st we had our first expense it was for the Uptown office for technical support and the amount of $747 the first thing we want to do is we want to go ahead and highlight all of our Tech supports here as well so how many expenses did we have for tech support well here's one and if we go down here here's here's two and here's

three and so forth and so on so what We're actually doing is we're taking this input value here so we want to count how many expenses did we have for tech support we're taking this value and we're searching the expense range and we're counting how many times tech support actually appears in there so if you think about it what we're doing is we need a range we need a criteria and that's it we already have that information so the range that we Want to search well that range is from C6 down to C50 and our

criteria well our criteria we can either type technical support in there or we can refer to cell G10 we want to use the cell reference because if we change Tech Support to another expense we can get a real time update in terms of how many times that expense was logged as well but the first thing we want to do let's go ahead and name our ranges so Instead of highlighting you know C6 all the way down to C C50 every time we want to run some type of function fun on here let's go ahead and

name the range I'm going to go ahead and highlight the entire range and right up here in the name box I'm going to go ahead and give this a name so for the office expense range I'll call this expense or expenses so notice I've highlighted the entire range And question and I'm typing the name of the range right here in the name box well I'll go ahead and press enter and now I have a range I'll put the name right here so I can remember what it is I call that range expenses going to do

the same thing here well now what I can do I'll go ahead and click on the drop down and I can see I have some ranges in here notice we have some table ranges we'll take a look at this in just a little bit if I click on That expenses range notice how it highlights this range for me so from now on when I refer to expenses it's going to refer to C6 down to C50 well we'll see how this will help for example if I want to go ahead and summary and maybe count amount

of expenses in that range well now I can come in here and I can use the countif function so I can insert my counter function and as I mentioned before it's going to Ask you for the range and for the criteria well for the range I'm just going to type expenses in here and notice how it's giving us a preview of the actual range so tech support communication office supplies my criteria I can either type technical support in here and quotes and it tells me that there were six entries for Tex support but I want

to use the cell reference instead so I'll go ahead and Refer to cell G10 so I'll go ahead and just click on Cell G10 in here so whatever value me delete that there you go so my range is now expenses AKA C6 through C50 my criteria is tech support AKA cell G10 if I press okay okay I'm notified right away that I have six expenses for tech support now I can change this to kind of give it a test maybe I'll go ahead and check to see how Many times did we have meal expenses locked

so I'll type meal in here and I'll press enter and we had seven so that is what the countif function does but combining that with naming the ranges helps us to work more efficiently go ahead and name the office range so B6 to b50 go ahead and name the expenses range C6 to C50 go ahead and name the amount range which will be D6 to D50 go ahead and enter your count of function give it A test and come right back let's go ahead and answer our next question here so what were the total expenses

for office supplies so in order to answer this question we need to find all of our entries here for office supplies go ahead and locate the amount and the amount column and add that to the running total so if we were to do this consistently we would actually get our answer right so at least in my view Right now here are at least the very first three entries for office supplies we add these numbers together and that is what the sum IF function will do so let's take a look at the sum IF function now

we already know we're going to be asked for the range and the criteria we already have the information that we need I'll go ahead and click on this cell for ag14 and I will go ahead and insert the Sum IF function so here's some if and again what's the range R well the range is going to be my expenses so I'll type expenses in here what's my criteria well it's office supplies but I want to use the cell reference here which is G14 and then the third one what is the range that I want to

summarize well I want to summarize the match that's found in the amount column I did name that as Amount so I'll go ahead and type amount in here and there we go it's already giving us our answer the total for office supplies is 392 right I'll go ahead and press okay and there we go all right just need to go ahead and give this the accounting or currency format I'll give this currency and press okay and there's our number format in here as well so this Works well because I already have my ranges named so

here's my amount range I can see all of my numbers under the amount column are selected my expenses and then my office as well now we can repeat the same thing in order for us to calculate the average cost per meal it's going to be the same thing the expense range our criter IIA is meal and we want to run the numbers on the amount column so I'll go ahead And this time I'll insert the average IF function this will give me the average per meal expense so I'll search for average if I'll press go

and I'll press okay so same thing range expense meal so range is going to be expenses again my criteria are the meals so I'll click here and the range that I want to run the numbers on well the Only range is the amount range so I'll type amount and there you go so the average meal expense is $594 so I'll go ahead and press okay again I'll give this the currency format I'll just use the format painter this time and there we go so three down one to go but as we can see it's always

asking us for the same thing what is the range what is the criteria and what is the column of Interest that you want to run your numbers on the function takes care of everything for us in the background a lot of calculations that we cannot see that we're not privy to always we're asked to do is just point to the different range and what is the criteria as well now what I can do because we're using the cell reference what is the average expense for let's say office supplies so I can type office supplies in

Here and it gives me the answer right away so 650 and 33 go ahead and write your sum if and average if go ahead and change the values and get some real time updates here as well it's a really nice way to get some insight on a data set come right back for our last example here we have a unique situation so in this case we want to summarize what are the total for the meal expense in the Midtown office so Now we have two ranges office and expense and we have two criterias the office

needs to be Midtown and the expense needs to be male now the sum if only allows room for one criteria and range so we need to use another function that will allow us to have multiple ranges and multiple criteria and that is some ifs I'll go ahead and click on Cell G20 23 and I'll click on the formulas tab let's go ahead and insert our sum Ifs function here I'll press go I'll go ahead and press okay there's also an average ifs that allows us to do the same thing in this case the very first

thing it wants our function arguments here it wants us to specify the sum range now remember our sum range is going to be the amount column so let's go ahead and type amount in here so I'll type amount there we go here's a preview of The amount array 747 907 540 and now we can enter criteria range one so criteria range one is the office and the criteria is Midtown so criteria range one we need to search the office column here so I'll type office in here I know it's locked on to the right range

here's the preview of the range criteria one is going to be Midtown so so far we're summarizing the amount column where the office is Midtown now we have criteria range two so I'll click on criteria range two in this case the expense column needs to return the meal expense so I'll type expenses in here and the criteria two needs to be meal so I'll click on meal and there you go so basically we're summarizing the amount column the office needs to be Midtown and the expense needs to be meal here's our answer 3310 I'll go

Ahead and press okay and let's just give it a Qui Quick Test we can change the office and the expense and let's see what we get here I'll also apply the accounting format or the currency format rather to here and we're good to go so now we've created a really nice Dynamic worksheet and it's very typical sometimes this worksheet will live on another worksheet Tab while the data is being entered in here as well so again this is Dynamically linked to this data set so any changes to our data set here is going to update

our little worksheet over here as well well I'll do one more thing here I'm going to go ahead and change this let's change this for what are the total expenses for let's say communication in the Uptown office so I'll change this to Uptown and change this to communication and there we go so all matches for Uptown communication we have 2,545 so those are our database functions let's head on over to our xlookup worksheet tab xlup is a really nice function basically it allows us to do several things one of the things that allows us to

do is to return a value from a database or return different parts of a record from a database let's see how this works we have a kiosk here and we're basically inputting the employee ID Number now after we input the employee ID number Excel is going to head on over to the employee ID column here and it's going to find that employee ID number once it finds an employee ID number in that column we can return the last name first name department and the hire date and they will all be displayed in our kiosk right

here so this way if someone ask you what is the higher date for employee with the number 1054 we don't even have to see this Database we we don't have to come and scroll through it to look for that employee ID we simply just need to set up our X lookup so that it does the work for us if I change this to 1368 we can see that 1368 it's down here this is the record for Tammy Woo and as expected it's returning the last name first name department and the higher date as well this

is a really nice way as well to Just automatically populate maybe a form with using X lookup So based on the value that you input in one cell it's going to return the corresponding field for you here as well so that is basically how X lookup works if you've used other functions like index and match or vlookup you know that they have prerequisites with X lookup we don't really have any prerequisites here as well let's go ahead and enter our X lookup I'll go ahead and delete my Formulas in here let's go ahead and enter

our X lookup let's return the last name first so I click on Cell B4 click on my formulas tab here's my insert function I'll go ahead and look for X look up I'll press go here's X lookup I'll go ahead and press okay so now with X lookup again the function will do the work for us we just need to point to the different places so it's asking us for The lookup value well the lookup value is what gets everything started it's going to be whatever is in currently in cell A4 the next thing it's

asking us for is the lookup array the lookup array is basically where can we find those employees ID numbers so it's going to be in column G right starting with G10 down to about row 46 the next thing it's asking us for is What is the return array well the return array is what you want to return so remember we want to return the last name that can be found on that First Column so it's going to be A10 down through the last row which I believe is 46 so basically if we were to plug

this in manually it would look something like this right so our lookup value is going to be A4 and then our lookup array is going to be basically where can we find those Employee ID numbers that we're placing in cell A4 it's going to be G10 down to g46 we have 46 rows of data well we go down to row 46 and then the return array we want to return the last name which begins A10 down through a46 as soon as we plug in the information here it's going to give us our result so it

should give us the appropriate last name based on the Employee ID number so let's go ahead and plug these in so our lookup value let going to to be our input cell which is A4 already confirms that 1368 is in that cell for us for the lookup array well I can find those employee IDs in the from G10 down to g46 so there we go here's the array which array do I want to return or range I want to return from the last name I Don't feel like scrolling through I'll click on the first last

name and I'll press control shift down highlights all of them for me and there we go look up values A4 we can find them in G10 through g46 the returning column is A10 through a46 we have woo for Tammy I'll go ahead and press okay and there we go so that is is how X lookup works and as you can see much more efficient than The other lookup functions that we can use I'm going to go ahead and change the employ ID to 1054 and I'll press enter and I am good to go right now

there's some other things that we could have done we could have named right we could have named the employee ID range if we want to and then we could actually name these ranges here as well last name first name department and higher date so that we could do that the other thing that we can do is we can Just modify our original X lookup here and we can lock on to cell A4 so in the formula bar I'm going to Absolute reference A4 so I'll press the F4 key on my keyboard we're also going to

Absolute reference the employee IDs so G10 through g46 so I'll press F4 absolute reference those as well so now we're locking on to the employee ID in cell A4 we'll also lock on to the Employee ID range over here as well so now what we can do we can autofill over and just change the return range I'll goad and press enter here and let's see if I autofill over to the right so we can see it's giving us the last name here's the first name it's moving over the department that's fine and for the higher

date this is incorrect we just Need to go ahead and change the return so that we're returning the values in column H and not column D so right here I can just say h10 through h46 and if I press enter we get the higher date it's giving us the serial number for the higher date so I'll just go ahead and change this to the date format I'll use a short date here and there we go so there is our X Lookup input a value ex Excel will find the values in a a database for you

and return feels from that record here as well go ahead and enter your X lookup to complete our kiosk go ahead and give it a quick test I'll put 1075 in here and here's our record for Cheryl Kane let's take a look at a really nice example of how we can use x lookup here so it's very common in the data world to have different data sets so if you take a look on this worksheet for joining Lists we have a few data sets here we actually have a table table over here so here's our

products table we have several products the Aspen product is $21 we also have a region table so the region table we have our sales rep for example Alan Ramos is from the Midwest region and then we also have an order form over here as well so here's our order form we have the date of the transaction we have the sales rep from our region table we have the Product from our product table and but we're missing something we don't have the price here and we need to go ahead and calculate the sale and if we

want to run a pivot table analysis on this data it would make sense for us to maybe insert a region column so that we can run some Regional reports as well so that's what we'll do I'm going to go ahead and insert a column First to the left here right after the sales rep so I'll go ahead and insert a column and I'll call this column region so now we can run pivot table analysis based on the date sales rep region product and the sale as well well let's go ahead and return the region based

on the sales rep so let's use Austin Reynolds as an example here if I said let's go ahead and return the region for Austin Reynolds well you'll take Austin Reynolds here as our input you're going to go down the region table here for sales rep and once you find Austin Reynolds you say oh well Austin Reynolds is from the East so you'll go ahead and put Easton here for Austin Reynolds but uh we don't want to do that manually and that's exactly what xlup is going to do for us here on this worksheet this is

10,000 rows of data so if I press control end we can see it Goes down to row 10,3 so let's see if x lookup is going to do a good job for us I'll press control home to go back to cell A1 let's go ahead and return the region based on our sales rep same thing lookup value lookup array and the return array so in this case I'll actually plug it in to kind of help us here so our lookup value in this case is going to be Edwin Malone okay so AKA cell H4 so

I'll click on Cell H4 for my Lookup value as far as my lookup array where can I find my sales reps well they're going to be in column D so it goes down to row 75 right so it's going to be D4 to d75 so this will be D4 through d75 and then what do I want to return well I want to return the region which is going to be E4 to E75 so I already have all of my inputs here that I actually need and now I just need to go ahead and plug them

into my xlup and allow xlup to do it's work for me go and make this Orange let's go ahead and return the region based on our sales rep I'll click here on Cell I4 and I'm going to insert my X lookup function my lookup value Edwin Malone and cell H4 so I'll click here and I can confirm it's recognizing that Edwin Malone is in here now it's giving us the table name for that cell because we're working with tables now for the lookup array well we can find those here in cell D4 to d75 so

I'll go ahead and highlight all of those and it also gives me the table name here as well right so table two sales rep column so it's converting it for me automatically we can also type that in if we want to so what do we want To return we want to return the region column from table two so I can actually type it here so I can say table two and then brackets I'll type the name of the column which is region and close my brackets so that's one of the benefits of working with a

table it automatically names the ranges for you as far as the column well here we go according to xlup Edwin Malone is from the east region right I'll go ahead and press Okay now because this is a table when you enter a formula in the top row here it's automatically going to fill down the formula for us so that's a major benefit here so let's press okay and let's see what happens so look at that we can see that we have all of the regions based on our sales rep well let's do a quick test

here's bernardet page is bernardet page from the Midwest region well let's find Bernardet here's bernardet yes so we can see our X lookup is working perfectly fine here and if we go to the end of the data set I'll press control end we can see it's inserted the region for all of our orders here as well so we're good to go let's go ahead and do the same thing here for the price right so in this case here is our lookup value it's going to be the carot product the look Array is the products the

return array is the price so we already have the information we need let's go ahead and plug that in here so X look up one more time look up value first product on the top row look up array all of my products from my product table the return array my prices from the product Table and there we go it's Already giving us our price which is $22.95 and if we locate the carada product we can see that that is indeed $22.95 so X lookup is taking care of that for us let's go ahead and press

okay and just like with our region adds the prices for all of our products here as well so we're good to go last thing we need to do is just to calculate the sale so we'll just do a simple calculation we'll multiply the units times the Price and to get our sale here so I'll click on sell M4 I'll say equal and I'll click on the units here and multiply that times the price press enter and there we go we have all of our sales so that's a real world use of X lookup go ahead

and insert the region column go ahead and populate the region column go ahead and populate the price column and go ahead and calculate the sale and come right Back let's go ahead and convert our order form into a pivot table and let's go ahead and analyze the data so because we're working with a table we can actually click on the table design Tab and we can use the command to summarize this with a pivot table so I'll go ahead and click here and I can actually place this on an existing sheet I do have a

sheet here called Advanced pivot table it's empty right now so I'll go ahead and place This there so I'll click on here I'll head over to Advanced pivot table and I'll go ahead and place it over here in cell A2 and I'll press okay so here is my pivot table now that I have my pivot table up and running I can go ahead and build out my pivot table now this is the default view for the pivot table so it's stacked we have our Fields up here on the top and we have our area boxes

on the bottom I like to click on the little gear icon here And just change the view and also sort my fields in alphabetical order as well so I'll click on the gear icon and I'll go ahead and choose side by side I'll also go ahead and sort A to Z just a matter of preference so there's side by side and now I'll go ahead and sort my Fields A to Z going to build out my table here I'm going to go ahead and drag the sale into the values Box and right here I get

my sum of sale so I have my sum of sale in here so by default it wants to summarize the values that I place in this box here now I don't have order IDs in here so what if I wanted to not get the sum of all of our sales which is perfectly fine but what if I want maybe the count of the sales right so how many transactions did we actually have so to do that we're going to Display the value in a different way instead of using the sum we're going to use the

count instead so to do that I'll go ahead and pull up my value field settings I can click on the drop down here in the actual area box here is my value field settings I can also right click on the number here and I can also get to Value field settings here as well so either way I can come in here and get that Information I'll click on the dropdown value field settings so notice right now we're summarizing the value field by the sum if I change this to the count right and now I can

also change the custom name that appears here as well so I want to know how many transactions did we have and I'll change the name here as well and I'll just call this one transactions I'll go ahead and press okay well before I do that I want to get Rid of the decimals so I'll click on the number format here on the bottom left and I don't want any decimals I'll go ahead and press okay and if I press okay again this tells me that I have have 10,000 transactions which is correct because we had

10,000 transactions on the original data set so now that I have 10,000 transactions now I can get some information such as how many transactions per region so we can see that we had most of our Transactions in the Midwest region 3,63 and we had fewer transactions in the South as well right of course I want to go ahead and move things around a little bit maybe I want base on region and product now I can go ahead and be a little more precise we had 125 transactions of the Aspen product in the east region here

as well so go ahead and insert your pivot table go ahead and add the sale to the Values check change it to summarize by the count function and create a couple of visuals and come right back let's go ahead and reset our table and let's take a look at the calculated Fields Advance option so we want to create a calculated field and we'll see why let's go ahead and reset our table just going to go ahead and put the sale back in here here and I'll just switch over to the Accounting [Music] format so now

we have the sum of sale well I want the sum of sale by sales rep so here are all my sales reps and we can see all of their sales here as well now what I would like to do is calculate the commission so let's say we're giving a 10% commission based on sales now I don't have a field over here that's called commission so how can I Calculate the commission well one thing that you can do just temporarily is to you can create an outside formula so if I say you know commission here I

can go ahead and enter a simple equation so I'll say equal the sum of sale times 10% I could even use the cell reference if I want to if I press okay okay here's the commission for alen Ramos and now I can go ahead and autofill down and this shows me all of my commissions for all Of my sales reps so that's one way that I can do that but this is temporary and the reason I say this is temporary is because as our pivot table expands it's going to overwrite any data that's in that's

over here right so if I just kind of let's say if I put my region into the columns it basically wipes that data out right and notice that these cells are no longer just the overall values so it's kind of a temporary solution so what can We do here instead going to remove the region the other option is to go back to the original data set so if I go back to joining list what I can do is insert another header over here that's called Commission and this will calculate the com the commission based on

the current sale then I would need to just go ahead and make sure all of my formulas are added here as well then I would need to go back to my pivot table refresh the pivot table so that we can display the Field for that new header well we don't need to do that we don't need to create a outside formula we don't need to even add a field to the original data set what we're going to do is create a calculated field to do that I'll click on pivot table analyze and over in the

calculations command group we have Fields items and sets if I click on this drop down I'll see an option to create a calculated field I'll go ahead and click here so There we go right so I'll go ahead and click on calculated field here's my insert calculated field dialogue box I need to do two two things I need to First go ahead and name it and then enter my formula once I'm done I can go ahead and add this to my field list here and we'll see how cool that is we can add a field

here and use that at different places in our pivot table then I can go ahead and Press okay and this will display the field in column C for me and display all the values here as well so I'll go ahead and call this [Music] commission now as far as the formula we can use Simple functions in here we cannot use anything too complex if you're looking for more advanced features go ahead and click on the question mark here this will open up a Microsoft article that will show you just some of the limitations in terms

of what we can do but the purpose is here is just to show you how a calculated Field Works so for my formula I'll delete that zero so we can run calculations on columns so in this case I'm concerned about the sale so I'll go ahead and insert the sale field here so it's going to be the sale times 10% and that's it so it's called Commission the formula is equal to the current sale times 10% I'll go ahead and click add and notice when I clicked on ADD I now have a new field that's

called commission I'll go ahead and press okay and now we have a field here it's sum of commission and it's giving me all the commission follow of my sales reps if I go to the bottom here's the grand total of all commission and that's correct because 10% of 915,000 is Basically 91,500 so that is how we add a calculated field now if you want to change that let's say we the commission rate changes to 11% this is the benefit of using the calculated field I simply need to go back to pivot table analyze and I

can click on calculated field here I will click on the drop down here locate the calculated field that I want to Modify and I'll just change this to 11% I'll click on modify I'll click okay and you can notice in real time all of my values have been updated here so 11% of 54.97 yields 60467 so that's a really nice way to create another field that you can reuse in our pivot table now here's the benefit of it what if I wanted to go ahead and calculate the commission based On region well I'll go ahead

and let's see I'll go ahead and add I'll remove my sales rep here so as you can see now here's the total commission if I want commission based on region here are the commissions that are paid out based on region so just like with our fields we can reuse them at different places in our pivot table go ahead and create a calculated field of your choice and come right Back now when you're working with your pivot table we natur want to be able to filter our pivot table to display the values that we're looking for

now if I click on the pivot table analyze tab we have some more advanced filtering so remember we do have the row and column label filters that we can interact with we also have our filter box over here as well but we're going to take a look at two types of filters one is the slicer and one is the Timeline so I'm going to go ahead and and remove the commission here I just want to focus on the sale based on region and also based on product so I'll move the region to the columns here

and I'll put the product into the rows so here's our simplified condensed table showing all of our transactions by region and product as well so here are my two here my row label filters I can filter by product here's my column label filters I can filter by region well I Want to insert a slicer for that so on pivot table analyze let's first insert our slicer and let's see how our slicer behaves I'll click here notice we have a slicer for each of our Fields here we don't have one for commission though because it's a

calculated field I want to go ahead and insert a slicer for the product and want all so for the region I'll go ahead and press okay here are my two slicers I'm going to go ahead and move them around I can resize Them I have quite a few products so this slicer is a little longer so I can actually display this Slicer in two columns but before we do that let's see what our slicer can do so if I'm presenting this data and I'm asked about a particular product if I just click here let's say

on the sunset product notice how it filters my pivot table it's only displaying the values in each region for my Sunset product pretty cool well what if I want To focus on the Sunset and the Aspen product if I hold down the control key and click on Aspen now I'm showing only for the Aspen and sunset product so notice how all of these numbers all 15 of these cells have been recalculated based on that filter I can take it a step further what about the Aspen and sunset in the east region there we go so

that's what our slicers are for again you can select multiple by Holding down the control key and clicking because by default you can only choose one value at a time there's another way that we can select multiple if we take a look at the top right of our slicers we'll see a little tool here this is the multi- select tool this changes the mode so that we can select multiple items going to go ahead and clear my filters so I'll clear my slicers here to make sure everything is selected I'll Click on that clear filter

icon everything is reset going to turn on the multi- select tool for both of my slicers and now I can select multiple or in this case unselect what I don't want so because everything is selected I can pretty much unselect what I don't want so if I just want Aspen doublers and the sunshine I can go ahead and do that right for my regions if I just want The East and the South regions there we go so our slicers allow us to filter our data that's being displayed on our table now for our slicers I'll

go ahead and reset them again or because it's a filter we have the clear filter option here now our slicers comes with its own contextual tab so if I click here I can see slicer appear at the end of my ribbon and I can do things like change the name of the slicer I can choose a slicer style I can Even choose how many columns I want because my product slicer has a really long one column here I'd prefer to have maybe two columns right this way it doesn't have to be that long so I'll

go ahead and change this to two columns instead of one let's see if that looks better there you go so there's two columns now it's not as tall I just need to make it a little wider so this gives me a little more control now I can stack them like This I'm good to go we can change the colors there we go so those are the slicers right so if you click here we can change the name of the slicer here are the different slicer settings that we can interact with again just some basic information

here by default it's sorted A to Z here's the caption option here's the name right if you do have blank values that's showing up you can always choose this option that says hide items with no data this way you will not See a blank button on your slicer as well so that's it for slicers go ahead and insert a few slicers go ahead and play around with them click on the buttons and check out how your pivot table is being updated to delete or slicer just just click on it press the delete key and we're

good to go let's take a look at the Timeline going to move my slicer down here to the bottom left and let's go ahead and insert a timeline so I'll click back on The pivot table analyze tab here and here is my timeline right next to my slicer I'll go ahead and insert that now the timeline is a very powerful tool because a lot of our data is based on time time such as quarterly reports or monthly reports or yearly reports a timeline takes a lot of the guess work out for us and it's a

very efficient tool it's a very simple tool as well so we'll take a look at it here I'll go ahead and click on insert timeline and Notice the timeline tool looks for a properly formatted date field so make sure that you have a properly formatted date field otherwise you will not be able to use the time line I'll go ahead and click here and I'll press okay here is our timeline right now the way our timeline works I'm going to change the color just so we can have some contrast here just like with our slicer

we have a timeline tab here we Can do things like rename it and we can go ahead and change the color I'll just make it Orange right so here is our timeline the way the timeline works is we have a hierarchy so we have years we have quarters we have months and we also have days to the top right of our timeline we need to First decide how do we want to view our data do we want to view it by month year or by quarter let's start at the highest level I'll go To years

here so as you can see we have years quarters months and days days if I click on years so it looks like I have transactions covering 2019 2020 and 2021 so if I click here it's a little time capsule if I click on 2019 we can see we had looks like maybe just one transaction so we had sales in 2019 of $798 well what about 20120 here we go that looks better so in 2020 so all this information here that's Being displayed this is based on the information for the year 2020 well if someone says

well what about 2021 did we do better did we improve from 2020 and we can say yes we increase sales by $115,000 from $450 to 465 so that is pretty cool so no need for us to write a lot of functions and formulas the timeline is giving us this detail here let's go ahead and change that I'll go to The Quarters here so I'll click on quarters and we have several Quarters here now because we're we have three different years of transactions so now I need to go ahead and click on my timeline and just

kind of make it wider so I can see the quarters for my different years here as well right so here's 2019 looks like we only had one quarter for 2019 let's see yeah so for 2019 quarter 4 $798 how did we do for quarter one in 2020 quarter 1 in 2020 sales of 41,600 all of my numbers are updated right away what about quarter 1 for 2021 here are all of my numbers so that is a pretty pretty nice tool to be able to use here the first half of 2021 sales of 69360 finally we'll

take a look at months we can drop down to days as well That's a little granular I'll just go down to months so here are my different months again you may need to just readjust your timeline so that you can see all of the data I'll just focus on 2021 here so in January of 2021 here are our sales in February March April so forth and so on so that is a really nice tool to get some real-time data on your data set by using the timeline I'll drop back down To the years here I'll

go ahead make this a little smaller so I can see I'll go ahead and choose 2020 as my target so that is the timeline highly recommend you use that especially if you're analyzing your pivot table based on time-based information or if you're presenting your pivot table as well let's take a look at report page filters I'm going to go ahead and remove my slicer here just make sure it's reset I'll go and remove that I'll go ahead And clear the filter from my timeline here as well then I'll go ahead and remove my timeline and

let's go ahead and take a look at what's called report page filters right so we'll take a look at that it's another type of filter that we can use so what I'll do now is let's go ahead and focus on our regions so what if I wanted to do something like get a separate pivot Table for the east region that I can manage independently right so build me another pivot table for the East also build another pivot table for the Midwest one for the South and also one for the West right and by the way

go ahead and put them on their own worksheet tab so I can kind of just analyze them independently They're still connected to the original data source but not necessarily to this pivot table here as well so those are called report page filters so how can we get Excel to create to insert four worksheets here with a pivot table on each of the worksheets for each of our regions well let's take a look the first thing we need to do is we need to introduce our region into the filters so here's our filters box over here

we need to go ahead and Introduce our region so if I wanted to have different worksheets based on product I can do that or sales rep but right now regions I only have four so it's a perfect candidate I don't necessarily want 75 um worksheet tabs for my sales reps so I'll go ahead and drag the region in here and then I'll run the report page filter now before I do that let's see where we can insert insert report page filter I'll click on the Pivot table analyze Tab and all the way over on the

left under the pivot table command group if I click on the drop-down here is the show report filter Pages you can see that it's grayed out and the reason it's grayed out is because we are missing a filter field here in this box so as soon as we drag our region in here we'll notice that this will become active and we can go ahead and run the Report and it's going to do exactly what we wanted to do so let's go ahead and drag the region into the filters here now we clearly have the region

and the filters I'll go back and I'll click on show report filter Pages let's see what happens I'll click here and notice it's asking us to show report filter pages of anything that we have in the filters box so I'll go ahead and press okay and I'll scroll over a little bit Here so that we can see it a little better we can see that Excel inserted a worksheet for the East so here's a pivot table for the east region we have one for the Midwest one for the South and one for the West as

well so these tables they are behaving autonomously they're behaving independently they're not connected to my other pivot table they are connected to the original data set but no longer connected to this pivot table here as Well so for example here in the East I can rebuild this table to the way that I want it to be right so maybe I'll go ahead and put the product in the columns I'll put the sales rep into the rows right and now we can have our detail here so this is all the east region if I go back

to my original pivot table I can see that it's not implementing those custom changes here as well so why would you want to do that well maybe you just want to go ahead and analyze data for The Midwest region without you know moving things around on the original pivot table so this is kind of like a drill down or drill through report that you can use as well so that is how we can insert the report filter page to finish up our practice file here we'll take a look at the very last worksheet which is

text functions now actually what we're going to be using is a function it's my favorite tool it's actually tool but it operates like a Function as well and this is called flashfill so again flashfill is a really nice tool it allows us to do things like what we would otherwise use concat or concatenate or text join or text split or text to columns so it's a combination of all of those different text tools that we can use in this case what I want to do I just want to go ahead and focus on column D

column G column I and column J and basically what I want to do first I want to go Ahead and combine the rep first name name into one cell so to do that I'll go ahead and click on the data Tab and Flash Fill is on the data tab over in the data tools command group here is Flash Fill little lightning bolt going through a table that is flashfill if you hover over it tells you automatically fill in values enter a couple of examples you want and Excel will fill it in for you we can

also press contrl plus e but Let's go ahead and watch it work we don't necessarily have to click on it let's go ahead and enter the rep full name and column D so I'll go ahead and type I'll just say Cara Olsen and I'll press the enter key now I don't feel like typing this everyone's name here so let's see if Excel will pick up on the pattern for me so I'll go ahead and start typing here for Anthony and notice as I'm typing Anthony's name here it's automatically recognizing the pattern right that I want

the full string value that's in column B add a space and add the full string value that's in column C here's a preview of what it would look like so yes I do want that so to accept that I'll press the enter key I can always test to make sure it's working fine I'll press the enter key and here we go so if I go all the way to the bottom I'll check that last one here here is Jerry ER and so pretty good so that works well as soon as you run Flash Fill we

get a little Flash Fill options here can do some basic things like undo the Flash Fill if it did not pick up on the pattern properly or I can go ahead and accept the suggestions I can also go ahead and select all 41 change cells I'll just accept these suggestions here did a pretty good job so basically Flash Fill Looks to the left senses a pattern and then it offers to complete that pattern for you now you'll be surprised to see what types of patterns flashfill can actually pick up on well let's head on over

here let's see if we can extract the actual month from our date here right so the month here is January so I'll go ahead and type January and let's run flashfill this Time so I'll click on the flashfill icon here and let's see what happens and look at that so it's extracting the month from this particular date for me right now this only works because we're using this type of date format if we just had a short date with the numbers in here that would not extract the actual month for us right so that works

well I'll go ahead and accept those Suggestions okay now what about this one in column I I want to extract the city here so the city is Boulder so I'll type Boulder the next city is Salem so as soon as I start typing Salem I can see it's offering Flash Fill for me I'll go ahead and press enter and I'm good to go I'll just quickly take a look sometimes when you run Flash Fill it doesn't get the pattern right away very Rarely will that happen if you have a more complex pattern right basically flashfill

will still will tell you you know I did not find the pattern for you go ahead and enter a few more examples until I can figure it out and I'll offer you the suggestion there as well one thing to note sometimes this will happen sometimes instead of Bowling Green we'll just get bowling in here right so sometimes it will emit the second word what's cool with flashfill if we see That error if we add if we update the cell to show Bowling Green it's going to go ahead and update all of the other cells here

that has you know cities with two names so you can train it even after you enter flashfill which is pretty cool okay let's do one more let's go ahead and enter Co for Colorado this time I'll use the shortcut so contr e so you do have to click on the next Blank cell to run flashfill I'll press contr e mission accomplished there we go I'll Go ahead and accept these suggestions here and I'm good to go so that is flashfill really nice tool to improve your data set in this case we were able to have

the rep's name in a full column here we're able to extract the city and the state so now we can run reports based on city and state we also extracted the month didn't need to do that because we have the timeline for the pivot table as well but if we're using another type of tool we can Extract the month here as long as we're using the full format here the actual spelling of the month for the date format so go ahead and play around with flashfill go ahead and throw flashfill at some other data sets

that you may have and we'll wrap up here with this worksheet welcome to module 2 now in this module this is what we'll be covering we'll be taking a look at array functions we'll also take a look at Advanced conditional Formatting we'll take a look at Advanced charts sometimes maybe we just have too many fields in our data set and trying to get the chart to look exactly how we want it to to display the data can be a challenge so we'll take a look at Advanced charts or combo charts and then we'll have some

fun creating macros that can automate and repeat tasks for us in Excel go ahead and open up the practice file for module 2 and let's dive in let's take a look at some array Formulas now we just have a small data set over here to the left our little fictitious company payroll information first name last name employee number hours hourly rate we're missing the gross pay well let's take a look and see how arrays work we may have heard range cell array well well how does an array actually work well let's take a look we're

going to go ahead and use the array capability to return what's currently being displayed in an array so For example we can link to a cell so what if I wanted to link cell I5 to whatever is in cell B5 well I can do that to do that I'll click on my cell here and I'll say I want this cell to be equal to whatever is in cell I5 I'm sorry B5 so it's simple equals B5 if I press enter it displays the value that is in B5 if I change this the value in B5

to Davis it's dynamically linked so it automatically changes to Davis I'll go Ahead and put this back to Jones so that's linking to a cell well what else can we link to can we link to a row range well let's see I'll go ahead and click here on Cell K5 and I'll say equal I'll highlight the last name and the employee number so equals B5 to C5 well let's see how that works if I press the enter key notice what it does it actually places the last name in one column and the employee number into

a Another column so interesting well what about linking to a range in a column right so what if I wanted to go ahead and Link let's say create a link for the first name so I'll go ahead and say equal and I'll highlight all of my first names here from Sarah down to Robert so it's equals A5 to a19 if I press enter I can see it returns that array for me now what's interesting if you Take a look at the formula bar here you'll notice that the formula is grade out so it says equals

A5 to a19 but it's gray out if I click on the top one here we can see that this one is accessible so when you're working with arrays it's that first cell that allows you to change the data that's being displayed in the array as well we'll notice we have a kind of a blue box around here indicating that we have inserted a dynamic function so we get this blue Indication here also we have that same blue for the linking to a row range so this is just to kind of see what we can do

how Excel links ranges when we enter those formulas well let's do something a little different here let's go ahead and use the ersan to link to two arrays so what if I want to go ahead and Link or combine the last name and the employee number into one well the %an on our keyboard is an actual Operator so for example if I said equal this last name here and the employee number so equals B10 n C10 it basically concatenates that for me as well now we just want to go ahead do it on a bigger

level instead of doing one cell to one we want to do multiple cor so 15 rows two columns so let's see I'll click on P5 and I'll type the equal sign here I'll highlight all of my last names so it's equal to B5 to b19 And I'll go ahead and grab all the employee numbers I know looks like a really weird formula right so B5 to b19 and C5 to c19 let's see what the output is going to look like over here right so I'll go ahead and press enter and look what it does so

it concatenates the entire array for me right so now we have pairs last name employee number last name employee number as well we'll see how this will Come into play when we are trying to return multiple values based on you know multiple input using X lookup let's go ahead and multiply these columns here so if I want to go ahead and multiply the hourly rate well let's see what happens I can just say equals 35.5 * 30 and I can do that then I can autofill down that gives me my answer so that works but

let's see if we can save a few Steps here we'll go ahead and click here and now I'll just say equal all of my hours and I'll multiply that by all of my hourly rates so it's D5 to d19 multiplied by E5 to e19 let's see what happens here look at that so does the same thing but this is an and array so these are just some of the different things that we can do we just want to be able to practice linking cells and ranges and arrays so we can See how this can benefit

us here as well so go ahead and perform these tasks link to a cell link to a row range link to a range in a column use the Ampersand to concatenate two arrays and come right back what does arrays have to do with X lookup well let's take a look at our course offering here we have several applications we have Excel word Outlook power query powerbi and power pivot but they all have different levels so we can choose Excel introduction intermediate And advanced likewise word introduction intermediate and advanced Outlook we only have introduction and advanced

we have lens we have unique prices based on the actual application and the level so if you were to create an X lookup kiosk here so what if we wanted to create a little kiosk so that our students can look up the price for a particular course you want to look up the price for Excel intermediate so I'll enter my X lookup Function here to get the output price so I'll click on my formulas tab I'll go ahead and click on insert function here's my X lookup so what is my lookup value so already I'm

stuck here because my lookup value cannot just be Excel it needs to be Excel and Excel intermediate I don't have lookup value two here I only have lookup value so how can I express that I want to look up pairs so I want this course Excel and Intermediate which is $250 so how can I do that well the keyword is and and remember we learned on the array formulas tab here that our Mand is an operator so what if I do this what if for the lookup value I'll click on B9 which is Excel and

intermediate so now what I've done is I've created a lookup value that's called Excel intermediate So this is concatenated into one here so the next part of X look up we need to for the lookup arrays where can we find this Excel intermediate combination well for the lookup array I can't just you know highlight the application because it just says Excel Excel Excel word word I need to be able to say Excel intermediate so what I'm going to do is I'll say well my lookup array is going to Be D2 to D15 and the course

level so the application and the level now let's see if this makes sense so my lookup value is B9 and B10 which is Excel intermediate and my lookup array I'm looking for a match for Excel intermediate we'll take a look at the preview here look at my first pair Excel introduction which is right here look at My second pair Excel intermediate which is right here well this matches my input or my lookup value so that should work for me last thing I'll do is this I want to return the price so I'll highlight my prices

here and look at that it's already giving me my price it's telling me that the Excel intermediate course is 250 and that is correct because here is Excel intermediate it is indeed $250 so what we need to do is combine The input names using the m% we also need to combine the column names using the m so in this case application and level and so the Excel is just storing that array for us so that we can use it over and over and over again in our X lookup function so if I click okay here

right so again it's already locked onto the arrays the arrays are here we can't see them but Excel has stored them for us so now if I change this to let's Say word intermediate it updates right away word intermediate is $200 what about word Advanced 300 there's several other ways to do this but in my opinion this is the most efficient way to look up multiple values and return a particular value while using X lookup let's take a look at some really cool array functions so we have a list of store names here we have

15 to be Exact but they're not unique you can see we have Safeway appears a few times Whole Foods appears a few times and so forth and so on so what if we just wanted a unique list of these store names this would be very helpful because if we had 500 rows of data it will be nice if we can just go ahead and get a unique list right so one of the ways that we can do that is by using a function that was introduced in 2020 we'll take a look at the unique Function

as an array function so the unique function I'll go ahead and just insert it right here into the cell so create a separate unique list from my list of 15 stores here I'll click on Cell C4 I'll type the equal sign I'll introduce the unique so it Returns the unique values for from a range or array I'll press tab to activate it and I simply just need to go ahead and highlight all of them I'll go ahead and close my Parentheses right these others are default so it's going to go ahead and by default it's

going to sort A to Z if we want to sort it in another direction we can enter some more parameters here right so here are if we want to so it's actually sorting by column by default and it's also going to sort A to Z so if we want to change these parameters we can change them here as well in this case we're just sorting exactly as is unique list from A4 to a18 if I press Enter so I can see here that even though I have 15 stores entered I only have eight unique stores

here as well so that is the unique function fun so pretty cool now what about the sort so here's another sort another array function this is the sort function so I can sort this unique list by using the sort function now again this is a function of the sort tool so this is pretty cool so we'll notice we have a Lot of array functions now that accompany an actual tool so here's the sort tool so I'll use sort I'll go ahead and press tab now we can see we have several arguments here so we have

the array we have the sort index we have the sort order and we have by column well in this case I just want to go ahead and sort this particular list A to Z so I'll go ahead and highlight them I'll close my parentheses And I'll press enter and there you go so it actually sorts the list for me in alphabetical order if you want to take a look at more advanced features or more arguments if you click on that cell that has the actual function in here just click on the FX to the left

here that will pull up the function arguments box and we can take a look at it and we can see that we have some other options in here so for example only the array is necessary the Sort index to sort order and sort by column these are optional but it's a number indicating the row or column to sort by so if we had a several columns and here as well we would need to specify the number of the column based on the position of the First Column here and then the sord order right so one

is for ascending minus one is for descending and if we were want to sort by row so by default it's sorting by Column if we want to sort by row we can change that here as well but in this case it's working fine for me now what I can do as well I can go ahead and accomplish both of these with just one statement so what if I wanted to go ahead and sort this list so make it unique and sort it as well so I can eliminate these two steps I can perform them with

just one step here so I'll do the sort function first So go ahead and sort I'll press the Tab Key here I want to sort the unique version of this list so I'm sorting the unique version of A4 to a18 just make sure I have enough closing parentheses here so sort the unique result of my store names here if I press enter I get the same list as here as well so we can Nest array functions which is Pretty cool as well so really nice option here to use here's another one it's Rand array so

Rand array is just a we can generate a list of numbers or values or dates that we can use for further analysis what if I wanted to generate some numbers here I'll use Rand array and for this one I'll use all of the arguments here's the Rand array function so it just returns turns a an array of random numbers I'll press the Tab Key I need to specify how many rows I want how many columns I want what is the smallest number what is the highest number and do I want a decimal or a whole

number well give me 10 rows and give me three columns the smallest number is going to be 100 highest number is going to be 399 and if I press comma again here I do want decimals so I'll choose false here I'll close my parenthesis and press enter and here's my random list of Numbers that can be generated so it's a really nice function when you just want to test something maybe you're testing a particular formula to make sure it's calculating things correctly or maybe you're just playing around and you need some numbers to actually run

some simulations on as well so pretty cool one thing to note though after you create the random array things will move around the numbers will change if I type a number next to it and press the enter Key so it's volatile so they all kind of change here as well what you can do is just right click copy those numbers and I'll just paste the values if you want to work exclusively with those values as well so those are some of the array functions go ahead and check them out and come right back let's take

a look at three examples of how we can use advanc conditional formatting we're going to go ahead and use an input cell to display what The values on our table here we'll also create another conditional formatting rule that will highlight the entire row based on a condition and then we'll create our third row rule which will highlight the rose as long as the wine color is white so we have the transaction number we have Representatives we have States we have the date in here as well we have the type of wine the group the part

the sales the margin and the Quantity as well now conditional formatting is a really nice way to filter your data to give you some visuals on your data set so for example if I were to highlight all of my sales here here and let me go back to the top here so we have quite a few so if I were to highlight all of my 305 cells there under the Home tab conditional formatting has a lot of really nice visuals or a built in that will help me To kind of just quickly visualize my data

get some insights we have top and bottom rules well what if I want to just display all of my sales that are over a certain amount so I'll say greater than right so show me highlight all of my cells with a light red fill with dark text that are over let's say 150 as you can see in real time as long as it's over 150 it's being displayed Here as well but I don't feel like coming back and clicking on conditional formatting and changing that number so what I can do because I have this little

up Arrow here this reminds me that I can refer to a cell reference so this cell is going to be my little input this will control the conditional formatting that's being displayed on my little pure wine distribution here so I'll delete that 150 and I'll go ahead and click on this Cell right the cell currently has 200 in there so it's equal to L4 I'll go ahead and click okay and there we go so now if I change this number so if I change my mind only 150 more than 150 look at that in real

time it's showing me all of the information here as well so that's one way that we can just kind of use conditional formatting to our advantage we can use a cell to control what's being displayed here as Well okay now what if we wanted to do something else notice right now it's only highlighting one cell right it's only highlighting one cell what if we wanted to highlight the entire row so as long as that particular in this case H5 as long as it's greater than or equal to 150 go ahead and highlight it here well

we have to create a rule for that so I'm going to go ahead and highlight because we want to highlight the entire row we need To select all of our records so I'll go ahead and select all of my records first and I'll click on conditional formatting now I don't have a rule that's built in for that so I need to go ahead and create a new rule so I'll click on new rule and the rule that I want to use is I'm concerned about the value that is in cell H4 so as long as

a value that's an H4 is greater than or equal to 150 I want to Go ahead and highlight the entire row so there's a lot here here's the world of conditional formatting well what I want to do is use a formula to determine which cells to format so on the bottom I'll go ahead and click on Cell H4 which is my first one here and I'll say is that greater than or equal to 150 if it's greater than equal to 150 I want a I'll go with a I'll go with a light Blue fill color

here now the only thing we have to change is we have have to change this right now it's locked on to H4 well if you take a look over here to the right you'll see I have written the formula for you that we need to enter notice we're absolute referencing the column but not the actual row right so we're locking on to column H but we're allowing Excel to evaluate each row here as well so to do That we'll remove the dollar sign before the four and here's our simple formula equals dollar sign H4 is

it greater than equal to 150 I'll go ahead and we can actually even copy and paste that in there but I'll press okay and let's see if this works so here we go so as long as it is 150 it's greater than equal to 150 it's highlighting the entire record for us which is pretty cool right let's try one more this time we'll use this formula to Go ahead and check to highlight the entire record based on the color of the wine I'm going to go ahead and delete this conditional formatting though so I'll click

on clear rules I'll clear rules from the entire sheet I can also go to manage rules for the entire worksheet and I can see the formulas here that we can use right so I can go ahead and delete them I'll go and press apply and I'll press okay I'll do the same thing as Before I'll go ahead and highlight all of my data and this time we'll do a combination of both we want to use a create a new rule based on a formula this formula has two parts we're going to use cell L1 as

the input so we can type either red or white in here and that will highlight the entire row for us so we're just going to use this formula right so I'm going to go ahead and evaluate E4 which is our first Type of line and I'll say is that equal to l11 now just like before we don't want to Absolute reference the row here so I'll remove the dollar sign before the four this should be l12 not l11 for the format I want it to be a light blue color as well and I'll go ahead

and press okay and let's see if this works here let's just make sure that this is l12 let's Press okay and there we go so this should be l12 so now if I change this to Red let's see what happens so look at that so now it's highlighting all of my Reds for me so really three really nice uses of conditional formatting the formulas are here for you go ahead and plug those in and come right back let's take a look at charts here so if you have ever inserted an Excel chart We know that

we have what's called recommended charts and depending on how we have our data situated we may or may not run into some issues when we are actually inserting a chart now I just want to go ahead and create a quick chart this is a really really nice data set to create a chart from so if I press alt F1 on my keyboard that gives me the default clustered column chart here that I can work with and as you can see it's working really well we can see all the Data everything is in good proportion and

we are good to go really this works well because we just have one scale for our sales but the problem happens when we have another scale maybe for the total or maybe for the unit so we can see our sales here they go up to you know from 0 to 1,600 on the vertical axis here as well but what if we wanted to express some other values that were on the range of let's say 0 to 500 will be a little difficult we'll maybe need to do something special and that's called a combo chart so

let's take a look at our next example and we'll insert a combo chart let's head on over to combo charts so here's an example so in this case we have the month we have the unit sold we have the sale and we have the profit so we already can see that we'll have some issues because our units sold they range from looks like about 45 up to about 220 so we'll have a let's say 145 to 2 20 and then we have our sale which ranges from 855 to about 4,000 so we can see we'll

have a different type of scale for our sale and then for our profit right our profit is larger than the unit sold but not as high as the actual sale so going to have some issues here trying to represent these values so let's see what happens let's see what happens if I insert just the default Chart here so I'll press alt plus F1 on my keyboard and let's see what it gives me okay so let's see so it gives me this chart here I'll make this a little bigger see if we can see it so

the chart that it gave me it's a clustered column chart as well so here's the cluster for January the blue represents our unit sold 130 the orange represents our sale which is 2470 and then the gray represents our Profit which is 500 so we can see right away that we can pretty much make out our sales here we can see them we can clearly read the values based on our axis over here as well however for our profit we can probably make it out we can see this is close to 500 here so we can

probably make them out but it's still just a very small portion of our chart right from 0 to 1,000 and then if we take a look at our Actually unit sold that's where the real problem comes in there's no way of me really knowing this is too small a scale to determine if this is 100 or so forth and so on so what can we do well what we need to do is to convert this to a combo chart I'll click on my chart here here are my two friends the chart design and the chart

and the format tab I'll click on the chart design and I want to go ahead and change The chart type so I'll change the chart type and I'll pull up the combo chart that will give me the opportunity to kind of customize the chart to display what I want to I'll have a nice preview before I actually close it as well there we go I'll click on change chart type now I can take a look at different charts here none of them are really working well on the very bottom I'll click on combo Chart so

when I click on combo chart we can see kind of did something here it's showing my profit as a line but that still doesn't help me so maybe what I want to do is maybe I want to show my sale on a secondary axis so I'll click here so now I have two axes right on the left this pertains to my my unit sold and my profit over here to the right this pertains to my actual sale so right now this is working pretty Well I can read my sale I can read my profit but

my units sold are kind of getting lost here so maybe what I'll do is I'll change that to this is a cluster column maybe I'll go ahead and change this to a line okay so that's what that looks like but still don't like the way that it looks right if I press okay this is what it looks like here so I'll just keep chugging away I'll click on change chart type type again I'll go Back to combo and maybe I'll express my sale as a line and everything else will be a clustered column so I'll

just keep playing around until I get the combination that works well for me so I think I'll settle for this I have the unit sold as a clustered column The Profit as a cluster column the sale is a stacked line on the secondary axis so let's press okay and let's see what That looks like so it's a lot better but if I take a look here on this goes from 0 to 1200 I don't necessarily need for it to go that high because we only go up the profit goes up to about 980 so I'll

double click here open the pane over here on the right side and I'll go ahead and change the max maximum to 1,000 instead so that goes to 1,000 for my profit this makes it a little easier at least for me to see my unit sold right So depending on how you feel comfortable we can use the combo chart to kind of move things around and we can take a look at the ad advanc formatting options here as well to just kind of change the ranges for our data so go ahead and insert your recommended chart

go ahead and change your chart type to a combo chart go ahead and move things around until you get the look and feel that you want for your chart as well let's go ahead and record some Macros now what exactly is a macro well a macro is a tool that Excel uses to record user actions and or commands now an action could be something like clicking on a cell right or using the arrow keys to move around so these are different actions here as well you can also press enter you can press tab something like

entering text in a cell and a command is something like making it bold giving it a color Changing the font type increasing the font size or maybe even applying some type of cell style to it as well so these are all examples of actions and or commands well you may find yourself doing the same thing over and over in Excel and in that case you want to be able to create a macro now everything that we do excel will record those actions and those commands and it will basically replay those actions and commands in Sequence

so we need to make sure that we're actually thinking about the sequence to make sure everything happens accordingly well the first thing we want to do let's go ahead and turn on our developer tab now to do that we need to get to the Excel options window here and we just need to go ahead and turn it on it's already here we just need to go ahead and turn that on so I'll go ahead and head on over to I'll actually right click on my home Tab and I'll click on the customize the ribbon option

here and as you can see already here I just need to go ahead and turn on the developer tab over here on the right side I'll go ahead and turn that on so again just make sure that we're on the Excel options head on over to customize the ribbon and over here on the far right we can see all of our tabs let's go ahead and turn on the developer tab and press Okay so I'll go ahead and turn that on I'll press okay here's my developer tab I'll go ahead and click on it here

so we can see we have a lot of the tools that we need in the developer tab we'll focus on the code command group where we want to go ahead and record a macro we can view our macro Library we can also go ahead and open up the Visual Basic Editor to take a look at the code and maybe even edit the code as well we can also do something like insert a Button from the controls command group to run our actual macro now to get an understanding of how our macro works I'll do something

simple here I'll just go ahead and type some text in here so if I go back to the Home tab and in the cell Styles so these really are some form of macros because these are all pre-programmed with a certain font type color effect and shade as well depending on what we use here so if I were to click on this I can see right now we have the aial 10 and it has a black text no shade look what happens when I click on one of these accents I'll go and apply the accent one

now if I click back on it we can see it's changed to Cali size 11 we can see that the color is white and we have a fill color on the background as well so those four or five commands were pre-programmed in here and that is a form of a macro so it saves us some Steps we don't have to change the font to Arial change the size to 11 change the font color to white change the fill color to that version of Blue there as well so go ahead and turn on your developer tab

and come back and we'll start creating a few macros now we'll go ahead and keep it simple we're going to create a simple macro so in this case we'll create two macros on this worksheet the first one that we'll create is a macro that always Places our signature in cell A1 so it's always going to place our signature right here and this is what I want the signature to look like it's just going to be my name so Mo Jones the name of my company and then I always want today's date so I'll use the

today function so I'll write equal today open parentheses close parenthesis so that's what it will look like I'll go ahead and press enter so this way always adds my name my company And the current date right so I always want this to run in cell A1 so I'll go ahead and record a macro now when you're recording a macro the macro always runs in the same position right so it's always going to run and cell A1 when you're recording macro it does use VBA which is objectoriented so you first have to select your object before

you run your macro now objects can be things like it can be a cell or it could be a range of cells it can be a table it Can be a chart it can be a sheet even a workbook so we'll take a look at the Visual Basic editor in just a little bit but there's a lot of of different objects that we can use in this case I'm going to go ahead and select cell A1 that's where I want my macro to run and I'll go ahead and click on record macro here's my record

macro dialogue box and I just have a couple of things I need to do I need to go ahead and give The macro a name and that's it these other ones here these are optional so I can go ahead and optionally give this a shortcut key I can optionally provide a description as well and we do need to decide where we want this macro to be stored either in this workbook which means we can only run this macro in this workbook if you want to be able to run this every time you open Excel we

need To change this to the personal macro workbook I have a lot of macros in my personal macro workbook I'm just going to go ahead and save it to this work workbook so here's the name now when you're naming a macro you cannot have spaces it needs to start with a letter so I'll just call this signature one just all one word here if I want to I can go ahead and assign a shortcut key for this first macro we'll Go ahead and assign a shortcut key now we want to be careful here when we

are assigning a shortcut key we want to use the control shift variant right so to do that I'm going to go ahead and turn on my caps lock now that I have my caps lock turned on if I simply type a letter in here notice how it converts it to control shift s so that's what I can do so again just turn on your caps lock and Type a letter in there I'll use the letter s for this one the other way you can do that is just hold down the shift key and type S

that will convert it for you okay I do want it to be stored in this workbook and for the description I'll just go ahead and add one here so I'll just say basic signature and I'm good to go now once I press okay it's going to start recording so I just want to be very deliberate with how I create the signature I'll go Ahead and press okay we're going to notice that the record macro icon here it's going to change into a stop recording so once we're finished we can click on there to stop recording

the macro I'll go ahead and press okay and now I'm recording so it's just waiting for me to enter some input now I already have cell A1 selected but just from experience I'm going to go ahead and click on it anyway and now I'll go ahead and start entering my data so Mo Jones I'll press enter type the name of my company and press enter and I'll drop in the today function now notice as we're interacting in a Cell all of the commands are gray out because right now we're kind of in between a task

so Excel is waiting for us to press the enter key or Tab Key to move away from the cell so that we can apply the actual Command right I'll go ahead and press Enter you can also press control enter so if you press control enter you will basically complete the command and this will give you the capability to add other commands as well in this case though I'll just go ahead and press the enter key and I'm done I'll go ahead and stop recording and so now I'm going to go ahead and remove this because

I want to see my macro running here I'll just click away from that cell I'll go ahead And pull up my list of macros we can Al also press alt plus f8 to pull them up and like I mentioned I have a lot of macros in here so here's my signature one that's in this workbook so notice here I can go ahead and run the macro which is what we want to do in a little bit we're going to go ahead and step into the macro and we'll also be able to edit the macro as

well we can also delete macros from here just going to go ahead and run It go ahead and keep your eye on cell A1 and you should see the macro running there so I'll click run and there we go so we did not even see it happening in sequence just kind of happened very very quickly right so every time I run that macro it's always going to run in cell A1 so that is how the macro works well I'm going to go ahead and create another one what if I want to create a macro that

places my signature based on my Selected cell well to do that we need to use what's called relative referencing but go ahead and create your macro go ahead and run it and come right back and we'll create a second signature here and we'll make a few more changes as well let's go ahead and create a macro that places our signature based on our cell selection so it makes more sense for me to be able to sign off on a document based on my cursor location maybe I want to go ahead and scroll to The end

of a particular document and then add my signature here as well so to do that I need to turn on relative references so right here underneath my record macro button I have used relative references if you hover over it kind of gives you a lot of information trying to explain how it works the best way is just to let's go ahead and record a relative reference macro and see how it works I'll go ahead and toggle that on so I can see it's turned on now I have a Nice border around it and it's highlighted

so now I'll just go ahead and click on any cell so whatever cell I click on that's where the macro is going to run I'll click over here I'll click right here on Cell F-15 and I'll record the macro I'll call this one signature [Music] 2 not going to assign a shortcut key this time I'll leave it stored in this workbook as well and I'll call this One signat signature uh with flex right so I have the flexibility to run this signature whereever want to I'll go ahead and press okay and I'll be good to

go now I'm recording I already have my cell selected so I can just start typing in here so I'll type my name I'll press the enter key and I'll put the name of my company now I do want to add the name of the company here I'll I'll press control Enter Because I also want to change or apply a cell style so if I press control enter notice how it places that that text in there for me but now I can perform additional commands before I move on to the next cell so I'll go ahead

and give it a quick cell style here give it that learn it blue now that I'm done I'll press enter to go to the next line and I'll go ahead and drop in the today function in here and I'll go ahead and press Enter and that's it I'm done I'll go ahead and go back to the developer tab and I'll click on stop recording there's also a stop recording button down here on the status bar but I'll click right here and I'm good to go well now it's time to go ahead and test out the

macros so I'll click on my macro Library here and let's see let's actually insert a new sheet and let's run that macro so I'll click on my list of macros here let's go Ahead and run the first macro signature one as you can see it always runs and S A1 based on the sheet that you're working on well what if I want to go ahead and sign off on I don't know g15 here I'll run my relative reference macro Signature 2 and as you can see it applies the style so it applies formatting we can

see it it it applies formulas as well functions so pretty cool now I did have a shortcut so I'll press control shift s let me Delete the content here from cell A1 so if I press control shift s there I can go ahead and run that macro that way as well so that's how we create a relative reference macro right we'll take a look at maybe some more uses of a relative reference macro but for now those are the two type of macros that we can create either an absolute reference macro which is the default

and then we have relative reference macros as well go ahead And create your second macro and go go ahead and just test it out and come right back now that we've created two macros let's go ahead and step into the macro going to go ahead and click on my macros Library here and for that signature one I'm going to go ahead and step into the macro so basically this will allow us to press the f8 key it's going to open up the Visual Basic editor for us and we can watch the macro run line by

line we Can also see the code which is a really good way to understand how VBA works I'll go ahead and click on step in two and here is my VBA window so here's the VBA interface we can see it still kind of looks like the Legacy Excel you know from Excel 2003 we don't have a ribbon we get the drop- down options here and there's a lot in here that we can use quick tour of this interface we have our project pane over here on the left so we Can see that we have the

actual workbook here's our workbook here are the sheets that make up this workbook as well now when you create a macro what Excel does it inserts a module and in that module is where we have our macros stored so here's my first macro so it says sub signature one so what that means is we're telling VBA visual basics for app for application in this case for Excel create a sub routine or a Subprocedure called Signature one we go down to the next line here we see some green text so these are actual comments so as

long as they're followed by an apostrophe these are just comments right and so the code editor ignored any comments so this is just a nice way if you have really long maybe 100 lines of code adding comments is a really nice way to just kind of help you to understand what's going on so I'll actually add a comment here so I'll say The first thing that we're going to do the very first line of code it says range a1. select well this is the way that we say to select cell A1 so I'll just say

select cell A1 so range a1. select I'll go ahead and press the f8 key notice how it jumps to that line we can see that cell A1 has now been selected the next line it says whatever the active cell is which is basically cell A1 formula R1 C1 equals Mo Jones so this formula R1 C1 is just the Legacy way of saying value so I can say so whatever the active cell change the value to mojones now if I want to change this I could as well if I just wanted to put my initials in

here I can do that so I'll just say MJ I'll go ahead and press the f8 key and we can see it places M MJ in there the next one we're going to go ahead and select cell A2 same thing formula R1 C1 equals learn it I'll put the exclamation point at the end here I'll press f8 so as you can see when we're stepping into the code we can actually edit the code as well and it's probably a really nice way to kind of check your code anyway you can watch it run line by

line and that way you can catch any any corrections that you need to make here as well okay so as you can see it's the same thing we select our object cell A1 and then we perform some Type of either method or change a value to that particular object as well so finally cell A3 we're going to apply the today function in there and then we selected cell A4 we actually did not need to select cell A4 so I can actually delete this last line here we didn't need to select an empty cell and we're

good to go so that is how that works I'm going to go ahead and click on another cell here let's Let's take a look to see how the other macro runs the relative reference macro macro so for this one same thing create a sub routine or a sub procedure called Signature 2 now notice the difference with the first line we can see the difference between the two here so for our first macro it is explicitly selecting cell A1 for our relative reference macro it's based on what you have selected so we're not saying you know

select a particular Cell whatever cell we have we're going to go ahead and put mojones in there so that's the difference between the two I'll go ahead and press the f8 key there we have Mo Jones and now it's a little different this is relative reference macro so it's recording the movement right so we're going down it says he's using the offset function here so we're going down one row but we're staying in the same column right and then we're going to go Ahead and add learn it in there and then we apply the accent

one style I can change this maybe I'll go with accent 3 see what accent 3 gives me all right so that light gray there and then we go down again drop in the today function and then we go back down again and then we end the routine here so this is VBA this is how it works you select your object you apply some type of Method to it or you change property or a value as we can see here as well so that is being able to step into the macro and the interface if you

click on that last worksheet tab here I just have some tidbits here regarding VBA just in terms of the syntax right so in this case if we wanted to change the interior color of a particular cell it's going to be active cell is the object we're going to change the interior color property To VB yellow as well right here's some more screenshots of the VBA interface and I even have some pieces of code here that you can try out in the immediate window go ahead and step into your macro take a look at the code

and come right back now that we've seen what macros can do let's take a look at a really nice example of what we can do with the macro so here's a work work sheeet that you know I received pretty often here and I Find myself making the same adjustments to this worksheet every time I get it because right now we can see this has a lot of issues we can see we're missing the prices here for the price columns we can see some of our text is aligned to the left some is aligned to the

right some are centered we can see some of the text is actually cut off because the column is not wide enough and we're just missing Styles in here so I want to go ahead and write down a list of things That I want to do so for example for uh for cell A1 I want to go ahead and give that the title style right for cell A3 maybe I'll go ahead and give that a heading heading three so it's going to be a lot of styles here right and next thing I want to do for

my prices I want to go ahead and give those the currency format so when you when you have a lot of steps it's a good idea to kind of write them down to make sure you kind Of perform them in sequence here as well so I'm good to go with that the next thing I'll do is for all of my headers I'll go ahead and give that the accent one style or any accent that we want here as well and then for my entire list I want to go ahead and Center a line all and

then finally for my entire document I want to autofit the columns so those are the steps that I want to take here but main thing is that I want to make sure that I'm using absolute [Music] reference okay so here we go so I'll go ahead and turn off relative reference because we want absolute refence F I'll go ahead and click on cell A1 here and I'll go ahead and record my macro and I'll just call this cleanup maybe I'll go ahead and assign a shortcut key so it's going to be control shift C for

My description I'll just say cleanup messy sheet right I already have cell A1 selected I'll go ahead and press okay and I'll just follow my prompt here okay so for cell A1 I'll go ahead and click on the Home tab and I'll apply the title style to that so here's the title for cell A3 I'll give this The Heading one or Heading three rather and then for all for my prices I'll go ahead and highlight my prices here and I'll give that the currency format I'll go ahead and grab my other price column give that

the currency format as well I could have held on the control key and highlighted both ranges and just apply the currency to them as well so macros will understand commands like that also Shortcuts so if you use shortcuts while recording a macro those will be registered as well okay so the next thing I'm want to go ahead and grab all of my headers and give those the accent one style and now we can see starting to make a little sense here I'll go ahead and Center align my entire list so I'll go ahead and highlight

my entire list like that I could have also pressed crl a to select all of the items on my list That will work as well so I'll go ahead and Center align everything and starting to look pretty good last thing I'll do I'll click on the all command here to the left of column a top of Row one and I'll click on the format Tab and I'll use the autofit column width command and there we go I'll go ahead and stop recording that looks pretty good everything is nice and neat according to my liking at

Least and there we go right now let's go ahead and test it on macro cleanup 2 and let's see if that works for me so I'll click here and I can use the shortcut that I created so contrl shift C and look at that I converted or cleaned up this messy worksheet with just one command here as well so pretty pretty cool one thing to note you cannot undo a [Music] Macro okay cannot undo a macro so if I press contrl Z nothing happens so before you run a macro make sure that you absolutely want

to run the macro because you cannot undo it so pretty nice macro here that we can run I do have some other ones on here so here is a macro sort I already have the steps filled out here so go ahead and create two macros one that will sort by division and one that will sort by division then by Department last thing we'll do with these macros I'm going to go ahead and go back to macro setup here and I want to insert a button that I can use to run my relative signature macro so

to do that on the developer tab right over here in the controls command group we have the insert command here so I'll go ahead and insert a it's really a form control so I'll click on insert here and that very first one is a button I'll go ahead and Click on the button here and and I'll put it right here you can draw it wherever you want to you can make it as big as you want to I'll just put it right here as soon as I let it go it's asks me which one of

my macros do I want to assign to this button well I want it to be that Signature 2 I'll go ahead and press okay and here it is so now I can just go ahead and change the Text so I'll say click to sign once I click click away from it the button is now active if I try to come back I see the little pointer here if you want to make any changes to that button you can WR click on it and you can click on edit text so now you can change the text

you can also move the macro around if you want to or even resize it so now you can move it as well right let's test it out I'll go ahead and sign off down Here and right here click to sign and there you go so I did edit the macro I changed the style that's why it looks a little different but now I can just click a button and run my macros here as well so pretty pretty cool you can also add macros to your quick access toolbar so if I click on my quick access

toolbar here let me show that below the ribbon if I click here and I say more commands here's the quick access toolbar And now what I want to do currently I don't have anything on my quick access two bar I want to go ahead and add my macros so I click on this drop down I'll change that to macros and here are my macros as you can see I have a lot here here's my cleanup macro I'll go ahead and add that to the quick access toolbar and here are my two signature macros I'll add

those as well right and I'll go ahead and press Okay so here they are so if I wanted to run my signature too I can click here and run my signature there as well so just different ways that we can run macros from the macro Library by using a shortcut by assigning it to a button or by adding it to your quick access toolbar as well congratulations on completing the course just as a recap we're able to use conditional functions to return values From a data set we're able to use x lookup to return fields

from a particular record based on a particular ID we're also able to take a look at some text functions as well really with Flash Fill and we took a look at some Advanced pivot tables as well we were able to play around with some array functions we're also able to create some Advanced conditional format adding by using formulas and highlighting entire rows we're able to take a look at Advanced charts and we're able to create a few macros in developer mode taking a look at the VBA interface there as well so congrats on completing the

course and I look forward to seeing all of you in a future learn it course thanks for watching to earn certificates and watch our courses without ads check out learnit anytime.com [Music]