Excel 2021 Beginner Tutorial
Excel 2021 Beginner Tutorial
Excel 2021 Beginner Tutorial
Exercise Files: https://bit.ly/3xZKDiw
Who it’s for: Beginners - experienced, everyday users looking to sharpen their skills and increase efficiency in Microsoft Excel.
What it is: Microsoft’s spreadsheet application used to clean and organize data.
What you’ll learn: We start with the basics, showing you the user interface and quick ways to navigate. Once you’re comfortable with the software’s layout, we quickly move on to calculating data with basic formulas and functions. In the Intermediate section, we show you the various tools and ways to sort and filter data in a timely manner. Then, we dive into one of Excel’s most popular features; the Pivot Table. Our Advanced training starts with basic functions like the IF function. After that, we explore complex Database functions, provide an introduction to Macros, and show you how to save time by automating common Excel tasks.
Join Learnit Anytime for ad-free training, exams, certificates, and exclusive content:
https://www.learnitanytime.com
For Private Group Trainings please visit:
https://www.learnit.com/private-group…
Join Offsite for our online community of teachers and peers:
https://www.offsitebylearnit.com
Manuals: https://bit.ly/30xZb59
Username: manuals
Password: password
Start 0:00
Introduction 0:03
Start Screen 2:35
Excel Interface 7:03
Zooming, Navigating, and Shortcuts 13:31
Performing Excel Calculations 28:19
Simple Excel Functions 36:53
ABS, REF, and AutoFill Functions 42:25
Checking Compatibility 47:51
Inserting, Moving, and Cutting 49:29
Cell Styles 54:30
Themes 59:41
Showing and Hiding Worksheets 1:03:16
Grouping Worksheets 1:06:19
Freeze Panes 1:08:45
Custom Views 1:14:15
Saving Templates 1:16:09
Spell Check 1:18:36
Print Preview 1:20:09
Conclusion 1:23:17
#excel #excel2021 #exceltutorial
(C) 2022 Learnit, Inc.
Any illegal reproduction of this content will result in immediate legal action.
Content
3.76 -> hello and welcome everyone
6.08 -> it's vasqueen with learn it
8.559 -> and today i want to start talking a
10.48 -> little bit about excel 2021
13.599 -> this class will focus on the beginner
15.44 -> topics of excel 2021 like the user
17.68 -> interface spreadsheet design and
19.52 -> formatting
21.68 -> however in october of 2021 as we all
24.48 -> know microsoft released windows 11 and
26.8 -> alongside it came office 2021 where you
29.679 -> can buy
31.039 -> a standalone version of office with a
33.12 -> set of features
34.399 -> instead of opting in for office 365
37.36 -> which some organizations do because they
39.76 -> can purchase a subscription
42 -> for microsoft
43.52 -> and when they get the subscription they
45.04 -> get updates to these softwares regularly
48.239 -> where office 2021 comes out with a set
50.96 -> of features and there's no updates after
52.96 -> that
54.879 -> now
56.079 -> in these sessions
57.68 -> we have an excel beginner intermediate
59.68 -> even expert session that we're going to
61.12 -> cover
63.12 -> we have a set of topics
66.4 -> now in this course i broke up the topics
68.56 -> into two different what i call modules
71.28 -> where the first module is going to cover
72.96 -> excel essentials the user interface some
76.08 -> basic features that we should know about
78.799 -> basic maneuvering keyboard shortcuts and
81.759 -> some formulas
83.439 -> basics of the max min sum count
88.96 -> in module 2 i also want to spend some
91.04 -> time and just start talking about
93.52 -> formatting
95.04 -> how to design a spreadsheet using
96.72 -> certain tools
98.72 -> how to set up a page how to go to page
100.72 -> print preview
104.079 -> now
106.24 -> this class is also designed to
109.6 -> be a little hands-on
112.24 -> meaning
113.36 -> i recommend you to pause the video after
115.119 -> i show you something and i even say that
116.72 -> sometimes in the video
119.2 -> to try to give a practice maybe building
121.92 -> the formula that you just saw me see
124.079 -> or
124.88 -> use the keyboard shortcut that i'm using
130.479 -> so with that being said don't be shy to
132.56 -> open up your excel your office 2021
136.319 -> and practice these things
138.8 -> in this beginner course i will be
140.319 -> building the spreadsheet entirely from
142 -> scratch there isn't an exercise file
144.879 -> but we'll be building it from scratch
146.48 -> from a blank workbook
150 -> i'm pretty excited to get started let's
152.319 -> go ahead and begin
156.4 -> so i just launched excel and as soon as
159.28 -> you launch these office products since
161.44 -> 2013 in fact
163.599 -> you get taken to a screen
166.239 -> that's known as the start screen there
168 -> has been some updates here and there to
169.76 -> it throughout the years but it's looking
171.68 -> like they left most of it the same if
173.76 -> not almost all of it the same this year
175.84 -> in office 2021
178.16 -> but let's talk a little bit about the
179.599 -> start screen and
181.2 -> on the left hand side you're going to
182.8 -> notice that there's three areas you can
184.64 -> navigate to
186.72 -> one being home
188.879 -> other one being new where you can access
190.48 -> your templates and build a new workbook
192.879 -> or even open an existing workbook
194.64 -> directly from your computer
197.76 -> let's talk a little bit about each one
199.12 -> of these tabs before we move forward to
200.879 -> a blank workbook and discover the new
203.12 -> office 2021 user interface
207.36 -> now when you end up clicking on home
209.04 -> here it's going to show you an area
210.56 -> where you can access your new
212.72 -> built-in templates
214.72 -> or you can browse online for some
216.72 -> pre-existing templates
218.64 -> that microsoft has available for you
222.159 -> and if you've saved any personal
224.239 -> templates you can also access them
226.4 -> directly from here
228.319 -> i just don't have any saved yet
231.12 -> now
232.08 -> besides actually going to home and
234.48 -> having this area for templates
237.2 -> you also have a search box to browse
238.959 -> templates and if you've opened any
240.4 -> recent workbooks
242.319 -> you're going to see them listed here
245.599 -> you can also pin them to this area to
247.599 -> keep them nicely conveniently accessible
250.4 -> and you can browse the workbooks that
251.84 -> people have shared with you
253.439 -> do maybe one drive or outlook directly
255.36 -> from here
257.68 -> so the home page is nice
259.84 -> area to get started
262.16 -> and when you click on new
264 -> it just shows you
265.44 -> the templates that we just saw on the
267.199 -> home page
269.6 -> and open of course
271.68 -> is it going to show you an area where
272.96 -> you can browse your cloud services like
275.36 -> maybe sharepoint or onedrive
277.6 -> or even browse your pc
280.24 -> to select the file or a workbook and
282.24 -> open it
283.44 -> or even access your autosave workbooks
285.759 -> in case there has been a crash you can
287.44 -> recover any unsaved workbooks directly
289.919 -> from here
291.04 -> very powerful feature
294.479 -> so a lot to discover here in the start
296.479 -> screen not too many changes that i've
298.4 -> seen
299.28 -> we still have our account where we can
300.96 -> sign into directly from the top hand
302.8 -> side which i'm already signed in as
305.6 -> we still have the excel options at the
307.52 -> very bottom
309.199 -> we have our account information at the
311.039 -> bottom where we can give that a click
313.039 -> and here we can see that i have my
314.96 -> office home and business 2021 license
317.759 -> there
321.6 -> i'm not going to change the theme
324.56 -> i'm going to leave it as the colorful
325.919 -> theme since we're talking about the
327.28 -> software today but there are quite a few
329.36 -> different themes that we can change this
331.28 -> software application too
333.6 -> or even the backgrounds we can have an
335.84 -> office background like right now i have
337.52 -> this clouds
339.199 -> i can customize and have no backgrounds
341.039 -> keep it nice and simple there
345.36 -> so we just did a little tour of the
347.199 -> start screen
349.039 -> it's useful to know about it's a good
350.88 -> area to get started
352.96 -> but now that we've done that i just want
354.4 -> to start talking a little bit about
356.639 -> my favorite template probably the one i
358.56 -> use the most
360 -> the blank workbook
363.12 -> because in this course i want to talk
365.12 -> about quite a few features in excel
367.68 -> and how i do that is i want to build a
370.16 -> spreadsheet
371.759 -> that actually tracks the sales of a
373.44 -> restaurant that i'm building
375.759 -> the restaurant's called
377.28 -> faz food
380.96 -> and i'm just going to track some weekly
383.199 -> sales see how much i made for the month
385.759 -> so sold the most food who sold the least
388 -> food maybe hand out bonuses depending on
390 -> that
391.12 -> and i want to build a spreadsheet that i
392.72 -> could eventually hopefully print
395.039 -> and put on my bulletin board every month
398.24 -> now hopefully this spreadsheet is
399.52 -> reusable
400.88 -> it has colors and it's pretty it's
402.8 -> formatted so that way i can put it front
404.56 -> facing and print it
406.24 -> and hopefully it all fits on one page so
408.16 -> we want to talk about all those features
409.52 -> that allow us to do that
411.28 -> using this newer interface that we have
416.16 -> now let me click on a blank workbook
417.84 -> here and give you a tour of the office
419.759 -> 2021 user interface
424.88 -> i went ahead and launched the blank
426.319 -> workbook
427.68 -> and now that i'm in a blank workbook i
429.36 -> just want to talk a little bit about the
431.039 -> office interface because there has been
433.199 -> some changes to it
435.68 -> as you all know the windows 11 look is
438.8 -> quite drastically different than windows
440.639 -> 10 has much softer bezels looks like a
443.44 -> much more lighter tone
445.28 -> and it has an overall different
446.8 -> experience to it
448.639 -> and i've noticed they kind of mimic that
450.479 -> look with these office products
453.12 -> and they actually have a newer interface
455.84 -> for the ribbon
457.599 -> kind of looks more protruded out there
459.12 -> as you can see with the tabs kind of
460.8 -> sticking out a little bit differently
463.52 -> than they used to be
468.88 -> and then of course we're always going to
470.479 -> have our command group still where we
472.08 -> have the labelings on the bottom we have
474.08 -> our command groups
482.08 -> so there is a lot going up up there
484.879 -> with the new office 2021 ribbon
488.08 -> now one thing i want you to notice
489.68 -> though
490.879 -> with this office 2021 ribbon is that
493.919 -> there's actually no quick access toolbar
496.08 -> anymore
497.919 -> there used to be a set of tools that we
499.599 -> can actually add here
502.879 -> this might alarm people
505.44 -> and don't worry i've actually found out
508.16 -> you can turn it back on
510.479 -> so if you are one of those people that
511.84 -> are familiar with the quick access
513.36 -> toolbar
514.56 -> and you notice you just upgraded and you
516.32 -> don't have it anymore
518.24 -> i do want you to feel free to pause the
519.919 -> video and first watch what i do
522.479 -> and then go add the quick access toolbar
524.32 -> to you your user interface because i do
526.48 -> enjoy using it
528.399 -> let me go ahead and right click
531.12 -> one of these tabs
533.2 -> so that i can customize the ribbon
537.519 -> and from there i actually have an option
539.2 -> to specifically customize the quick
541.04 -> access toolbar
543.44 -> and now in office 2021 they're actually
545.839 -> going to ask you to well
548.64 -> turn on the show quick access toolbar
551.2 -> setting
553.519 -> and you can choose the position above or
555.76 -> below the ribbon i'm gonna put mine
557.44 -> above the ribbon
560.24 -> and hit okay
563.279 -> so now i have my quick access toolbar
565.12 -> options there where i can add them
567.68 -> directly to the top where they used to
569.76 -> be there
572.88 -> i can also right click and pin some
575.2 -> items there from my toolbar in the
577.44 -> ribbon in the command groups as well
580.32 -> so if you do enjoy using your quick
581.92 -> access toolbar i highly recommend to
584.399 -> just give this video a pause
586.399 -> and go to your ribbon and right click a
588.48 -> tab
590.32 -> just right click any tab it doesn't
591.76 -> matter which one it is
594.24 -> and customize the ribbon
597.12 -> so that you can access the quick access
599.279 -> toolbar settings
601.279 -> and on the very bottom there you set the
602.959 -> chicken market on
604.56 -> and choose if you want it above
606.88 -> or below the ribbon
609.839 -> and hit okay
613.68 -> glad you did that if you didn't it's
615.44 -> okay maybe one day you will if not maybe
617.6 -> you like the minimalistic look
620.88 -> now
622 -> i also want to mention that if you want
623.519 -> to hide your ribbon
626.399 -> we have a little area there a little
628 -> drop down where you can select it
630.16 -> and now we have see settings for the
632.16 -> ribbon
635.04 -> we can always show the ribbon that's my
636.8 -> preferred setting
638.8 -> we have a full screen mode which hides
640.64 -> the ribbon until you click on the top
644.48 -> we also have an option for show tabs in
646.24 -> case you're on a mobile device or a
648.399 -> little laptop that way you have a small
650 -> screen you can save some real estate
652.24 -> so as you click it shows you the tools
654.32 -> i'm going to always go to always show
655.92 -> ribbon though
657.44 -> and i like how they conveniently left
659.2 -> hide the quick access toolbar option
662.8 -> and show the quick access toolbar option
665.36 -> directly there so we can quickly add and
667.6 -> remove those toolbars that we have
672.32 -> pretty interesting i also want to point
674 -> out the bottom of our workbook
677.44 -> where our sheet names are has a little
679.04 -> bit more of a rounded bezel look instead
680.8 -> of a square look
682.24 -> uh we can still add our sheets and of
684.079 -> course if you do a right click in this
686.16 -> area
687.44 -> you're still going to get the activate
689.2 -> window
690.24 -> which is going to be a vertical list
692.48 -> of all the sheet names in the workbook
697.36 -> i'm going to take advantage of the zoom
699.04 -> slider here
700.88 -> the zoom slider and i'm going to zoom
702.48 -> into the spreadsheet so that we can
703.76 -> begin
706.079 -> maneuvering around excel
713.2 -> great
714.16 -> nice and zoomed into my spreadsheet here
716.56 -> i want cell a1 that's what my name box
718.8 -> is showing me
721.92 -> and the cool thing about excel is today
724.32 -> at any point in time if you ever want to
726.32 -> look for a certain feature
728.56 -> they also have a search box at the very
730.32 -> top of the ribbon this has been here for
731.6 -> quite a few iterations now but it's
733.519 -> worth they mentioned where you can
735.279 -> search for certain features
737.44 -> and even if you need help on them
740.16 -> receive help on them by looking into the
741.92 -> microsoft support articles that open up
744.48 -> pretty natively within the software
751.36 -> this is a quick little article on
752.72 -> powerpivot
754.079 -> a great tab that you can use for your
755.519 -> pivot tables
759.36 -> go ahead and close that there
761.6 -> so
762.399 -> not many changes to the user interface
764.72 -> but it does have a nice clean refreshed
767.04 -> look
768 -> i want to say the biggest thing that
769.519 -> you're going to notice is that your
770.72 -> ribbon display options have now moved
773.2 -> and your quick access toolbar
775.36 -> is now
776.56 -> able to be turned on or off
779.68 -> which is something that we couldn't
780.8 -> previously do
782.959 -> go ahead and pause the video
784.72 -> open up a workbook
786.56 -> play around take a look at your user
788.079 -> interface
789.2 -> set up the settings for your quick
790.48 -> access toolbar
792.32 -> i'm going to save my workbook here
794.72 -> i'm going to go ahead and call it the
796 -> faz food
798.8 -> monthly sales
806.32 -> so that we can get started
808.16 -> hopefully you do the same
812.639 -> so now i actually want to begin by
814.72 -> typing in these cells and creating the
816.72 -> spreadsheet that i would like
819.04 -> and i'm going to do that by adding a
820.48 -> title to my spreadsheet i'm just going
821.92 -> to call this one fast food
825.2 -> and i'm just going to put on the bottom
826.399 -> there always fresh
829.839 -> and why not always fazz
835.44 -> that's me
837.519 -> now when i hit enter here
840.079 -> i just want to point out for people when
842 -> you hit enter in excel that actually
843.92 -> moves down
845.519 -> something known as your cell address box
848.88 -> which is that green box there
851.36 -> and the enter key is how we navigate and
854 -> essentially move it down a cell space
866.399 -> so i'm going to hit enter just one more
867.839 -> time because i do want to add some
869.36 -> headers
871.279 -> to my spreadsheet because i want to
872.72 -> actually track the sales people
878.88 -> and i just want to do a week by week
881.44 -> breakdown
883.36 -> and now that i've actually added in
885.519 -> salesperson i just want to mention
887.12 -> another
888 -> little shortcut that i use
890.88 -> because i did move over to this cell
893.12 -> over here
894.24 -> i moved basically to the right that's
896.079 -> what i essentially did i moved over
898.72 -> and in order to do that
901.76 -> i simply clicked on the tab key
904.959 -> and that actually went ahead and moved
906.56 -> me
910.32 -> to the right hand side
912.8 -> and now of course if i hold on to shift
917.279 -> and tap
919.6 -> that's actually going to move me back to
921.199 -> the left
923.92 -> same thing with enter
942.72 -> extremely useful to navigate with
944.24 -> shortcuts especially when you get some
946.079 -> more advanced topics like an excel
948 -> expert when we talk about macros
953.279 -> so now that i have this here i actually
955.199 -> want to add in a quick little column for
957.44 -> week one
960.56 -> and i notice when we are typing in
963.279 -> spreadsheets
965.12 -> there are certain situations where words
967.279 -> get cut off in the cell
971.04 -> and that's not a big deal i mean there's
972.959 -> many ways we can fix this
975.12 -> but this might happen all over the place
977.36 -> might happen over several different
978.72 -> columns
980.72 -> we can always go to the top of each one
982.399 -> of these columns where the letters are
984.32 -> that's where the column is
986.079 -> and between them you can always expand
987.759 -> them to make them bigger
990 -> or smaller
993.519 -> however i've always dealt with
994.72 -> situations where i have multiple columns
997.6 -> multiple multiple columns that have that
999.519 -> situation
1002.32 -> like this column and this column now
1006.079 -> and i like to mention in my beginner
1008 -> courses that there's this little
1009.36 -> triangle here
1011.839 -> next to your column headers
1014.24 -> and you give it a little click
1016.24 -> it's going to select all the columns and
1018.24 -> all the rows in the entire sheet
1022.32 -> and if you go ahead and do a double
1023.839 -> click in between one of the columns
1027.199 -> or one of the rows
1028.959 -> it's going to give you a best fit
1031.679 -> for the entire spreadsheet since
1033.36 -> everything's selected for entire columns
1035.439 -> or for the entire rows
1039.199 -> so notice how the sales person column is
1041.199 -> nice and large simply because
1043.52 -> it takes the place of the largest cell
1046.16 -> and it fits the column to that size
1049.36 -> where b and c fit the cell size of week
1051.6 -> one and week two
1054.24 -> so always remember if you need to
1056.08 -> quickly fix a spreadsheet sizing for
1058.32 -> these cells
1059.6 -> or if you see a lot of pound signs or
1062 -> hash signs within cells when you have
1063.84 -> numbers
1065.12 -> it's just letting you know that these
1066.4 -> cells are too small to display something
1068.24 -> for you
1069.52 -> and you might want to quickly click on
1071.039 -> that triangle and do a double click on
1072.72 -> the rows or the columns depending on
1074.32 -> what situation it is
1075.919 -> to quickly get it fixed for you i
1077.36 -> usually do that for email something out
1079.52 -> or quickly before i save the workbook
1081.36 -> and close it so the next time i open up
1083.2 -> it's nice and clean
1087.919 -> now that i actually have these headers
1089.84 -> in place where i put week one and week
1092.4 -> two
1095.2 -> i want to continue on by adding in
1097.84 -> week three
1099.44 -> and week four in these cells
1102.48 -> but in order to do that i'm going to
1103.919 -> take advantage of a tool
1105.84 -> called autofill
1111.28 -> now i want you all to know
1113.44 -> that every cell that you have a cell
1116.24 -> address box on
1118.24 -> has this little square a green one
1121.2 -> on the corner of it
1126.08 -> that's the auto fill handle
1128.88 -> and what it's going to do is going to
1130.24 -> help you
1131.76 -> if you have a word and a number it's
1133.2 -> going to increment that number next to
1134.72 -> the word so i can have week four week
1137.12 -> five quarter one quarter two quarter
1139.28 -> three
1140.96 -> i can do months i can do days
1145.12 -> let me go ahead and expand this here
1147.679 -> make it to week three and i'm gonna make
1148.96 -> it to week four
1151.6 -> now a lot of people are new to this when
1153.28 -> they use the autofill handle
1156 -> please make sure that when you are using
1157.679 -> it that you're making sure to see the
1159.28 -> visual indicator
1161.2 -> that you're on top of the autofill
1162.799 -> handle by looking at the skinny black
1164.72 -> crosshair
1166.08 -> before left clicking and holding
1168.559 -> and dragging across
1178.72 -> looking good looking good
1182.64 -> so
1183.44 -> there's something i want to do here
1186.16 -> to my headers
1188.799 -> currently
1194 -> i have my headers in
1196 -> but
1196.72 -> what i want to do is i want to highlight
1198.24 -> them
1199.919 -> i want to highlight all of these headers
1201.919 -> so that i can align them
1204.32 -> in the middle of the cell for each one
1206.24 -> of these cells
1208.24 -> and in order to actually do that it's a
1210.32 -> really simple process
1212.08 -> but i want to talk about
1213.76 -> keyboard shortcuts to do things
1217.2 -> because this is definitely where the
1218.4 -> power of efficiency comes in
1221.52 -> in real life you might have 50 60 you
1223.6 -> might have a whole year's worth of data
1225.28 -> that you're highlighting
1227.12 -> not just four weeks
1229.2 -> and
1230.08 -> knowing these keyboard shortcuts will
1231.6 -> save an immense amount of time when
1232.88 -> highlighting things
1234.48 -> so if i needed to highlight this top row
1237.44 -> from the very start here
1239.84 -> i can always use ctrl shift
1242.72 -> and then i can choose a directional
1244.4 -> arrow
1246.32 -> it will highlight in that direction
1248.159 -> until it meets a blank cell
1253.28 -> let me try that out ctrl shift
1256.32 -> to the right
1258.88 -> i like i like and of course i can always
1261.36 -> go to my home tab
1263.12 -> and under the alignment command group i
1265.12 -> can center
1266.96 -> these items here
1274.84 -> great now that i have this in here
1278.08 -> what i want to do is i also want to
1279.679 -> continue by adding in the actual sales
1281.76 -> people names
1285.84 -> and then hopefully giving them some data
1287.6 -> some raw data that i can put in that i
1289.039 -> have for the month
1292.32 -> so i'm just going to add in some sales
1293.679 -> people here let me go ahead and put my
1295.12 -> name as one of the sales people
1305.52 -> here i'll go ahead and put
1309.28 -> some familiar names that we might have
1311.039 -> heard of maybe not
1327.44 -> keep it simple
1328.88 -> it's a small staff
1330.96 -> super small team
1333.84 -> we're going to keep it with only four of
1335.12 -> us
1337.28 -> so now that i have this here i want to
1339.679 -> actually start adding in some data
1343.2 -> to my spreadsheet
1348.32 -> so when i enter in data i'm going to put
1350.88 -> my cell address box on a cell i'm just
1352.96 -> going to add in some data some fake
1354.159 -> numbers like 50
1356.64 -> i'm going to hit tab
1358.559 -> to move over to the right so i can add
1360.159 -> in 100
1362.88 -> and now i want to talk a little bit
1366.32 -> about another trick that we want to
1368.559 -> take advantage of
1370.64 -> we want to get used to it
1373.28 -> it has to do with the autofill handle
1374.88 -> again
1376.48 -> my auto fill handle
1379.679 -> remember that thing i just used a moment
1381.44 -> ago where i highlighted a chunk of
1383.919 -> cells or acell and this little square
1386.64 -> was pulled
1388.4 -> well what happens if you do it with two
1390.4 -> cells selected
1392.24 -> instead of just one
1393.679 -> right that's what i have now
1396.08 -> it's actually gonna follow a pattern
1399.84 -> so this pattern is plus 50.
1403.2 -> so in these next cells if i actually
1405.039 -> pull my autofill handle in that
1406.559 -> direction to the right
1408.48 -> it's going to be 150
1412.799 -> and
1414.84 -> 200. me go ahead and pull that
1423.44 -> and of course
1425.039 -> autofill tries to help as much as
1426.64 -> possible so if you end up pulling it
1428.64 -> down you have too many cells selected
1431.76 -> like it's not going to recognize a
1433.2 -> pattern here
1435.28 -> but it will help you by copying down the
1437.44 -> cells now
1452.159 -> i'm going to go ahead and drag down here
1456.48 -> copied it i'm going to drag down here
1457.84 -> again
1460.08 -> and i'm going to drag down here again
1467.279 -> so i have some fake numbers in here just
1469.2 -> some fake sales data to get the
1470.64 -> spreadsheets
1471.84 -> template a little bit of a look i want
1473.919 -> to see how it would look with numbers in
1475.44 -> it
1477.6 -> however this is supposed to actually
1479.2 -> display sales data
1484.72 -> and what i want for these actual cells
1487.919 -> are for them to have a data type
1492.96 -> for the currency values that i'm
1494.559 -> displaying
1496.08 -> and in order to do something like that
1499.12 -> i want to actually use the keyboard
1500.96 -> shortcuts to highlight the chunk of
1502.64 -> swells
1503.84 -> or cells i should say
1514.32 -> i can always use the keyboard shortcut
1516.159 -> to highlight to the right
1523.84 -> and of course
1526.159 -> i can always use the keyboard shortcut
1536.48 -> to highlight all the way down
1545.6 -> now that i have this highlighted i have
1547.36 -> multiple routes that i can take
1549.52 -> to add this currency format
1554.08 -> now inside my home tab i actually have a
1556.48 -> number command group
1559.039 -> where i can toggle on the currency
1560.559 -> format and hide my decimal places
1564.72 -> but i'm all about efficiency
1567.279 -> and if you notice there's actually
1568.64 -> keyboard shortcuts for many things that
1570.559 -> we're talking about today
1574.32 -> and if you want you can always use
1575.76 -> control shift
1577.52 -> and the number four at the same time
1579.52 -> being held
1581.039 -> remember the number four holds a dollar
1582.84 -> sign to change those cells that you have
1585.44 -> selected to a currency format
1587.919 -> that's pretty convenient if you change
1589.76 -> it to five it goes to percentage
1592.24 -> date times three two one is general
1596.159 -> or one is number tilde's general
1599.12 -> i'm going to hit the ctrl shift 4
1601.76 -> there
1604.48 -> rather simple
1605.919 -> keeps it smooth i didn't have to move my
1607.36 -> hands anywhere i have to go away from
1609.12 -> the keyboard
1613.12 -> now the final thing i'm going to do is
1614.4 -> i'm going to knock away these decimal
1616 -> places
1617.6 -> by holding down the alt key
1621.279 -> and clicking on the letter h because
1622.96 -> that's where the h key
1625.84 -> and then i'm gonna click on the number
1627.039 -> nine because that's going to hide the
1628.4 -> decimal place
1630.48 -> and you can also do it really fast you
1632.32 -> can hit alt and then h and then nine
1635.039 -> really fast and it'll hide the decimal
1636.48 -> places
1639.039 -> i've been talking for a while though
1641.279 -> and i've actually produced quite a nice
1642.88 -> spreadsheet there's not any color to it
1645.36 -> but i have the layout down
1647.84 -> i have the columns created and the
1649.52 -> headers in them
1651.279 -> i have some sample data have all the
1653.12 -> sales people's names on the roster
1656.48 -> got the title the slogan things are
1658.32 -> looking good i centered the headers
1660.799 -> added the currency data type that i
1662.559 -> needed
1665.44 -> now what i would like you to do
1667.76 -> is just pause this video and
1670.64 -> catch up
1672.32 -> if you haven't been doing so pause the
1673.76 -> video and create this spreadsheet use
1675.679 -> those shortcuts
1677.679 -> practice the data types adding in cells
1680 -> hitting enter hitting tab hitting shift
1681.76 -> tab hitting shift enter
1683.919 -> navigate around
1685.919 -> go back to the video and repo where you
1687.52 -> watch certain parts as you do it
1689.679 -> but definitely practice with those
1691.279 -> shortcuts in mind
1693.84 -> hopefully i'll see you soon pause the
1696.24 -> video give us a try and come right back
1700.32 -> welcome back
1701.679 -> hopefully you had fun creating the
1703.039 -> spreadsheet practicing the shortcuts
1706.399 -> and now that we have some data or some
1708.48 -> names actually in here
1710.88 -> something i want to mention that we have
1712.799 -> available since office 2016.
1716.08 -> in case you come across a word
1718.32 -> you're not familiar with you want to get
1720.32 -> some information an article about
1721.919 -> something there's a tool known as smart
1724.159 -> lookup
1726.08 -> where you can always look up information
1728.24 -> using a bing search
1730.799 -> directly from the spreadsheet
1735.679 -> and of course once i'm done with this
1737.36 -> search i can close it
1739.6 -> and it's just worth a mention
1741.279 -> when you have this cool feature known as
1743.2 -> smart lookup with a right click
1745.679 -> or you can always go over to if i'm not
1748.32 -> mistaken
1750.72 -> the review tab
1752.559 -> and the smart lookup is available under
1754.559 -> the insights command group
1762.159 -> very interesting stuff
1764.08 -> they try to get you to stay in excel as
1766.159 -> much as possible instead of hopping away
1767.919 -> to these various browsers that you might
1769.76 -> need to use with this feature
1773.84 -> so now that i have this in here
1776.32 -> i have this data in here
1778.24 -> what i want to actually do
1780.64 -> is i want to build a column
1784 -> that tracks the totals
1786.24 -> for these sales people
1789.279 -> and in order to do that i have to
1791.44 -> perform these simple calculations
1795.12 -> a sum
1796.159 -> i have to sum certain cells
1798.72 -> and there's so many ways to do that in
1800.32 -> excel
1801.6 -> so many different ways
1803.84 -> and i just want to talk about these
1805.039 -> various ways that we have available
1807.44 -> since this is a beginner course let me
1809.52 -> build a total
1812 -> column here and i just want to run
1813.919 -> through the various ways you can perform
1815.6 -> simple calculations or even build a
1817.84 -> formula
1819.12 -> to do the job
1823.76 -> now the most basic way to actually add
1825.84 -> something in excel
1827.6 -> or to run a formula an equation a
1830.32 -> function is to start your cell with an
1832.88 -> equal sign
1835.679 -> that tells excel hey
1837.36 -> i'm about to do a formula i'm about to
1838.799 -> do a calculation
1843.2 -> and if you want you can actually select
1845.679 -> the cells that you want to use
1847.919 -> in the formula
1849.36 -> by selecting the cell
1851.12 -> once the equal sign is there
1853.76 -> so instead of actually putting the
1855.039 -> numbers you want
1857.6 -> although some people do this
1859.44 -> but even numbers that you want it's much
1861.44 -> more preferred to put the cell address
1863.12 -> you want tell excel where to look
1865.84 -> that way in case the number changes in
1867.52 -> the spreadsheet you don't have to go
1869.279 -> running to update the total column as
1870.96 -> well
1873.279 -> now of course this way might take a
1874.88 -> little time if you have a lot of numbers
1876.32 -> to add
1878.399 -> and it seems kind of like a repetitive
1879.919 -> process there with even four cells
1883.52 -> but if i sum up b5 plus c5 d5 and e5 and
1888.08 -> hit enter because i'll go down a cell
1890.96 -> will total to 500 as as it should
1893.6 -> as it should there
1895.52 -> on the bottom of excel there's something
1897.12 -> known as a status bar
1900.24 -> you haven't heard it before the bottom
1901.919 -> of that software is called the status
1903.679 -> bar
1904.799 -> where our zoom slider is located and it
1906.72 -> actually shows us the sum is 500
1909.36 -> when you have cells highlighted of
1910.799 -> course
1918.159 -> so another way of actually doing a
1920.799 -> calculation
1922.32 -> is instead
1924.159 -> you're still going to need an equal sign
1927.919 -> but this time you're going to use
1930.72 -> a function
1933.279 -> from the excel's function library
1936.96 -> it ends up showing you all the function
1938.399 -> names once you start filtering for it
1940.24 -> and start typing in a few letters
1943.6 -> like i want to use the sum function and
1945.279 -> i can filter for some
1948 -> and once i end up highlighting it i can
1949.679 -> always click on the
1951.12 -> tab key to fill in the rest for me
1955.519 -> and this essentially allows me to
1956.96 -> highlight a chunk of cells
1959.6 -> within a parenthesis
1963.519 -> so that i can use the sum function to
1965.76 -> add up all these cells for myself
1967.679 -> without manually adding those plus signs
1969.76 -> in
1970.72 -> it was a repetitive task
1978.559 -> you know there's even a way where you
1980.08 -> can highlight
1981.679 -> remember we know shortcuts
1984.08 -> we know the shortcut where we can
1985.279 -> highlight something with
1988.88 -> control
1992.799 -> shift and the right arrow
1996.96 -> i'm going to do that to highlight the
1998.08 -> michael scott there
2003.6 -> and if i want i can also do this thing
2006.24 -> where i can hit alt
2008.48 -> plus
2009.6 -> an equal sign
2012.48 -> this is actually going to use a tool
2014 -> known as autosum you have to hold the
2016 -> equal sign and the alt key at the same
2018.32 -> time
2019.279 -> it's called auto sum
2021.039 -> and when i do that if i hit alt and the
2023.519 -> equal key
2025.2 -> it'll actually run a sum function
2028.48 -> for me on the row that i had selected
2031.279 -> the issue with that is every time you do
2032.88 -> it it's always going to show up on the
2034.08 -> cell right next to it
2036.48 -> so if you didn't want the answer to be
2037.76 -> right next to it that wouldn't be your
2039.039 -> way
2041.12 -> now of course i'm going to highlight
2042.32 -> this ctrl shift to the right
2046.64 -> and one final way that i want to talk
2048.24 -> about is using something invented
2051.359 -> in 2016 it was called the quick analysis
2054.32 -> tool
2056 -> where we can always quickly
2059.2 -> access
2060.56 -> a set of tools that are already in our
2062.24 -> ribbon
2063.359 -> but one of those tools is called totals
2067.679 -> and if i want i can always select
2070.879 -> the sum
2073.04 -> as one of the totals there did make it
2074.72 -> bold for me
2076.159 -> but that's always a quick fix by turning
2077.919 -> off the bold there
2081.52 -> so many ways to do the same task
2084 -> honestly
2085.119 -> performing a simple calculation or doing
2087.679 -> a formula
2089.52 -> using a tool known as quick analysis
2092.8 -> all these things are going to get the
2094 -> job done and
2095.28 -> add up some cells subtract some cells
2097.44 -> maybe multiply some cells
2099.599 -> we'll play around with those functions
2100.96 -> coming soon but the one basic one i
2103.359 -> wanted to start out with was some to
2105.52 -> just perform simple calculations across
2107.92 -> the workbook
2111.119 -> now something i want to mention is i've
2112.8 -> been talking for a little bit i've added
2114.56 -> a lot to the spreadsheet
2117.2 -> i use onedrive
2118.96 -> you may be using onedrive or another
2120.72 -> cloud service but when you do save your
2122.88 -> word files excel files powerpoint files
2125.2 -> on onedrive
2126.56 -> autosave actually turns on
2129.76 -> i don't have to worry about saving
2131.2 -> anything because it automatically saves
2132.88 -> it for me
2134.48 -> but i just want you to spend a moment of
2136.079 -> time if you haven't yet going to the
2137.52 -> file tab and saving a copy of the
2139.599 -> workbook
2141.119 -> if you have yet to do so
2143.2 -> if you have yet to do so or hit the save
2145.119 -> button just you've already gave it a
2146.96 -> name just to keep and save all the data
2149.68 -> that you've currently added
2152 -> never know when you get an unexpected
2153.359 -> crash or a power outage
2158.96 -> so
2160.16 -> now that we've practiced filling out a
2162.079 -> totals row
2163.76 -> before we move on to some other
2166.64 -> and i wouldn't even call them more
2167.839 -> advanced functions just some different
2169.92 -> functions like max min count
2172.88 -> i want you to practice
2174.96 -> i want you to practice doing a formula
2176.96 -> by putting in the actual cell addresses
2180.64 -> i want you to practice by actually
2182 -> putting in a sum function use your
2183.92 -> keyboard shortcut to highlight things
2188.24 -> use autosum
2189.92 -> alt plus equal
2192.56 -> use quick analysis
2195.28 -> highlight the cells and go to the square
2196.96 -> there
2199.599 -> when you are done practicing the totals
2202 -> go ahead and come back to the video and
2203.359 -> press play and we'll continue with some
2205.92 -> more basic calculations formulas or
2208.96 -> functions that we should use throughout
2210.8 -> excel
2214.32 -> all right
2216.88 -> while you're away i went ahead and added
2218.64 -> a few extra cells in this spreadsheet
2221.04 -> let me go ahead and highlight these two
2222.48 -> columns
2223.68 -> i just want to add some borders around
2225.359 -> it as well
2227.04 -> i'm gonna add some borders around these
2228.56 -> cells
2230.079 -> because i want to actually use the sales
2232.079 -> data that i have
2234.96 -> and i want to figure out some totals
2237.839 -> the average
2239.52 -> the max
2241.04 -> what's the biggest number what's the
2242.48 -> smallest number and how many numbers are
2244.16 -> in the data set
2246.96 -> let's start out with the total here
2249.68 -> so of course we're going to use the
2251.2 -> function library in excel and we've
2253.28 -> already used this one quite a few times
2255.04 -> the sum function
2257.599 -> always remember to click on tab please
2259.68 -> it saves you quite a bit of time with
2261.119 -> these longer function names that we're
2262.8 -> going to see in just a moment
2266.16 -> and with some here i'm simply going to
2267.68 -> highlight this chunk of squares
2270.4 -> remember we have keyboard shortcuts if
2272.24 -> there's really large
2273.76 -> that's going to grab our total there let
2275.359 -> me go ahead and grab that so i don't
2276.64 -> double up the values
2279.44 -> and i'm going to close the parentheses
2284.4 -> and when i hit enter it's going to find
2285.76 -> me the sum so that's 2000 makes sense
2287.76 -> there's four rows of 500
2291.359 -> and the average
2293.359 -> there's also another function that i can
2295.2 -> find and once i've typed in av i can go
2297.44 -> down find average and click
2300 -> tab
2301.04 -> to fill in the rest for me
2303.68 -> and of course i'm just going to
2304.72 -> highlight week 1 through week 4 and find
2306.8 -> me the average
2310.079 -> of
2310.839 -> 125 and of course the biggest sale is
2313.839 -> using a function called max
2319.68 -> where i can highlight a chunk of cells
2322.4 -> and it's going to find the biggest
2323.599 -> number within those cells
2326.88 -> and of course min
2332.16 -> will find the smallest number
2336.72 -> now counts the final one there
2340.32 -> and when you use a count what is
2341.839 -> essentially going to do
2350.24 -> it's going to count the values
2352.8 -> like how many values you actually have
2354.24 -> selected
2357.119 -> so there's 16 values make sense there's
2358.96 -> four rows with four weeks four by four
2361.44 -> 16.
2363.839 -> so pretty simple to practice these
2365.52 -> functions
2366.96 -> now again a lot of times people they
2369.52 -> actually go to the formulas tab
2371.76 -> and you can actually insert functions as
2373.92 -> well using the insert function dialog
2376.16 -> box
2377.92 -> this does take a little time to fill out
2379.839 -> for each little function but if this is
2381.599 -> your way where you use the sum function
2385.599 -> you might want to take advantage of it
2387.44 -> i'm going to hit sum and hit ok
2390.24 -> and then i can go ahead and select the
2391.68 -> cells that i want to add inside this
2393.2 -> form
2395.359 -> so that's another way of doing the same
2397.119 -> thing the same functions i can use the
2399.359 -> formulas tab
2400.8 -> and go to insert function
2403.119 -> and this one's average yeah
2405.44 -> i can always go ahead and select these
2406.88 -> cells and hit ok
2409.599 -> i can do the same thing for the max
2412.079 -> instead of writing it in the cell with
2413.599 -> an equal sign
2415.119 -> i can insert function which adds the
2416.96 -> equal sign
2418.8 -> find the max function
2420.64 -> hit ok
2422.32 -> select the cells i want to use for max
2425.839 -> and same thing for min
2428.16 -> if need be
2430 -> of course i don't see min in the list so
2431.76 -> i have to search for it
2434.48 -> there it is
2435.599 -> and i can hit ok
2437.68 -> and from there i can highlight the same
2439.28 -> chunk of cells
2443.119 -> let's do this count just to get all them
2444.8 -> in
2445.599 -> i'm going to type in count just to hit
2447.52 -> go there it is on the bottom there
2451.359 -> and from there i can highlight these
2452.72 -> chunk of cells and hit okay
2455.44 -> and i'm just going to re-highlight
2456.56 -> everything and i'm going to go to the
2457.599 -> home tab and add the borders again
2463.28 -> so rather simple rather simple whether
2464.96 -> you're using the insert function from
2466.48 -> the formulas tab or you're just adding
2468.24 -> the equal sign and typing the function
2470.48 -> name in and hitting tab
2472.24 -> both ways are there available for you
2478.8 -> great
2481.119 -> go ahead and pause this video and i do
2482.88 -> want you to try
2484.48 -> these other functions that we have
2487.599 -> besides just
2490.4 -> some
2492.24 -> now before you go i want to give you a
2494.72 -> bonus
2496.16 -> i'm going to give you a bonus function
2499.359 -> i'm going to go to cell e1 here
2502.8 -> and i want to type in
2504.72 -> the date
2506.24 -> in cell f1
2508.64 -> and i want the date to always show me
2510.8 -> the current date
2513.28 -> and i have a function called today
2516.56 -> and there nothing goes inside the
2517.92 -> parentheses but they need to be there
2521.04 -> and what that's going to do is it will
2522.4 -> add me the date
2523.839 -> inside of the cell
2525.44 -> and tomorrow i'll say whatever date
2527.119 -> tomorrow is and so on and so forth
2530.319 -> so it's a nice function to go ahead and
2532.4 -> have the current date from your computer
2534.319 -> time displayed on the spreadsheet
2537.92 -> so now that i've covered that go ahead
2539.76 -> and pause the video practice these
2541.68 -> simple functions before moving on
2546.8 -> now that we have
2548.56 -> the basics down i want to do another
2551.28 -> formula i consider this a simple
2552.88 -> function as well
2554.64 -> but i actually added another cell at the
2557.04 -> top where each sales person actually has
2559.119 -> a goal they have to meet
2561.52 -> and it looks like everyone met the goal
2563.04 -> it's 500
2564.88 -> but i want to actually track that using
2567.119 -> a function
2568.88 -> so if they met the goal i just want to
2570.64 -> have the letter p for pass
2573.28 -> and if they didn't it's going to say f
2574.8 -> for fail which none of them failed they
2576.24 -> all passed
2577.599 -> we're the dream team here
2581.119 -> so what i'm going to do here is i'm
2583.2 -> going to use a function
2586.079 -> in our function library known as
2588.88 -> the if function
2592.24 -> it simply goes and it checks whether
2594.16 -> something is either true
2596.48 -> or it checks whether something is false
2600.8 -> if that ends up being true you can do
2602.8 -> something
2604.319 -> if it ends up being false you can do
2605.92 -> something else
2611.04 -> this if function here
2613.44 -> has three different arguments you have
2615.76 -> to fill in
2617.52 -> the logical test
2619.599 -> the value if true
2621.44 -> and the value if false
2625.44 -> i can always compare
2627.119 -> if this cell
2628.88 -> f5 is greater than or equal to
2632.88 -> the cell with my monthly goal
2636.48 -> so that's my logical test that can
2638.24 -> either be true or it can be false
2643.28 -> now the value if true
2645.599 -> is what i want excel to do if it ends up
2648.319 -> being true
2651.28 -> i'm just going to say the letter p and
2653.2 -> because p isn't a number i have to put
2655.119 -> it in quotes
2658.72 -> so that it can display the character and
2660.56 -> if it ends up being false i'm going to
2661.76 -> hit comma
2662.96 -> after each one of these i'm hitting
2664.24 -> comma
2665.52 -> if it ends up being false i want to hit
2667.68 -> f which none of us actually failed
2670.72 -> and i'm going to close my parentheses
2674.48 -> so it's rather simple it just checks
2676.079 -> whether this cell is greater than or
2678 -> equal to this cell
2679.44 -> and if it's true it's going to say p
2682.88 -> if it's false it's going to say f
2685.839 -> but none of us are false we all are
2688.079 -> equal to 500 actually
2695.76 -> now i actually can use autofill
2698.4 -> that tiny little square
2701.599 -> on a formula
2713.2 -> that's weird
2716.839 -> hmm
2718.4 -> someone failed
2720.079 -> even though it's 500 that's really weird
2723.2 -> let's let's look into this let's see why
2726.4 -> so when i did this autofill
2728.64 -> so this is the original formula with f5
2732 -> and f2
2734.56 -> autofill actually is going to change the
2736.72 -> cell address
2739.28 -> of a formula when you drag it down
2742.8 -> so notice how this is f6 now it's
2744.64 -> comparing this cell oh and a blank cell
2746.48 -> that makes a lot of sense
2749.44 -> and this one was just a word that's why
2751.04 -> it said fail it compared to a word so i
2753.04 -> guess the value of this word is big
2756.48 -> but that's not comparing what we want it
2757.92 -> to it's not comparing the correct cell
2762.64 -> so what i'm going to do here is i need
2766.48 -> to absolutely reference
2769.28 -> it's called absolutely reference
2772.319 -> a search and cell address
2775.04 -> i can lock in this cell address so that
2777.119 -> when i use my autofill
2779.839 -> it will remain at f2 and only f5 will go
2782.48 -> to f6 to f7 to f8
2786.079 -> that way i'll compare the correct cells
2787.76 -> and in order to do that i simply add a
2789.44 -> dollar sign
2791.839 -> around the f and a dollar sign
2794.319 -> around the two
2797.2 -> when i end up hitting enter here and i
2799.44 -> redo my autofill
2802.48 -> you're going to see it all change to
2804.16 -> pass
2809.119 -> and
2810.319 -> if i go and i just really quickly i'm
2811.839 -> going to browse through these they're
2812.8 -> all pointing at f2
2814.72 -> because i've absolutely referenced this
2817.28 -> cell address
2819.2 -> to lock it in place
2821.68 -> so it is kind of interesting to know
2823.359 -> that
2824.319 -> autofill works a little different with
2825.92 -> functions and formulas they change the
2827.76 -> cell addresses within them
2829.76 -> but in case you need to have them fixed
2832 -> we can always use
2834.8 -> absolute referencing
2836.8 -> in situations like the goal situation
2839.04 -> here where we had one cell that needed
2840.64 -> to stay put
2842.24 -> and others needed to go down we can
2843.839 -> always absolutely reference one another
2847.44 -> i do want you to try this out pulling
2849.68 -> out an if
2851.119 -> this is as far as the functions i will
2853.76 -> go i'll keep that as simple functions
2856.4 -> but absolute referencing is definitely
2858.56 -> something i wanted to cover so we can
2860.4 -> get the basics of it down
2862.24 -> autofill absolute referencing go ahead
2864 -> and give this a try
2865.359 -> pause the video and come back so we can
2867.76 -> continue on
2872.079 -> now something that's worth a mention
2874.16 -> since we are using the newest version of
2876.24 -> office
2877.359 -> is if you want to check if you're using
2879.28 -> any features that might not be available
2882.079 -> in other versions so far we haven't
2884.72 -> you can always go to the file tab
2887.119 -> and under info
2889.359 -> we can inspect the workbook
2892.319 -> i'm seeing more and more something we
2893.76 -> have to use is check compatibility
2896.24 -> just to make sure we're not using any
2897.76 -> features that aren't supported by
2899.839 -> earlier versions of microsoft excel
2902.96 -> i can also have this
2904.72 -> turned on
2906.24 -> to check itself every time i save the
2908.72 -> workbook
2910.8 -> so definitely run this
2912.64 -> if you are working with clients they're
2914.24 -> outside the org and you're using
2915.839 -> advanced features not many in this
2917.599 -> course but maybe something like x lookup
2920 -> or x match or the let functions or we'll
2922.72 -> talk about in the more expert sessions
2924.72 -> or intermediate sessions those will
2926.24 -> definitely cause the compatibility
2928.16 -> checker
2929.119 -> to run a quick little check and tell you
2931.2 -> that you might not see those functions
2933.2 -> on another versions of excel
2936.16 -> definitely worth it worth an issue with
2937.92 -> worth a check compatibility
2940.72 -> and remember it's always a file tab away
2944.079 -> under info for check the issues there
2947.119 -> now go ahead and give that a try before
2948.72 -> moving on from this video check the
2950.64 -> compatibility issues just to give a
2952.24 -> muscle memory to find that area and if
2954.72 -> you feel like you are going to be
2956 -> sending out quite a bit of workbooks to
2958 -> outside of your org and you're not sure
2961.119 -> what version people have make sure to
2963.359 -> check mark
2964.559 -> check compatibility when saving this
2966.16 -> workbook for yourself
2970.48 -> all right
2973.359 -> welcome back
2975.119 -> now i actually just got a request
2977.92 -> from my team
2979.44 -> it's more of my data analysis team they
2981.44 -> told me that for their reporting needs
2984.319 -> they really need a column that just has
2986.4 -> the last name
2988.079 -> and the first name separate
2990.559 -> in two separate cells
2992.24 -> so that when they're tracking things it
2993.68 -> can go by first and last name
2996 -> so they asked me to insert two
2997.599 -> additional columns here where i can
2999.2 -> track the first name and last name of
3001.599 -> each of these sales people
3005.92 -> i'm going to insert a row or i should
3008.079 -> say a column i apologize i'm going to
3009.76 -> right click and insert a column
3013.04 -> and it's going to show up to the left of
3014.72 -> whatever column i have selected
3017.52 -> so i'm going to insert i'm going to call
3019.04 -> this first name
3024.319 -> and if i go ahead and select column c
3026.48 -> and i hit insert it's going to show up
3027.839 -> to the left of column c and i'm going to
3030 -> call that last name
3034.16 -> just to keep things short i'm just going
3035.76 -> to put last in first actually
3038.64 -> let me go back there and just remove the
3040.4 -> names to keep things short
3045.2 -> and i'm just going to start typing in
3046.559 -> the first names there's not too many
3050.96 -> starting 2013 and up
3053.44 -> when you start typing names like this
3056 -> it's going to automatically try to help
3057.52 -> you it's like oh you're splitting the
3058.8 -> first names up
3061.2 -> super useful it's actually known as
3062.96 -> flash fill
3064.319 -> let me go ahead and do the last names
3065.599 -> too
3069.119 -> super useful
3071.28 -> wonderful and just to resize everything
3073.599 -> i'm going to click on this triangle here
3075.839 -> and double click to resize that again
3077.839 -> and there we go
3080.079 -> i got the first and the last name in
3085.28 -> don't worry in case you ever get a new
3086.8 -> sales person you can always insert a new
3088.96 -> row as well by right-clicking and it'll
3091.04 -> show up above the selected row
3093.76 -> above the selected row but i'm just
3096.079 -> going to undo that there i don't really
3097.359 -> want to do that
3103.839 -> all right
3107.04 -> so now that i've inserted a column or
3109.599 -> maybe a row if i chose to do that
3111.839 -> what i want to do is i want to move
3113.52 -> things around a little bit
3115.92 -> i actually want to get this sales person
3117.92 -> goal
3120.319 -> and i want to move it
3123.2 -> just a little bit over
3127.839 -> to about maybe down here so i can keep
3129.76 -> the metrics all in one spot
3132.16 -> maybe in fact maybe right over here
3136 -> just to keep the metrics in one spot
3138.88 -> yeah or maybe even on the bottom of this
3140.559 -> area here let me go ahead and grab this
3143.92 -> and i'm just going to go over to the
3145.44 -> border of the cell address the border
3149.52 -> and i'm going to do a left click and
3150.72 -> hold and i'm just going to drag it to
3152.319 -> where i want to locate it it's actually
3153.68 -> rather simple to do that i can put it at
3155.839 -> the bottom here put it on the side let
3157.52 -> me put it on this side here
3161.119 -> cool
3163.68 -> notice how because i have this absolute
3165.44 -> reference to change to the cell
3168.559 -> it referred to the correct cell still
3170.559 -> wonderful
3173.599 -> so there's my sales person going i'm
3175.359 -> also just going to add a border around
3176.8 -> that as well just to keep it pretty
3178.48 -> there
3179.68 -> salesperson goal i'm gonna make it bold
3184.319 -> i can do that to the date too here i'm
3186.4 -> just gonna copy the date this time i'm
3188.079 -> gonna hold down the control key
3191.2 -> and then go to the border
3194.559 -> when i hold down the control key and i
3196.24 -> left click and i drag it makes a copy
3200.96 -> i'm just going to add the borders and
3202.72 -> make it bold as well
3215.04 -> so
3216.079 -> now that we have this here
3218 -> and i've
3219.44 -> moved around some cells and i've entered
3222.88 -> some columns
3224.8 -> i want you to pause the video and catch
3226.559 -> up with me
3228.24 -> practice inserting these columns by
3230.559 -> having them show up to the left hand
3232.24 -> side
3233.119 -> practice
3234.319 -> moving a chunk of cells
3236.64 -> by just going to the border of it and
3238.559 -> dragging it
3239.92 -> and practice copying a chunk of cells by
3242.48 -> holding down the control key and then
3244.72 -> dragging it from the border
3247.119 -> when you get a little bit comfortable
3248.559 -> doing those process or that motion with
3250.96 -> your mouse and keyboard
3253.119 -> and you got it down go ahead and come
3255.04 -> back to the video so we can start
3256.48 -> talking a little bit about
3258.319 -> styling
3259.52 -> making the spreadsheet look presentable
3261.359 -> for printing
3262.96 -> using themes that's something
3264.96 -> interesting too
3266.88 -> go ahead and play around with this and
3268.319 -> come right back
3271.119 -> all right welcome back and hopefully you
3273.359 -> had fun playing around moving things
3276 -> inserting columns and watching flash
3278 -> fill do its thing
3279.44 -> by entering the first and last names and
3281.44 -> having it fill in the rest for you
3285.76 -> now i do just want to spend some time
3287.92 -> talking about formatting because we are
3289.839 -> looking at a black and white spreadsheet
3292.4 -> it's going to end up being printed
3294.64 -> and something that i take advantage of
3296.319 -> when formatting things in word excel
3299.119 -> is a take advantage of these style sets
3301.44 -> that we have
3303.119 -> to where i can go to maybe a title cell
3305.359 -> and in my home tab
3308 -> i actually have a command group known as
3310.319 -> styles
3313.2 -> and from here i can add in
3315.68 -> a style to mimic a title a title cell
3325.04 -> i can also do that for my headings
3326.8 -> there's also heading styles this is
3328.319 -> using the office theme
3330.64 -> these are the office styles there's also
3332.559 -> different styles we can add
3335.04 -> here i'm going to get the date and
3336.319 -> highlight it
3338.319 -> i'll make this a check cell now that
3339.839 -> looks too too much i'll do explanatory
3343.04 -> there we go i always make this a little
3344.319 -> bit bigger so it shows it
3350.96 -> normal
3352 -> explanatory
3354.559 -> and ending
3361.68 -> there we go i want to make it a long
3363.2 -> date
3364.4 -> i just changed the date to a long date
3366.96 -> so you can see it fully spell that looks
3368.559 -> nice
3370.96 -> i also want to grab these headers but
3372.799 -> remember
3373.839 -> there's a lot of headers now so we're
3375.28 -> going to take advantage of that keyboard
3377.04 -> shortcut when we grab these headers
3385.04 -> and when i do that i just want to make
3386.559 -> this
3387.839 -> headers more dominant that they're
3389.359 -> actually headers by highlighting them
3392.16 -> and just going to the top and i'll make
3393.839 -> them into uh nothing too big let's do
3396.799 -> heading three there so we can add the
3398.4 -> line to the bottom
3400.319 -> all right looks good
3404.48 -> i'm also going to highlight these
3405.76 -> salespeople names right the sales people
3407.839 -> names and make them a certain color as
3409.68 -> well
3413.04 -> yeah i'm just going to make these ones
3415.28 -> um here i'll make them
3419.2 -> a little gray there
3421.28 -> i'm going to actually grab the data
3423.52 -> itself week 1 through week 4.
3426.319 -> i'm going to make them into input cells
3428.079 -> because uh
3429.04 -> that's actual input that i'm putting in
3434.72 -> and of course i'm going to get this
3436.079 -> total cell
3437.599 -> and this was gold met cell
3440.72 -> and what i want to do is i'm going to
3441.839 -> have this as output cells as output
3444.319 -> cells letting me know it's the output
3445.92 -> for the input
3453.359 -> i'm going to grab this chunk with the
3454.88 -> metrics here i'm just going to add a
3456.48 -> shading to it here i'm going to do
3459.119 -> a nice yellow to stand out as well as
3461.599 -> the monthly gold cell just add some
3463.2 -> color to this there
3465.44 -> cool
3468.88 -> i also want to just grab this entire
3470.799 -> chunk here and i'm just going to move it
3472.799 -> right i want to move it over to the end
3474.319 -> here
3475.2 -> and i'm going to resize everything to
3476.88 -> make sure everything fits
3478.88 -> there we go
3484.16 -> so it didn't take too long but i've
3486.319 -> styled up the spreadsheet rather nicely
3488.559 -> let me actually add some borders around
3490.64 -> this chunk of squares
3493.839 -> let me highlight the chunk
3495.92 -> you can always highlight a chunk the
3497.44 -> entire chunk by hitting ctrl in the
3499.76 -> letter a this will grab everything
3501.599 -> surrounding your cell address
3503.839 -> yeah
3506.16 -> control a
3508.559 -> everything surrounding the cell address
3515.76 -> now that i have this selected i can
3517.599 -> always go and add the borders from the
3519.28 -> home tab
3520.559 -> by applying the all borders option to it
3524.559 -> so this will show up in the print as
3526.799 -> well which i'd want
3530.319 -> here i feel like this style is very
3533.119 -> close to the output style so i'm going
3534.88 -> to change it i'm going to easily
3536.4 -> highlight it because you can always
3537.599 -> change these again
3539.119 -> instead of using a 20 accent here let's
3541.2 -> go ahead and change this maybe to a blue
3544.079 -> there we go
3549.599 -> i do want you to pause the video
3551.68 -> and i want you to practice using styles
3555.68 -> adding in any data and model title and
3558.4 -> headings or themed cell styles
3561.2 -> to your spreadsheet try to use at least
3562.799 -> one of each
3564.88 -> once you do that go ahead and come back
3566.64 -> so i can show you how we can drastically
3568.64 -> change these color palettes
3570.64 -> and the fonts and the effects if you had
3573.28 -> any in the spreadsheet with just a click
3575.52 -> of a button
3576.72 -> by toggling the different themes in the
3579.44 -> workbook
3582.88 -> lovely welcome back hopefully we have a
3585.04 -> nice colorful spreadsheet now with a
3587.92 -> bunch of cell styles put into place
3591.44 -> not to be alarmed though if you're
3592.64 -> wondering why your favorite color wasn't
3594.4 -> there or certain colors weren't there
3596.96 -> it's simply because of the fact that
3598.559 -> when you open up a workbook by default
3601.2 -> you are using something known as the
3603.359 -> office theme
3606.559 -> and in fact under page layout
3609.76 -> you can browse the set of themes that
3611.28 -> you have available
3614.64 -> i have a themes command group
3617.76 -> and from there it's going to tell me i
3619.04 -> have the office theme set but i have
3620.72 -> these different themes that i can switch
3622.4 -> to
3623.2 -> which will change the font sometimes the
3624.96 -> size a little bit as well as the color
3626.72 -> sets that we have available
3628.48 -> within the styles
3644.559 -> i'm just browsing to see which one i
3646 -> like the best honestly there's so many
3650.72 -> you know i think this is a restaurant
3652.319 -> i'm going to use the organic that just
3653.68 -> always makes sense to me
3656.559 -> looks all organic
3658.559 -> i'm going to go to the home page or the
3660.4 -> home tab and i just want to show you how
3662 -> my styles have completely changed
3665.92 -> so it's rather useful to
3668 -> set cell styles that you want and then
3670.4 -> at the very end pick a theme
3672.559 -> that you want now don't get me wrong if
3674.16 -> you don't like any of the themes that's
3675.599 -> also possible
3677.52 -> but you're always allowed to change the
3679.28 -> color of the theme if you don't like it
3681.119 -> maybe you like the font of organic
3683.599 -> but you weren't feeling the color
3686.079 -> you're feeling some other type of color
3687.76 -> maybe this one here median
3690.559 -> so now what you did is you've changed
3692.24 -> the color for the theme
3695.599 -> and if you want you can also save this
3699.44 -> you can save this as a brand new theme
3703.359 -> so at the next time you want to build a
3705.04 -> spreadsheet you can keep the same color
3706.799 -> set with the organic font
3709.839 -> i'm going to go ahead and call this
3711.2 -> custom theme
3717.2 -> called custom organic theme because
3718.88 -> that's what it's based off of
3722.88 -> and this theme will be available and
3724.88 -> actually word as well
3726.4 -> where we can go ahead and apply this to
3727.76 -> our documents change the font and the
3729.68 -> color sets that we have available this
3731.599 -> will be available for all of our
3732.799 -> workbooks the custom theme there
3735.52 -> that we saved i do want you to try this
3737.839 -> out though
3738.96 -> i do want you to spend a moment
3741.28 -> picking the themes browse through all
3742.96 -> the ones that you have
3744.4 -> see if you like any of them
3746.72 -> if you don't like any of them spend a
3748.24 -> moment and build your own pick the one
3750.72 -> that you do like the closest to what you
3752.4 -> like and then set the color
3754.96 -> set the font
3756.48 -> and if you are planning to add shapes
3758.64 -> boxes
3759.839 -> images
3761.04 -> you might also want to set the effects
3763.2 -> as well
3764.559 -> and then once you have done that in
3766.319 -> order to actually save and reuse that
3768.16 -> without always having to add these three
3770.24 -> toggles
3771.52 -> go to save the current theme
3773.76 -> that will save the color the font and
3776 -> the effect within label that you labeled
3778.24 -> it and it will add this new header
3780.079 -> called custom to your themes area
3782.64 -> that you'll have available across all of
3784.64 -> your workbooks not just this one
3790.799 -> don't forget to come back when you give
3792.079 -> this a try
3793.76 -> see you soon
3796.96 -> all right so i actually got another
3798.88 -> request from my team
3801.68 -> my team is asking me to grab
3804.16 -> this top half
3805.839 -> of the data
3807.44 -> and if it's possible to actually add it
3809.68 -> to a separate sheet in the workbook
3812.079 -> without the notes on the bottom
3815.119 -> this would be end up being used to maybe
3817.2 -> just browse the data display the data or
3819.359 -> just kind of filter the data they just
3821.359 -> want the sheet with the data not really
3822.64 -> the metrics on the bottom
3825.839 -> not a big deal i already have it it's
3827.2 -> just a matter of me adding a separate
3829.28 -> sheet usually when i have more than one
3831.039 -> sheet i like to name things so let me
3832.96 -> name this first sheet
3834.96 -> fast food
3838.64 -> monthly
3840.72 -> and i'm going to go ahead and hit a plus
3842 -> sign here and i'm just going to call
3843.119 -> this the
3844.319 -> data
3845.599 -> to add the sheet for the data sheet
3848.16 -> and i just want to grab this entire
3849.76 -> chunk the sales person week one through
3852.64 -> week four with the totals and the goal
3854.4 -> mat
3856.88 -> and we have a shortcut for that as long
3858.559 -> as my cell address box is somewhere
3860.4 -> within
3861.52 -> that chunk
3863.28 -> we can always use ctrl a like we
3865.2 -> mentioned earlier
3867.92 -> control and the letter a to highlight
3870.319 -> all of the data in that surrounding
3872.4 -> chunk
3874.96 -> and of course i can always go to the
3876.319 -> home tab and choose copy or i can right
3878.88 -> click and copy
3880.64 -> or i can always use ctrl c
3882.96 -> there's so many ways to copy
3887.44 -> but i will end up seeing these marching
3888.96 -> ants
3890.48 -> once i end up seeing this marching ants
3892.48 -> here
3893.599 -> i want to go ahead and add this by going
3896.72 -> to the datasheet
3898.16 -> let me zoom in a bit with my zoom slider
3903.119 -> and i'm just going to go ahead and hit
3904.319 -> paste
3906.88 -> great i'm just going to resize these as
3908.64 -> well
3910.48 -> just to have the raw data on one sheet
3912.319 -> and lovely there i met that request easy
3914.88 -> peasy
3919.92 -> now keep in mind if you didn't want
3922.64 -> this sheet to be present
3925.359 -> you can always right click
3927.28 -> and hide it
3929.039 -> and when you want to do it end up seeing
3930.799 -> it you can always right click a sheet
3932.4 -> and unhide your worksheet to see it and
3935.2 -> make it available
3939.92 -> so now that i've inserted a worksheet
3942.64 -> i've hid the worksheet i unhid the
3944.4 -> worksheet and i've moved data to the
3946.72 -> worksheet i want you to practice this
3949.76 -> before moving on to something i call
3952.559 -> grouping a worksheet
3954.88 -> i want to go ahead
3956.559 -> and i want you to practice moving the
3958.4 -> data to this worksheet here
3962.4 -> when you do that we're gonna come back
3964.4 -> and cover a feature known as grouping
3967.28 -> where i can add
3968.72 -> two sheets together
3970.4 -> and everything i write down will bleed
3972.319 -> on to both sheets
3974 -> so if i copy and paste the title it'll
3976.559 -> show up on both sheets
3980.4 -> welcome back but i do have another
3982.4 -> requirement from my team they're asking
3985.119 -> me to add the word internal in nice
3987.599 -> large bold red letters
3989.92 -> in cell a3 on all of the sheets in this
3993.52 -> workbook
3994.72 -> luckily there's only two
3996.72 -> but i know of a clever way where we can
3998.72 -> add that internal
4001.28 -> snippet on both sheets at the same time
4005.359 -> i'm going to do this concept known as
4007.44 -> grouping where i can get both sheets and
4009.599 -> put them together
4011.2 -> by holding down the control key
4014.96 -> and then selecting
4018.48 -> the sheets
4021.28 -> you want to group
4024.96 -> and when i do that it's going to show
4026.16 -> like as if they're both activated
4029.44 -> there's my data fast food monthly and at
4031.92 -> the top it says group
4034.88 -> and also a lot of things are grayed out
4036.559 -> from the insert tab now because it's not
4038.48 -> going to let you insert something to two
4040.079 -> sheets at once just type
4042.48 -> and if i type in internal
4045.359 -> i can go ahead and set it to bold make
4047.28 -> it red
4048.64 -> make the font size a little bit bigger
4050.24 -> let me do that for myself
4052.24 -> bold red font size bigger
4055.2 -> and i also just want to make it centered
4057.039 -> as well
4058.799 -> and now that i've done it in a grouped
4060.48 -> manner i can right click
4062.799 -> and ungroup and i can see that both
4065.52 -> sheet data
4067.44 -> and fast food monthly have internal
4070.16 -> listed with all the formatting all the
4072.24 -> sizing
4073.52 -> the same i only have to type it once
4076.72 -> grouping is extremely useful for those
4078.48 -> last minute edits to a workbook where
4080.24 -> you have to add multiple sheets of data
4082.079 -> you have to have the same data point
4083.52 -> into them
4084.48 -> we have to copy and paste data into
4086.24 -> multiple sheets at once add a formula to
4088.88 -> multiple sheets at once
4090.96 -> very useful tool i do want you to try
4093.119 -> this out
4094.16 -> the concept of grouping something
4096.48 -> grouping a worksheet before we move on
4099.04 -> to the next feature
4100.719 -> which will be known as freezing panes
4103.679 -> i want to make it easier to scroll
4105.679 -> through the spreadsheet by locking the
4108.08 -> top rows of my headers so they stay in
4111.359 -> place
4112.56 -> as i scroll
4114.159 -> very convenient we have a ton of rows in
4116.56 -> the data set give this grouping a try
4119.52 -> hopefully i got you excited for freezing
4121.52 -> pains when we come back
4125.759 -> if we had a little fun grouping those
4127.6 -> worksheets
4128.96 -> i do hope you went and ungrouped them
4131.04 -> now that we're done with that
4133.359 -> that's important to do and at the top it
4135.279 -> doesn't say grouped otherwise it's going
4137.199 -> to be hard to do this next parts
4140.719 -> now what i want to do with this
4142.08 -> spreadsheet is i want to make it easier
4143.6 -> to browse through digitally
4146.08 -> i want to make it easy so as we scroll
4147.839 -> the headers don't get lost
4150.319 -> because as you see i scroll to a certain
4151.759 -> point i can no longer see the header
4153.279 -> names
4154.799 -> it hides them from me
4157.199 -> but there is a way to lock those in
4158.64 -> place so as i scroll
4160.96 -> these first four rows will stay into
4162.96 -> place
4165.759 -> in order to do something like this
4168.719 -> we essentially have to format the view
4172.08 -> of our excel workbook
4175.199 -> now in the view tab we actually have a
4177.92 -> command group
4179.759 -> known as window
4182.48 -> and a very convenient tool that i have
4184.159 -> placed in there is freeze panes
4187.52 -> freeze panes will actually if i have
4189.759 -> let's select row five
4192.799 -> will actually freeze
4195.36 -> everything above
4199.199 -> the selected
4201.52 -> row
4204.08 -> so if i end up doing this with row five
4206.56 -> four three two and one will be frozen
4210.48 -> brace panes
4212.8 -> and as i scroll
4215.679 -> now the top is always locked into place
4219.28 -> i can always go to freeze panes and
4220.96 -> unfreeze them as well
4224.239 -> and there's also a ability to lock in a
4226.96 -> column as well if i wanted to lock in
4228.8 -> the
4229.76 -> salesperson names maybe
4233.28 -> i can always select the column
4236.56 -> and everything to the left of the column
4238.88 -> will be frozen
4240.32 -> when i select freeze panes
4250.8 -> i'm going to undo that
4252.8 -> there also is a situation where
4254.239 -> sometimes you want to freeze maybe the
4255.84 -> first four rows
4258.159 -> and also the first column itself
4261.92 -> and to that i'd say you have to select
4263.76 -> the intersection of the two
4266.159 -> of the row and the column where they
4267.6 -> meet
4268.8 -> and if you have your cell address box
4270.32 -> here
4271.6 -> right over here
4273.36 -> and you go to freeze panes it's going to
4274.96 -> actually lock in
4277.44 -> this area
4279.52 -> as well as the top area for me
4282.48 -> so two areas and once let me show you
4284.159 -> that
4286.56 -> and now i can go ahead and scroll left
4288.239 -> right and even up
4290.4 -> and down
4292.8 -> and freeze panes is locked into place
4296.719 -> i do want you to try this feature it's
4298.64 -> extremely useful when you have large
4300.4 -> spreadsheets
4302.32 -> but if you do plan on ever using the
4304.64 -> page layout view
4307.76 -> okay very convenient when you're
4309.28 -> printing out a workbook it shows you how
4311.04 -> everything's gonna fit onto the page
4314.32 -> it's actually going to tell you that
4315.84 -> freeze panes
4318.159 -> is not compatible with that feature
4321.28 -> which will be talking about shortly
4323.84 -> and this feature here will unfreeze the
4327.84 -> when i hit ok
4330.32 -> and it just shows me that the totals the
4332.4 -> goal met and even the date cell
4335.679 -> are going to be on a separate sheet now
4337.199 -> of course i can always move things
4338.56 -> around like if i wanted the date all to
4340.56 -> be on one page
4342.719 -> i know how to move things and i can grab
4344.64 -> the border
4346.32 -> i can just move it a little closer there
4349.12 -> and i'm going to make this column just a
4350.4 -> little bit bigger
4356.32 -> there we go
4357.44 -> so everything's still on one page there
4360.159 -> i'm gonna get the best fit for
4361.28 -> everything one more time
4362.88 -> looking good
4364 -> i can leave this on this page or i can
4365.76 -> move it somewhere else on the page i
4367.28 -> make the columns a little bit smaller to
4368.88 -> make things fit but maybe i don't want
4371.04 -> this on the page i don't want to print
4372.56 -> out this thing i can only print one page
4374.4 -> here
4375.6 -> here let me go back to the normal view
4377.199 -> now that i fixed
4378.8 -> the page and i moved the date onto one
4381.52 -> sheet
4382.88 -> yeah so page layout view will actually
4385.12 -> disable freeze panes
4387.04 -> and it even leaves this little marker
4389.6 -> letting you know where the page cut off
4391.6 -> giving you an idea for the future if you
4393.199 -> ever need to make some edits
4395.92 -> extremely useful i do want you to use
4398.239 -> freeze panes
4399.76 -> and i want you to go to page layout view
4402.48 -> and then move the date
4404.4 -> and then notice how you have the line
4405.84 -> and freeze pane is no longer on at that
4408.239 -> point you can leave it off that's fine
4410.88 -> or you can leave it on that's up to you
4412.88 -> but i'm going to leave it off for the
4414.239 -> remainder of the time and i'm going to
4416.08 -> go ahead and now that we've covered
4418.88 -> the ability to freeze pains
4422.159 -> print out things
4424.239 -> i want to talk a little bit about
4426.64 -> how
4427.44 -> we can actually save
4430.32 -> the work that we've created and maybe
4432.08 -> reuse it
4433.36 -> reuse it for another month by adding in
4435.84 -> some new raw data
4438.239 -> so i'm going to design this spreadsheet
4439.679 -> make it a little prettier
4441.28 -> and i want to end up saving it what we
4443.6 -> call as a template file
4447.6 -> so that i can always access it when i
4449.44 -> open up excel
4450.88 -> and i can re-add the data for another
4452.8 -> month if i need to
4455.679 -> so i got a request from my team and they
4458.239 -> asked me to make two different versions
4460.4 -> of this workbook
4462.56 -> they asked me to make one that actually
4464.239 -> shows the total and whether or not the
4466 -> goal was met
4467.76 -> and they also asked me to build another
4469.199 -> one
4470.56 -> without these two columns visible
4473.6 -> so in order to do that i'm going to take
4475.28 -> advantage of a feature
4477.199 -> known as custom views in my view tab
4481.44 -> and i'm going to save a view with all
4483.52 -> the data
4484.96 -> then i'm going to hide some columns and
4487.04 -> i'm going to save another view
4488.719 -> so that way if we ever need to toggle
4490.32 -> between the two views
4491.92 -> we have those two views saved and we
4494 -> don't have to constantly hide and unhide
4495.679 -> things manually
4497.92 -> the very first thing that you want to do
4499.6 -> when using custom views that's located
4501.92 -> in the
4502.719 -> view tab
4504.159 -> is to always add the original view with
4506.4 -> all the data showing
4508.48 -> so that way you can always revert back
4509.76 -> to it when you switch to another view
4513.28 -> i'm just going to call this original
4516.48 -> and hit ok
4518.32 -> and i've just saved a view
4520.48 -> and now i want to hide these two columns
4523.199 -> by right clicking and hiding them when
4524.88 -> they're selected
4527.76 -> and i can save another view
4532.719 -> known as hidden
4536 -> and when i hit ok
4537.679 -> what this is going to do is it's going
4538.88 -> to save the two views so if i ever need
4540.719 -> to go to the original and hit show
4543.04 -> i can browse the data if i need to print
4544.96 -> this out
4546.159 -> i can go to the hidden and hit show
4548.56 -> and i can print this version out
4552.08 -> so this is a very convenient feature i
4554.08 -> do want you to try building at least two
4556.159 -> custom views one original and one hidden
4558.56 -> one before we move forward there
4561.28 -> go ahead and give this a try
4563.199 -> come back and then we'll go ahead and
4564.88 -> save this as a template
4570 -> all right we've done a lot to this
4571.679 -> workbook
4573.52 -> now if you do want to use this every
4575.28 -> month something you can do is you can
4578.159 -> save it as a template
4580.8 -> templates are accessible directly from
4582.719 -> the file tab
4585.36 -> under more templates
4587.6 -> let me actually save this document or
4590.08 -> this workbook i should say as a template
4591.84 -> by removing
4593.28 -> all of the sales data that's currently
4595.679 -> active in there
4597.6 -> and i'm going to leave all these
4598.64 -> formulas in place because once i
4600.48 -> actually find
4602 -> and add these formulas these sales it's
4604.08 -> going to fill in the formulas for me for
4605.679 -> next month but i just want to save the
4607.36 -> template for right now
4610.96 -> now this date is always going to change
4613.04 -> to the correct date so i'm going to
4614.4 -> leave it
4616.08 -> but the big thing with saving templates
4618.159 -> is changing the way you save the file
4621.6 -> when i go to the file tab and i choose
4623.36 -> to save a copy
4625.28 -> this time when you choose to save the
4627.44 -> workbook
4628.8 -> you're going to save it as an excel
4631.12 -> template file
4634 -> it's automatically going to change the
4635.6 -> location of where this file will be
4637.52 -> saved it's going to save it in a folder
4639.12 -> known as custom office templates
4642.88 -> and when you hit save here you're now
4644.48 -> going to have access to this template
4646.32 -> directly from the file tab
4648.8 -> under your more templates area
4652 -> there will be a personal section built
4655.679 -> with all your personal templates that
4657.44 -> you can always pin
4659.36 -> directly to the front
4661.12 -> and the next time you restart excel
4662.96 -> you'll actually have access to it
4665.04 -> directly from the start screen or the
4667.52 -> file tab like i have access to it here
4670.56 -> and that way you can always get your
4671.76 -> monthly sales data quickly inputted and
4674.32 -> you can always track the metrics in all
4676.64 -> the format all the layout will stay
4678.4 -> consistent
4679.76 -> all of your custom views will stay
4681.6 -> consistent
4683.12 -> and if you ever need to print out this
4686.32 -> we're gonna go through
4688.159 -> spell checking this
4690 -> and the print
4691.44 -> preview of this coming up next
4694.32 -> before we conclude excel 2021 beginner
4698.719 -> hang in there we're almost there
4700.96 -> just a few more goodies on printing in
4703.28 -> the next upcoming videos
4705.36 -> and a few more goodies on spell check we
4707.84 -> want to make sure all of our words are
4709.92 -> spelled correctly and how we can add
4712 -> certain words to our dictionary if we
4714 -> need to
4716.8 -> now something that is worth a mention in
4719.04 -> excel word and even powerpoint is the
4721.28 -> review tab
4722.96 -> before it comes time to print out
4724.64 -> something i want you to know that you
4726.4 -> have a proofing command group
4729.12 -> this proofing command group will allow
4730.88 -> you to run a spelling check
4733.44 -> and you can go ahead and run it on the
4734.719 -> entire workbook from the very beginning
4737.36 -> and you can either add things to the
4738.96 -> dictionary if it's a word that you want
4741.04 -> to not show up as a misspelled word or
4743.36 -> you can change them to the correctly
4745.04 -> spelled version of the word like my word
4746.96 -> average there
4750.64 -> once the spell check is complete you're
4752.32 -> actually in good shape to print out this
4754.239 -> document
4755.92 -> now in the previous videos we did talk a
4757.679 -> little bit about how we can use
4759.76 -> the view tab
4761.199 -> to do a page layout view
4763.6 -> and that's when we learned about custom
4765.199 -> views to hide those two columns that
4767.04 -> were showing up
4768.56 -> but what if we had a situation where we
4770.48 -> had to print out those two columns what
4772.64 -> options do we have when we print
4775.12 -> we're going to talk about that coming up
4776.64 -> next
4777.679 -> now that we've done a spell check we've
4779.44 -> designed the spreadsheet and we've saved
4781.199 -> it as a template it might come time to
4783.28 -> print a hard copy to see how that looks
4786.239 -> before printing it we have several
4788 -> settings that we can toggle to to make
4789.6 -> sure we find the best print for this
4791.84 -> spreadsheet
4793.04 -> hang tight before we move on to that i
4795.52 -> do want you to practice this though
4797.28 -> practice running a spell check remove a
4799.679 -> letter or two on some of these words and
4801.199 -> run a spell check from the review tab of
4803.28 -> your workbook
4804.4 -> and then come right back we're going to
4806.32 -> talk about printing a worksheet
4810.32 -> so now that we have this workbook all
4812.159 -> finalized
4813.44 -> we ran our spell check
4815.44 -> i want to point out a print
4817.6 -> keyboard shortcut of course is control
4820.32 -> and the letter p that's going to take
4821.84 -> you to the print page
4823.52 -> where we can see a thumbnail of the
4825.04 -> worksheet that we're going to print
4826.96 -> and our print settings that we can
4828.64 -> toggle through
4832.239 -> when i go over to control p
4835.76 -> i also have the option of actually
4838.4 -> clicking on the file tab and hitting
4840 -> print
4841.04 -> just remember it's always a keyboard
4842.8 -> shortcut ctrl p to take us to the same
4845.12 -> exact area
4847.12 -> and something that we always have access
4848.8 -> to is our print settings where we can
4850.64 -> always print the active sheet
4852.8 -> our entire workbook if there's multiple
4854.719 -> sheets we need to print
4856.48 -> or just a selection if we want to just
4858.4 -> highlight maybe a chunk of sales
4861.04 -> just the salesperson version last we can
4863.84 -> always go to file and print
4865.84 -> and change this to just selection
4868.88 -> now we got a list of just the sales
4870.32 -> people
4871.679 -> so definitely useful print options here
4874 -> if we browse through them i'm going to
4875.28 -> go back to active sheet
4878.08 -> if you do end up needing to print out
4879.92 -> these two additional columns and you
4881.6 -> want it all to show up on one page
4884.159 -> a great useful tool in the print
4885.84 -> settings is the fact that we can switch
4887.679 -> the print
4888.88 -> to a landscape view this might add an
4891.199 -> additional one to two columns on the
4892.8 -> spreadsheet
4894.719 -> if that's not the case you can always
4896.239 -> switch this back and it'll print out on
4897.76 -> a separate sheet
4900.56 -> or you can always use a larger paper
4903.12 -> size
4904.88 -> there is a catch this your printer has
4906.719 -> to support that paper size
4908.96 -> same with margins you can adjust the
4910.96 -> margins to have your own custom margins
4912.719 -> but if you make them too far out your
4914.88 -> printer may not support the print
4917.199 -> to actually go that far out
4919.92 -> if all else fails
4921.92 -> and you need to show everything on one
4923.76 -> page you always have scaling options
4926.56 -> where you can fit all the columns on one
4928.4 -> page or kind of shrink things to make it
4930.239 -> work
4931.52 -> you also have rows on one page
4934.8 -> it'll fit all the rows on one page
4936.4 -> sometimes it comes not even legible
4938.56 -> because there's so many rows that they
4940.48 -> squished in there
4942.48 -> and we also have an option just to
4944.08 -> shrink all the rows and the columns just
4946.639 -> to make it work on one page this is a
4948.96 -> last resort option to me the scaling
4950.96 -> options but overall i am glad they're
4953.36 -> there
4955.28 -> so
4956.159 -> now that we've gone through this here
4957.84 -> these are our print options that we have
4960 -> it's usually recommended to first run
4961.76 -> the spell check like we did in the
4963.199 -> previous video
4964.88 -> and once you're ready with that you can
4966.56 -> print out a copy of this worksheet see
4968.4 -> how it looks on actual live paper and
4970.8 -> maybe post it to that bulletin board
4972.719 -> like we were talking about the very
4974.159 -> beginning of this video
4977.76 -> give yourself a round of applause you
4979.36 -> have successfully completed both modules
4981.52 -> of excel 2021 beginner
4983.84 -> i hope you take the excel intermediate
4985.76 -> course where we're going to cover a lot
4987.04 -> about report building and more about
4989.12 -> text functions
4991.199 -> very interesting stuff in that session
4992.88 -> as well
4994.88 -> hang tight
4998.199 -> congratulations you have successfully
5000.56 -> completed excel 2021 beginner i hope you
5003.12 -> take the intermediate and expert session
5005.199 -> that we have
5007.04 -> in this session we did cover two modules
5009.36 -> module one was excel essentials an intro
5012.239 -> to formulas and functions in module 2 we
5015.36 -> focused on formatting spreadsheet design
5017.6 -> and page setup and printing i do want to
5019.92 -> thank you all for sticking through let
5021.679 -> me know what you thought about this in
5022.96 -> the comments tell me what your favorite
5025.12 -> feature was
5026.8 -> inside of this entire course in the
5028.32 -> comments as well that's a good one i
5029.76 -> want to hear that
5031.6 -> if i had to put a name on my favorite
5033.84 -> feature it would definitely have to be
5036.719 -> the ability to save templates
5039.12 -> that can save an immense amount of time
5040.88 -> if you use that so at this point in time
5042.88 -> i just want to thank you all my final
5044.48 -> thank you until the next video let me
5046.88 -> know in the comments what you enjoyed
5048.56 -> what parts
5049.92 -> you found the most interesting i hope to
5052.56 -> hear from you soon
5054 -> take care everybody
5057.28 -> thanks for watching don't forget we also
5059.679 -> offer live classes and office
5061.36 -> applications professional development
5063.52 -> and private training visit learnit.com
5065.92 -> for more details please remember to like
5068.48 -> and subscribe and let us know your
5070 -> thoughts in the comments thank you for
5072 -> choosing learn it
5080.19 -> [Music]
Source: https://www.youtube.com/watch?v=bF31VEFvMmY