Introduction to Microsoft 365 Excel & Conventions for Class - 365 MECS 01

1..Introduction welcome to video number one in this new class microsoft 365 excel the complete story and in this video we'll see the conventions we're going to use in this class and get an introduction to excel 2..Intro Song [Music] 3..Download Files and Create Class Folder now the very first thing you're going to do with every video is to download the excel file and the pdf file now this is the highline college website but over at youtube these links are available below the video we're going to right click the excel file save link as on your computer you're going to select some drive whether it's a hard drive or an external drive i'm going to select the desktop then with the drive selected on the left we're going to use the keyboard control shift n to create a new folder you can name it something smart like microsoft 365 excel and the class at highline is business 218. when i hit enter that names the folder hit enter again now i'm inside that folder i'm going to keep the extension.xlsx click save for the pdf right click save link as that's the correct location file name extension is fine click save let's use the keyboard window e that opens up windows explorer because i want to verify that that actually downloaded on the left i'm going to select my folder and i'm looking inside sure enough both files are there now if you don't 4..Change File Extensions in Windows Explorer see the dot extension like dot xlsx or dot pdf go in windows explorer to view over to file name extensions and check this if we uncheck this we do not see the extensions without those extensions it makes file management more difficult so we're going to check file name extensions now let's use windows explorer to open our excel file i can double click the file to open now here are the topics for our video we're going to start off by going to the sheet conventions now we haven't even formally talked about what these tabs are and how excel is set up but let's 5..Color Coding Conventions for Class click on this what's called a worksheet tab conventions and this shows you the color coding i'm going to use for excel workbooks now this is a table these are called column headers or field names these column headers or field names will usually be in dark blue with white font the actual bits of data the employee name and the amount of sales they had since that's raw data that we type in that's not going to have any fill we'll learn how to create formulas and the formulas will always be in cells that are green this is a number that the formula uses called a formula input when we type the number and then the label at the top we'll either have red with white or sometimes we'll do it this way now if we scroll down using our scroll bar over here here's another color coding we will use excel table objects that's where the column headers are field names are at the top the data is in each row and in this case it'll have that blue with white font and alternating colors for each row and for the sheet tabs down at the bottom yellow means information blue means the sheet that you're going to work on type things create formulas create pivot tables and the red sheets have the exact finished example that i created when i finish the video for example the convention sheet is yellow because this is just information and in fact this sheet is in the pdf notes when you download it so if you like to read a piece of paper you can do that here's a blue sheet so when i click on it we'll actually create a formula right there but if you click on the red one right next to it a n means answer it has the same thing but it has the finished formula also we have some practice or homework problems and we don't get those till video number three the black will be an empty sheet where everything after it is a blue sheet where you do the homework and a red sheet that has the answer now back in our workbook for video number one let's go to the worksheet by 6..Rows, Columns, Cells, Worksheets, Sheet Tabs, Workbooks clicking on the sheet tab structure now fundamentally excel represents a two way grids we have columns and we have rows the columns are represented by letters a b c d and the rows by numbers here's the d column here's row 20 and at the intersection of a column and row that's called a cell now the name of that cell is d20 you can also look up in the name box whatever cell you have selected i'll select b2 it'll tell you that cell now next to the name box is the formula bar and that'll show you the content of the cell so column row make up a cell all the cells make up a worksheet now we can call this a worksheet or just sheet for short each one of these tabs down at the bottom these are called sheet tabs and all you have to do is click to jump to a new sheet if you want to rename you simply double click type a new name and i'm not going to hit enter because i want to keep it structure but when you hit enter that names the sheet now i'm going to click escape you can also right click go up to tab color and color your sheets now i'm going to click escape you can also add a new sheet with this plus button when i click the plus it gives 7..Naming Convention for all objects in class you a new sheet with a terrible name do not leave default names sheet1 sheet2sheet3 are not informative always give the worksheets a good name that communicates the content of that sheet now i'm going to right click not the cell but the actual thing i want to do something to the sheet so right click and delete back on structure now when you select a sheet this is called the active sheet i go over to conventions this is the active sheet back to structure so we have column row cell all the cells make up a worksheet and all of these worksheets make up the workbook now don't call your workbook a document the word document that's for a word file this is a workbook and don't confuse worksheet that's just one sheet with lots of cells with workbook the workbook or the file that contains all the worksheets now later we'll see that there's some other things like a query using power query or a data model using powerpivot those are other types of objects behind the scenes now we have a workbook file and up in the title bar we can see the name of this workbook file 8..Tabs in the Excel Ribbon now down here are tabs for the ribbon i have them collapsed right click one of the tabs and uncollapse you can also use the keyboard control f1 that is a toggle to show and hide the tabs in the ribbon now this is the home tab and in the home tab there are groups clipboard font alignment and the ever so important number formatting and then in each group are commands that you can click there's the insert tab in the ribbon insert charts insert pivot table over in data get and transform data and the queries and connections group that's the amazing power query tool that we'll see later back to home 9..Quick Access Toolbar = QAT. Build Custom QAT now above the ribbon tab are some save buttons that we're never going to use we'll use keyboards control s to save and f12 to save as but to the right of those save buttons i want to click the drop down and there's something called the quick access toolbar or quack that's a tool bar that we're allowed to build so i want to come down and say show below the ribbon and the way you build this quack is you go to the items that you use all the time and right click add to quick access toolbar right click add to quick access toolbar right click add right click add and we can see our quick access toolbar starting to emerge but here's the really cool thing you can go to any one of the tabs right click add and even come to a drop down here this is power query get data launch power query editor and right click add to quick access toolbar now the real advantage to this is if you're working in the data tab all the time you have to come all the way back to home and then go to get your fill but notice this fill button here it doesn't matter where i am or which tab i'm working in these commands are always visible now we're going to learn a lot of keyboards in this class so we're not going to add things we use all the time like a pivot table because there's a convenient keyboard for that now you can improve your quat even further by right click customize quick access toolbar under choose commands from you select all commands and there's about a thousand five hundred commands you can go through this entire list including some items that are not in the ribbon tab select whatever you want add you can reorder i'm gonna put increase decimal first decrease decimal second once you build your quit then you click ok and if you like the visual here you can leave it below but i like to right click show above the ribbon now another thing that's important and convenient about the quack is each of us uses different commands so you get to go through add all the commands that you use all the time now i'm going to hit pause and so that's exactly what i did i added just the buttons that i like now let's 10..Navigating through Workbook talk about navigating through the sheets you can click to activate a sheet there's also sheet arrows and watch what happens to the active sheet when i click right it pushes one sheet under shows a new sheet over here but the active sheet remains active click click and then go back the other direction there's also if you hover a reminder there control left click will go to the last sheet so i'm holding ctrl click sure enough i can see the last sheet notice the active sheet is still active because i'm using the scroll arrows click and i can see all the sheets at the beginning click click also over on the right these three ellipses when you click that it'll select the next possible sheet bam there it is click it got that sheet the best way to navigate through sheets when you have a lot of sheets is to right click the scroll arrows and there's the activate sheet dialog box you can click on conventions click ok and conventions is now the active sheet there's also a keyboard notice i'm on conventions control page down moves the active sheet to the right control page up moves to the left now we want to go to our next sheet keyboard so i'm going to use control page down twice now here's a 11..Keyboards: Desktop or Laptop? picture of my keyboard for my desktop and here's a picture of my laptop if i want to be efficient and work as quickly as i can i'm using this type of keyboard not a laptop keyboard now we're going to use these function keys at the top they do some amazing things in excel there's the window key there's actually a right click key these are the navigation keys page up page down arrow keys and the number pad for entering numbers and there's a quick enter right there and also math symbols now on a laptop you have all sorts of different sets of keys but you can see we don't have a number pad navigation keys are down here there is a page up and page down but the biggest problem is the f keys you have to use an fn or fx key to access these that means you have to press this key with the f key actually sometimes when i go on the road i bring my laptop and i bring a plug-in keyboard like this now 12..Keyboard Shortcuts keyboard shortcuts there's a list of keyboard shortcuts we're going to use now you don't need to memorize all these now because we'll use them throughout the class and when we bump into them we'll talk about them these are also in the pdf notes but i want to give you an illustration of why keyboards are so important we have a data set of revenue and our goal is to add some number formatting to show the currency and then add now the first thing is we don't know how tall this is so we're going to scroll down and try and find the bottom oh no no we do not want to do that we want a keyboard to jump to the bottom now i just scroll down and i want to jump back to the active cell that means the last cell i selected so i use control backspace that's a great keyboard to get you out of trouble when you're way deep down at the bottom of a spreadsheet either a selected cell or in a formula now let's click in the top cell and the keyboard to jump down to the last cell with data is control down arrow and just like that we can see the last bit of data is in row 235 now that keyboard knew to stop because the next cell was empty that means if you have an empty cell in your data set it's not going to get to the bottom it's just going to get to the cell directly above the empty cell control up arrow now our goal is to select the entire column and then add number formatting before we do that let's think about selecting a cell that's b32 and before you click on e32 what happens if we hold shift and then click select hold shift click that highlights everything between the two bookends or between the two clicks so in the top cell here control down arrow would get us to the bottom but if we hold shift that will jump to the bottom and select everything in between so control shift down arrow now we could go up to the home ribbon tab number group and add currency number formatting but all the options for number formatting are not in the home tab they're in the format cells dialog box and the keyboard to open up the format cells dialog box is control-1 now we'll see this a lot throughout the class format cells we can add number formatting all sorts of alignment that's not in the ribbon font border fill even protection backup number i'm going to select currency 2 decimals is fine i'm going to use the dollar sign and with currency number formatting you're allowed to select how a negative number is displayed that's all good let's click ok and just like that we've quickly using ctrl shift down arrow formatted all of our numbers now let's come over here and we want to add 13..First Formula: SUM function using a keyboard and we want to use the sum function now most people select the cell go up to home over to editing and use the sigma or autosum button now hover and notice what it says alt equals hover over some other commands in any ribbon and if it has a keyboard microsoft will usually show you what it is so bold is control b and although there are about 450 worksheet functions in excel sum function is the only one with a keyboard so look at that alt equals so let's try it alt equals and there it is that's our first formula it put in an equal sign which always has to be the first character in the cell to create a formula we're using a built in function called sum the open and closed parentheses has the range b36 to c36 now that's not the correct range but notice the selected range has dancing ants as long as those dancing ants are dancing around you're in full edit mode watch i can click anywhere until i get exactly the correct range so let's click in the top cell and i definitely don't want to click and drag with my selection cursor i'm going to use control shift down arrow so that's the second use for that you can use it to select and format or you can use it to select cells when you're creating a formula now let's use ctrl backspace to jump back to the active cell and then enter and there you go keyboards are fast that's why we use them now let's go over to the sheet cursors this is a list of cursors as we 14..Cursors encounter them in the class we'll talk about them this is also in the pdf notes now let's go over to data and alignment 15..Data & Alignment and how Default Alignment Helps now the types of data that we can have in the excel worksheet well we can have text numbers logical or boolean values that's when we have true or false we can get errors and empty cells really isn't a data type but it's something that we have to deal with now let's type the word excel and when i hit enter i want you to notice what the default alignment is enter it's left that's your visual cue that this is text if i type a number 43 and enter well the alignment is right that's your visual cue that excel thinks this is a number if i type lowercase true in this case i'm going to hit the tab key to move to the right it capitalizes and centers the other boolean value is false and notice i spelled it wrong so of course that visual cue tells me that's not a boolean value that's text we'll try it again false there you go if we create a 16..Second and Third Formulas yield an error formula and all formulas start with an equal sign with my selection cursor i'm going to click on cell d4 so that means it'll take 43 divide that's forward slash and i'm going to type a 0 in well are we allowed to divide numbers by zero when i hit the tab key to move to the right sure enough in excel it gives me a divide by zero error if i create a formula equal sign and i try to add a number plus a word well you're just not allowed to do that in math when i hit enter it's polite it gives me a value error that means one of the inputs into the formula along with that math operation are not correct so these are the different data types that we'll see in the worksheet later when we use power query we'll see that there are some other data types also in excel we could have text a number a boolean and an error all in the same column later in power query it'll only let us put a particular data type in the column and that's very helpful when we're using pivot tables and other data analysis tools now 17..Why Default Alignment is important: Fourth & Fifth Formulas why is this default alignment important well let's look down here we have some sales here and here immediately you should recognize the default alignment says these are numbers these are text if we come over and try to add alt equals it got the correct range c14 to c18 so when i hit enter these are numbers so the sum function adds over here alt equals well wait a second we immediately know there's trouble because it didn't grab the numbers above well we can redirect using the selection cursor so i get the right range and enter well that's zero the sum function is programmed to ignore text excel sees these as numbers these is text but really the immediate visual cue is that these numbers are not aligned to the right now that's where power query will come in later because this is common where we get numbers from the company database and they come to us as text in power query it's easy to just convert these to numbers now another 18..Default Alignment to track down errors great use for default alignment is to track down typing errors now for numbers we saw that we can type a number but dates and times are also numbers if i type 43 and accidentally type an extra decimal tab the visual cue immediately tells me that's not a number it's text if i enter the date 12 slash 24 20 2022 when i hit tab there are definitely not 244 days in december so excel does not recognize this as a proper date which should be aligned to the right with time you enter hour colon minutes and you're supposed to put space am or pm but if you leave out that space tab there's the text that's not a proper time value in all cases if i fix my mistake it'll immediately be aligned to the right and i'll know it's a number i can select the cell put the cell in edit mode with f2 and if i click backspace to remove one of those periods now when i hit tab i can see the alignment to the right i know it's a number f2 i'll do the same thing here remove one of the fours tab and now i have a proper date aligned to the right so i know it's a number for this 8 a.m all i have to do is add a space tab and now i have a proper time aligned to the right now let's look at one last thing scroll down here's an inventory tracker 19..Don’t Use Center Alignment!!! and we're going to highlight this and do something that many people in the working world mistakenly do home over to alignment and they want everything to be neat and tidy so they use center alignment but as soon as you do that you lose your visual cue right here here's a formula f2 well it looks like it's working these balances over here look like they're correct but let me undo that alignment using the keyboard for undo control z we lost that visual cue and this one the totals are wrong and so is that so don't use alignment always keep the default alignment now the exception is if you have a finished report you're done with everything and it's something that someone's going to view then you can use alignment but this is a working model where we're entering numbers and we have formulas keep that default alignment i can't tell you how many consulting jobs i've done where they send me the spreadsheet and everything centered and it makes it much harder to track down errors alright so default alignment sometimes it's our best friend now let's 20..What Excel Does: Hold Data, Calculations and Data Analysis go over to the sheet what excel does now what can excel do well it can do almost anything the possibilities are almost limitless but in general it can do three things it can hold data make calculations and perform data analysis now when it comes to holding data this is a table with field names at the top and records in rows each row tells us a particular date sales rep and the amount of their sales but this is just data we're going to use to make calculations and perform data analysis we're not storing it here permanently you use databases for that now in excel we can hold data in the worksheet we can also hold data in a pivot table cache and a data model which we'll see later we can also make calculations from that data if i hit f2 you can see we're using the average function to calculate average sales and data analysis that's just taking raw data converting it into useful information to gain insight and make decisions here we just took sales and date we had to roll it up into monthly sales this is a report and from that report we created a visualization so we can gain insight june was the biggest month and here we can see the trend from january to june the trend is up and between may and june it looks like it's way up now that's our perfect transition to next video next video on this calc sheet we'll make these calculations using excel worksheet formulas and then on the data analysis sheet we'll take this data set and with a pivot table we'll create these two reports and this 21..Summary visualization now in this video we talked about what excel can do very importantly we talked about data and alignment cursors we talked about the importance of keyboards and keyboard shortcuts we talked all about the structure of excel and we started it off by talking about the conventions for this class all right next video we'll 22..Closing and Video Links make calculations and perform data analysis see you next video [Music] you