Microsoft Excel - From Beginner to Expert in 1 Hour | Bobby Gibbs | Skillshare

Microsoft Excel - From Beginner to Expert in 1 Hour

Bobby Gibbs, Making Technology Fun

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
75 Lessons (1h 38m)
    • 1. Microsoft Excel Promo

      1:14
    • 2. Welcome To The Learn Excel 2013 in 1 Hour Course

      0:56
    • 3. How To Technology Exclusive Facebook Community

      0:42
    • 4. Overview

      0:30
    • 5. Excel Tip #1 - The Power of The Ribbon

      1:38
    • 6. Excel Tip #2 - Secrets of The Quick Access Toolbar

      1:50
    • 7. Action Step - Start Excelling With The Ribbon & Quick Access Toolbar

      0:23
    • 8. Overview

      0:27
    • 9. Workbook Guide #1 - Creating a New Workbook

      1:04
    • 10. Workbook Guide #2 - Saving a New Workbook

      1:48
    • 11. Workbook Guide #3 - Navigating within a Workbook

      1:00
    • 12. The Real Meaning of WWW #1 - Add & Remove Worksheets

      0:27
    • 13. It's All in The Name - Renaming a Worksheet Tab

      0:25
    • 14. Copy & Move Worksheets Between Workbooks to Boost Productivity

      1:48
    • 15. The Real Meaning of WWW #2 - To Hide or Not To Hide?

      0:51
    • 16. Action Step - Work With Those Worksheets & Workbooks

      0:22
    • 17. Overview

      0:31
    • 18. It's All About The Numbers, Dates and Text

      1:48
    • 19. Cells Step #1 - Editing Data

      1:11
    • 20. Cells Step #2 - The Incredible Benefits of Copy, Cut & Paste

      3:00
    • 21. Cells Step #3 - Drag & Drop

      1:39
    • 22. Secret #1 - Adding & Deleting Columns & Rows

      1:51
    • 23. Those Wonderful Worksheets - Ease of Use with Find & Replace

      1:40
    • 24. Secret #2 - Adjusting Column Widths & Row Heights

      1:02
    • 25. Case Study - "If Only I Could Sort Out This Data"

      3:17
    • 26. Action Step - Enter & Edit That Data

      0:20
    • 27. Overview

      0:28
    • 28. Powerful Cells #1 - Adding Impact with Borders

      1:33
    • 29. Powerful Cells #2 -Merging Cells

      1:16
    • 30. The Benefits of Multiple Lines - Wrapping Text in Cells

      0:40
    • 31. Hidden Secret #1- Changing Merged Cells into Standard Cells

      0:54
    • 32. First Impressions Count - Changing Font Size, Type & Color

      1:24
    • 33. Hidden Secret #2 - Applying The Delightful Date Format

      1:52
    • 34. Hidden Secret #3 - Why You Need Number Formats

      1:06
    • 35. Case Study: Table Please

      2:40
    • 36. Action Step - Time To Format

      0:24
    • 37. Overview

      0:26
    • 38. Part #1 - Transform Your Worksheets with Print Titles

      1:36
    • 39. Bonus Tip #1 - Save Money with Print Area

      0:57
    • 40. Part #2 - Finding The Right Paper Size & Orientation

      1:26
    • 41. Bonus Tip #2 - How Margins Will Maximise Your Printing

      1:46
    • 42. Part #3 - Scaling: A Must for Perfect Printing

      1:55
    • 43. Adding Authority - Headers & Footers

      4:04
    • 44. Case Study - Now How Do I Print My Work?

      4:16
    • 45. Action Step - Print Those Worksheets

      0:22
    • 46. Overview

      0:27
    • 47. Basic Formulas #1 - Addition & Subtraction Formulas

      2:06
    • 48. Basic Formulas #2 - Multiplication & Division Formulas

      2:36
    • 49. The SUM Function - The Core of Excel

      2:21
    • 50. Save At Least 33% of Your Time with Copying Formulas

      1:28
    • 51. Important - Absolute Cell Referencing

      2:09
    • 52. Skyrocket Your Results - Using Formulas Across Worksheets

      1:33
    • 53. Bonus Step #1: VLOOKUPS

      3:44
    • 54. Bonus Step #2: IF STATEMENTS

      2:15
    • 55. Action Step - Start Experimenting with Functions & Formulas

      0:20
    • 56. Overview

      0:28
    • 57. Graphs & Charts Part #1 - Creating Graphs & Charts The Quick Way

      2:05
    • 58. Graphs & Charts Part #2 - Creating Graphs & Charts The Quicker Way

      1:19
    • 59. Bonus Step - Using Filters

      0:19
    • 60. Acing Pie Charts

      1:19
    • 61. Sparklines - How Mini Graphs Will Revolutionise Your Worksheets

      1:00
    • 62. Action Step - Create Those Graphs & Charts

      0:23
    • 63. Overview

      0:24
    • 64. PPT #1 - Creating Superior Pivot Tables

      1:13
    • 65. PPT #2 - Sorting & Using Pivot Tables

      1:19
    • 66. Action Step - Put Those Pivot Tables Into Practice

      0:21
    • 67. Overview

      0:24
    • 68. Phase #1 - Changing Cell Appearances Based on Their Value

      1:14
    • 69. Phase #2 - The Ease of Finding Duplicate Values

      0:52
    • 70. Rule #1 - Top vs Bottom Rules

      1:40
    • 71. Rule #2- How Being Average Rules

      1:05
    • 72. Rule #3 - Removing Conditional Formatting Rules

      0:44
    • 73. Bonus Step - Color Scales, Data Bars & Icon Sets

      0:58
    • 74. Action Step - Start Conditional Formatting Now

      0:22
    • 75. Final Action Step - Putting It All Together

      0:42

About This Class

Wouldn't it be great if you could learn Microsoft Excel in just 1 hour? Imagine going from beginner to expert in just 1 hour.

All this is possible in just 4 easy and simple steps.

Having taught 1000s of beginners Excel, they were instantly able to apply what they had learnt within this course.

Here we welcome you to the Learn Microsoft Excel 2013 in 1 hour course, where you'll learn how to use Excel in real life scenarios based on a variety of case studies.

Guarantee: By the end of this course you will be able to use Excel in real life scenarios within just 1 hour.

Excel can be used for…

  • Weekly planners
  • Invoices
  • Payroll systems
  • Budget keeping
  • Listing and sorting data
  • Bookkeeping
  • Customer and employee data
  • Scheduling
  • Almost anything that requires and uses data

And the best part is you can even use it at…

  • Home
  • Work
  • An educational establishment
  • Or just for fun - the possibilities are unlimited! 

Here we will focus on…

  • Creating worksheets which will turn you into an Excel Expert in less than 1 hour
  • Applying Excel to real life situations to enhance your results in your working environment
  • Using the right tools that will boost your productivity by at least 33%

Plus you'll receive 2 Bonuses for enrolling which are…

Bonus #1: The Excel 2013 Elite Formula Cheat Sheet - formulas available on demand

Bonus #2: The 4 Step Excel 2013 Functions Guide - makes using functions and formulas simple and easy

Enroll and Take Action

Join now and make the commitment of going through our course. It is sure to enhance your skills and level of knowledge with Excel.

We look forward to seeing you in the course.

See you then

How To Technology

Transcripts

1. Microsoft Excel Promo: Welcome to the Learn Excel 2013 in one hour course where you will learn everything you need to know about Excel 2013 in just one hour. We at how to technology have trained thousands of students on a number of different technical courses. With are fully accredited Microsoft certified instructors. Each of our instructors have at least 10 years of technical experience on many have worked with organizations such as Microsoft, who I spend many hours trying to learn. Excel 2013 by yourself, when you can have immediate access to everything you need to know in one place are four step formula was designed from the ground up to teach Excel 2013 in the shortest amount of time possible. We understand that you're here to learn and not waste time on our four step formula guarantees this. The ideal student for this course is someone that wants to learn everything about Excel 2013 in just one hour. No prerequisites are necessary for this course, and there's also a 30 day money back guarantee, so there is no risk on your end. Check out the course description on. We look forward to seeing you in the course 2. Welcome To The Learn Excel 2013 in 1 Hour Course: Hello and welcome to the Learn Excel 2013 in one hour from beginner to expert course. My name is Bobby, and first of all, I like to start by saying Thank you for dedicating your time and energy into developing and enhancing your skills and knowledge off exam. With the use of a four step formula we're going to delve into looking at. There is exercises, case studies, activities on video to tourists ensure you're definitely getting the most of this course. There will also be several action steps view to do throughout the course. Make sure that you do them as they will help with making a ray of your progression of fast and seamless as possible. In addition, we also recommend that you take notes to further ensure you're gaining the best and most optimal learning experience on to make learning as simple as possible. With that said, I will see you in the next video 3. How To Technology Exclusive Facebook Community: Hello and welcome to the Haras to Technology Support Network. This group has been specifically designed for those who want to grow and take their technical skills to the next level as well as that. It's for those who want to your great results on be provided with Continent office. True value. So if you want to be amongst office who are like minded on who are hungry to learn more, this is the group for you. So all you have to do to join this group is is to go to the euro facebook dot com forward slash groups Ford slash How to technology on Be sure to introduce yourself. Go to that your own on. I'll see you guys in the Facebook group. 4. Overview: This one hour EXA 2013 course has been broken down into four easy and digestible steps. To make sure your experience of this course is the best, we recommend that you spend at least 15 minutes on each entire step. Hair will be going over the fundamentals off exiled 2013 to ensure you have some solid ground to work with will be covering the basics, such as understanding the ribbon on a quick access to double in the next video ago. These in more detail. 5. Excel Tip #1 - The Power of The Ribbon : XO, Tip number one, The power of the ribbon. Okay, so at the top affects, so is already been on. As you can see, we've got a number of different tours that we can actually use now. These tours, they come on this several groups. So, for example, we have editing cells, stars number alignment for on clipboard. Okay, Onda, we also have above we have several tabs. So you've got home Insert page loud formulas. Data review on view. There's also developer Tub, which is in currently available. So in order to bring the up if we right click the selected head are go to customized of ribbons. Select, develop our and then click. OK, we have the developer tab now available, as you can see her. So if we go back to our home tab on on the group alignment, as you can see, some groups they have small icons in the bomb, right? Thes allow further options. So, if I was to click on one of these, I've now got some extra options available. But for now, we're gonna close that now. If I was to double click a top, the ribbon minimizes Andi. If I was to select that time again. A NASA spread she as you can see the ribbon no longer Pirmin e stays. So in order to bring that back or you go do again is selected Tab No, double click it once more on. Now. If I select a spreadsheet, the ribbon stays back permanently on. That's an overview off the ribbon. 6. Excel Tip #2 - Secrets of The Quick Access Toolbar : xar Tip number two Secrets of the Quick Access Toolbar. Now, if you look the top left of Excel, you'll find a quick access toolbar. The's air shortcuts to commands have been excel. Such a saving a spreadsheet. Now, if you click the drop down arrow, just hair, you'll notice that you've got other commands that you can add to the quick access to a bar . So if I was to select quick print, the quick friend option is now available. Andi options are already within. A quick access tour was have ticks next to him. So now if I was to go to show below the ribbon, the quick access tour bomb moves to blow the ribbon instead of on top. So if I select the IRA again, I can now show above the ribbon another way of accessing the quick access tour bus to go too far. Then go to options on hair, your secret access toolbar so I can select it him. This is where I'll find Mawr selections for the quick access to or ball, which I can now add. So, for example, if I was to select the car option, I can now select at Andi. I could select the email. Auction is what? But let's just say I know what's get rid of caught. So again we'd have to do is just click it and then click Remove on. That option is now gone. I can also changed, ordering off the options that I have available on the right inside. So if I select quick friend on, I want to move up one mullah. The options are now reordered on. I could again just to show you I could change the order on do and bring it down a few Andi with that, I could just select. Okay, on that is the quick access to a ball. 7. Action Step - Start Excelling With The Ribbon & Quick Access Toolbar : excellent. You finish the first section of this course. So just as a quick recap, you should now have a good understanding off how the ribbon and a quick access toolbar works. Now it's your turn. Experiment intends that these two features as their fundamentals towards using Excel off doing this. Daniel, all set for the next section. 8. Overview: working with worksheets and wondrous workbooks hair. We're going to be covering the basics of how workbooks and worksheets function. We've applied our free step system to this section to make sure things are a simple is possible. By the end of this section, you'll be able to perform tasks such as navigating within a workbook as well as hiding and in hiding worksheets. I'll see you in the next video. 9. Workbook Guide #1 - Creating a New Workbook : what God number one creating a new workbook. Now, whenever you create a new workbook with Vinick, so by the fall it will say book one at the top, their several ways of actually creating a new workbook within Excel. One method is to do control end on your keyboard On as you can see we now have book to showing another method is to go to a quick access tour bar and select new icon now book free affairs here if you do not have the new icon available, if you click the arrow on the quick access to or bar and select knew that I could should. Now a pair on another method is to go too far, then select new Andi. As you can see, we have the option of creating a blank workbook or choosing several templates. There are also categories above where you can also select a template. So for now, we're just gonna go a blank workbook on. As you can see, we have book for available 10. Workbook Guide #2 - Saving a New Workbook : What book? God number two, saving a new workbook. Okay, so you've got some work on. Do you want to save? What do you do? Okay, so there are several ways that you can save a workbook. Women excel. One method is to go to the quick access toolbar onto selective save my corn. Another method is to do control s on your keyboard, Onda. A novel Meffert is to go too far thing to go to save and then to select where you actually want to save your work in this case. Well, so it's a computer, and then I would select the desktop. Andi here. As you can see, I'm prompted with putting a name for my father. So just for the sake of things, I'm gonna put my work shit, and then I'm going to select save Okay, on. As you can see now, my fall is could my work shape. But what if I actually wanted to save another version off my same work? Shit. So, in other words, what if I want to change the phone name? What I do for this I have two options. I can either purse F 12 on my keyboard or I can go too far. Go to save us, go back to wherever I want to save my work. So again I'm going to select the desktop on. I can now select a whole new name. So just for the sake offense, almost say my worksheet. Version two. Then I'm going to select safe Andi as you can to that's now, but not to my worksheet. Version two. 11. Workbook Guide #3 - Navigating within a Workbook : What? What? God number free navigating within a workbook. Okay, so here you have your vertical scroll bar, which you can if you click on, you can slide like so you have your horizontal scroll bar. Just here as well. Is that you have the zoom in slider. So if you move it to the right, you can zoom in as you can see. And two left this dizzy. Um, Out. Okay. And here you'll find your work sheets. If you want to add more worksheets, you just click on the plus Bond. Like so on again. Now we have shoot for you. Worksheets are known, also known as spreadsheets. As you may already know, Andi within a worksheet, you have cells as you can see her. So if we click on cell, do you four, for example, we can see it showing up here, which is the name books on. Besides the name box, we have the formula bar. So if I was to type the world, let's just say hello. It also occurs in the formula bar. I know it's not begin within a workbook 12. The Real Meaning of WWW #1 - Add & Remove Worksheets : I don't remove work. She's okay. So, as you can see over have currently got sheet one showing. So if you want to add another work sheep, we go do this. Just click this. Plus, I couldn't just heard like So now we've got sheet to appear in, but let's just say we want to get rid of sheet to. So in order to do that, if you right click machine and then select elite de Rigo sheets has now been removed. 13. It's All in The Name - Renaming a Worksheet Tab : renaming a worksheet tab. Okay, so over has, you know, we have sheet one. So let's just say we want to rename it. All we do is right. Click sheet one on. We go to rename just here on. Let's just say we want to call it my work Shit on. Just enter on. There we go. You've renamed the work share? 14. Copy & Move Worksheets Between Workbooks to Boost Productivity : Copia move worksheets between workbooks to boost productivity. Okay, so here, as you conceive God worksheets my work she on my worksheet to we're gonna do is we're gonna firstly, copy this work she and we're gonna put into a novel workbook. So if we actually click the view tab just up here on if we select switch windows as you can see, we've got to work books which are commonly open moving excel. So if we select the my worksheet version to workbook, it brings us this. I'm working a transfer that previous worksheet into her. So if we go back now in order to do so, if we right click my work shit on we go to Bugle copier on we select. As you can see, we've got the my worksheet worksheets selected already on. If we select our destination, which is on the hair now we select. Okay. Oh, and just before you did that, you want to also make sure create a copy selected as well. And now we select Okay, As you can see my work, she has been copied over onto into the workbook. My worksheet version two. And so, as you can see there. So now if What if we just went to move or work she over? In order to do that, we just again we'd right click. The worksheet will do this for my work. Shoot Version two, go to Movil. Copy. This time, we're not going to select Create a corporate. We're just going to select the work shit that we want to work with. Select destination on select. Okay, Andi. As you can see, that's not been moved over on. If we go back to a previous work, she that's no longer available. 15. The Real Meaning of WWW #2 - To Hide or Not To Hide? : to hide or not to hide worksheets. Okay, so let's say you wanted to hide a few worksheets bought Before we do that, we're gonna actually add a few more. So if I click plus by an Okay, so we have free worksheets on Let's say, for example, you wanted to hide sheep free. So what we do in order to do that is right. Click up on click Hide on. As you can see, a shoot for is no longer showing what if now we want to show again. So in order to do that, we gotta do is click a sheet that's currently showing That's right. Click a sheet as commonly showing on. Go to Owen. Hide on as you can see, um, shoot, Freeze now available. So if we select Okay, Onda, we know have sheep for again 16. Action Step - Work With Those Worksheets & Workbooks : congratulations. You've now finished off this section. You should now have a good understanding of how worksheets and workbooks operate. So now it's time to take some action and experiment. Give these exercises to try on, put what you've learned to the test. So with that said, I'll see you in the next section. 17. Overview: timeless secrets of editing and entering day inside cells. Okay, so I've been this section. We're gonna be looking at different data types, such as numbers, dates and texts. We're gonna be looking at columns and rolls and as well is that we're gonna be looking at cells. You should by the end of this section, you'll be able to use to copy cotton pays functions, and as well is that you'll be able to add columns and Rosa's Willis delete them as well. So with that said, I'll see you guys in the next video. 18. It's All About The Numbers, Dates and Text : numbers, dates and text. Okay, so let's just say, for example, I want to input some data over the sir, so Well, as you've probably guessed that you selected herself example, I'm going to select the store you free on? We're just gonna put in the word Hello. So I should in see that pairs have been the sore on within the formula ball. Now, there's actually two ways off confirming the day that you've input. So, for example, we can either press enter on if I was to just redo that again within the sort of free type . In the word holla, we can actually use thes commander. We can hit into her, which is the tick. So again, that's been entered. Now, let's just say once more you have the same one hella on. Did you didn't want to put that word in? We'd after the issues pressed escape key, like so or again if we have Hello, You can cancel it by personal cross. Just her. Okay. So same rules apply to numbers. Really? So you just selected So Andi, for example, we can import numbers and we can here enter Well, we also don't have to important. So again, just to show you we can use Tab, for example and what are again We've got our numbers. Ondas Well, is that we can actually use dates. So let's just say if we have the 12th of march from repressed and tall, if we not select that sell, it would give us the 12th of March for this year. I should consider in the formula bar on another. We're actually doing the 12th of March would be just to do 12 slash oh, free. And if we hit enter again. We've got that automated within formula bar. 19. Cells Step #1 - Editing Data : editing data. Okay, so they're a few ways you can actually edit data within Excel. One method is to click on this other. You won't actually read it on to just type over it. Now, if I was to enter, I'm left with white typed. However, if I was to click on that cell once again on If I was to type over on, then select escape, it reverts back to wide Previously had. So again, just to use the same. So again, If we go to the formula bar now, I can also change what's input in that self from there. So began Just to show you If I type the word hello and hit enter the word Hello remains in this So you for Oh, sir e free. Okay, So another method, in fact, off changing their contents of the sun is to select it once more on inter press F two on Did you can now change the words or whatever is important than that. So like so on. Once again Just hit. Enter 20. Cells Step #2 - The Incredible Benefits of Copy, Cut & Paste : the incredible benefits off. Copy, Clown paste. Okay, so two copier. So, first of all, you obviously want to start by selecting the So you want a copy on? If you go to the top left and select copy option, you can see this cell E nine is now highlighted. So if we select another source such as G two on, we select paste. As you can see, this has been copied and pasted into this allergy to Okay, So in order to stop this highline or your food issues press escape on your keyboard like so ? No, if I want to copy a range of so such as from cells e to to e nine. By the way, how I did that was just started again. You just click, hold and drag downwards like so. So now I have d cells highlighted on again. I could just sit at the copy option on select herself, such as hate to on select the paste function. And as you can see, the range of cells have now been copied and pasted from hey ht th nine. So with that said, there are also all the ways of actually copy and pasting such as? So let's just say, for example, I was to use to 79. I could use control see, on my keyboard on select herself such just d 11. Andi now use control V, which will paste the value like so. So if I press escape to stop that highlight in, I can now show you guys how to actually use caught. So if I was to select the soul, that's a C nine and used caught in the top left. You can see this cell highlights as usual, identifies to select another source such as hate 11 and select paste. Not in fact, I'll do control view. As you can see, it's now been pasted in so hates 11. Okay, so you can also call opium pace Poppy cut and paste, in fact, into other worksheets. So let's just say I was to select this range from hedge to to hate each nine on we're gonna have caught him. Ondo chicken suits highlight as usual, I'm gonna caught and paste do cells into my worksheet to I'm gonna slip So I t on. I'm gonna I'm actually going Teoh do a paste hair like so And as you can see these have now been pasted from cells I to toe iron line within the my worksheet Teoh work ship. Okay, There's also another method of actually copy and pasting. So if I was to select the cell hates 11 and right click, I can also copy copy and paste from here. 21. Cells Step #3 - Drag & Drop : dragon Drop, drag and drop allows you to move the contents of one cell to another. So So just to show you what that looks like if we slept with self hate five on what you do is basically along the green board off the so hate five. If you go to one side where you see four arrows, any side will do and you click on a hold on you move it towards another cell such as J nine and release. As you can see, the contents of now move to number. So So you can also do that with a range. So if I was to select E to Teoh in line and now go to the side where I see four arrows, I could not move thes rangers SOS Teoh whereby please So, as you can see now, these soldiers have now been moved Teoh. So the for teen two t 20. Okay, so let's say if I wanted to move a cell or range of SOS to another worksheet all idea to do that again, it's just select herself. Example Andi go to decide. And as I click and hold, I also go hold onto the bottom boat on the keywords on. As you can see, I just drag. It'd worksheet our desire and just go to so as I please and release just to shoot that looks like with a range of cells. So again, just click and hold drug down outside, hold haute and go to a worksheet off my choosing on release. 22. Secret #1 - Adding & Deleting Columns & Rows : adding and deleting columns and rows. Okay, so if you wanna insert new column will you have to do select a column? Head are So I'm gonna slept column right click, go to insert. And, as you can see or other columns have been shifted towards the right, However, I can also slept more than one column. So if I was to click drag across from columns J T K. On to El on, then go Teoh inserts. Now, as you can see or other columns have shifted free places towards the right. The same also applies of Rose. So if I select road for right click good insert. As you can see, everything's been shifted downwards by one row on. I can also do the same thing by selecting more than one room and then selecting Insert Andi again. Everything's been shifted down free rose. Now, if I want to actually undo these changes, I could simply put click on do hair or I could do controls that on my keyboard, like so. Also, if I want to delete columns again, I could just select a column head are right, click and go to delete, and as you can see all the quantities for that column have now been deleted. I can also delete several columns by clicking, clicking and highlighting the columns, like so on. Going to delete on a lot or contents have not been deleted. The same applies to rose. So if I select rose seven, right click and go to deliver, all contents within that role have now been deleted on. I could also select several rows right click and early on again, or contents have been deleted. 23. Those Wonderful Worksheets - Ease of Use with Find & Replace : ease of use of finding the place. Okay, so let's say you've accidentally important wrong Valuev in a cell or maybe several cells. For that fact on, you need to find and replace these cells. Okay, well, there's a very useful talk with final replace, which is located hair. These binoculars just has. So if you select this icon and then you select to find what you can now do is in fact, if you is to import the divider you looking for. So if we type in cat and we select fine next, it will actually find all these values for for you. Sorry, Andi. If we select, find all, it will find a location or values that have the value that you're looking for. So what you can actually do as well is that you can replace these values by selecting the replace town hair on. Let's say we actually want to import the word hat. Now I can replace that cat's that has now turned to a hut, and I can apply that toe over cells as well. Or some people I could just do replace all on. As as you can see, this is all done we made six replacements. So if you just select okay with that, Andi just just doesn't I don't also many other auctions that you can select. So again, as you can see, finding replace can be a very useful asset. We've been excel. 24. Secret #2 - Adjusting Column Widths & Row Heights : adjusting column works and row hides. Okay, so if you want to adjust the whip from the coil in if you go in between two column headers , as you can see, the curse will now have to arrowheads on. If you click and drag, I've left or right, you can actually just wait for the column and just release if you want to. Quickly, however, automatically adjustable it for column to the same whip for the value of in the cell. If you just double click in between. Two column headers automatically adjust the work from a column. A similar principle applies to Roses was. So if I go in between two rows and I click and drag, welcome it the height of rose much bigger on if I was to double click, it automatically adjusts the height of a row in accordance to its value of in the cell 25. Case Study - "If Only I Could Sort Out This Data" : Okay, so you just opened up a spreadsheet within Excel, and you've got all these rows of they are. You don't know where to begin. You don't know what to do, and it needs to be swords. How do you do that? Okay, So in order to actually sort out these rows of they are if you actually highlight the entire table. So, for example, if you just click top left corner off your table on if you do control, shift and down and you press, the writer of that will highlight the first row of all the columns. And then if you press down, it highlights the entire table. And now what we're gonna do hair is when there's two options. In fact, if we should let the home tab as you can see, it's a sore and fill a hair on. What we can do is we can sort this day, are in ascending or descending order so sinister that descending order for now. And as you can see, all Dale has now been sorted in descending, although from the first column. So if you want to get a bit more advance of this, though, we can go to the day atop on. As you can see, these options are also found here on we can click on the sore. And now this is where we really against Negri. So has a bit more advanced. So actually sort by again, just very similar. Quite different columns within our table. So we can actually add levels as well and delete levels with that and even copy levels. So with that said, what we're gonna do is we're going to sort by first name. Andi. No, actually will change. It will sort by last name first. I will so on values and will do from eight. Said I'm gonna add another layer hair. Then we're gonna sort by first name values. I'm gonna go from eight set. And as you can see, this is sort at first on the first level by last names. Andi, we've got in ascending orders. Walsh, we've got Brian Sharon Fear and got anybody with the name cosign. Just one hair on your car. Oh, so it speaks for itself. Really? So that's that There has clearly been So it's so if we highlight this entire table again. So if we click here and do a control shift, right? Right. Arrow key and impressed down and go back. Okay, we can actually delete these levels. So if we just delete that Onda we saw on costume idea again on go off smallest to largest again These have not been sword in ascending or the based upon cost. Tomorrow I d. Ok, there's also You can also add filters to you table as well, which can also be very useful. So if we just click on itself in the first roll off where the columns are located on we select filter. As you can see, all these head is now have feel So for example, if I only wanted certain costume ideas when you produce, click is dropped down Arrow Just hair on we de select or on Let's just sell wanted costume idea Free seven and known only Select Okay, As you can see, these are the only roads of they are. They are now showing for costume ideas free seven and nine 26. Action Step - Enter & Edit That Data : great job. You should not have a better understanding of how to Antar Eddie and sort Deo Vinick cell So once again well done. What? Here comes the form part. It's now your turn experiment Test out what you've learned and give it a shot With that said I will see you in the next video. 27. Overview: Lee Excell Fundamentals for workbook. Former in OK, so then this section, we're gonna be looking at borders. So formatting so manipulation changing the form of a solar inter formats such as numbers or the date format adding borders two sells, like we're gonna be covering quite a bit in this section, actually, so make sure you're paying close attention on, but that said, I'll see you in the next video. 28. Powerful Cells #1 - Adding Impact with Borders : adding impact of poor this Okay, here, I'm going to show you how to add a border to a selected area. So first off, Mike Sickness click and select an area that I would work with, Like so Then if you go over hair, you know, is there's a grid icon. This section is where your prayer borders. So you just click the drop down our own hair on, for example, it Let's just say if I was to select all borders, as you can see, um, now go or borders throughout the area I selected. So if I want to revert those changes so I could just do a control Z Onda Okay, we're back where we started. So if I say that that area again and quickly dropped down error and for example, this time I selected FIC box board or just to show you, we now have a FIC box board around the area we selected. Okay, so I'm gonna do it on do or control Z Teoh exchanges on again just to give you another example. Let's say we just wanted a board in the top. So again, So if I do another control Z and now there's also the option off, actually just drawing a border without selecting the area. So if we select the drop down arrow hair and we go to draw border, we now have a pencil icon. So if I was to select herself, for example Andi, if I was to click and drag, I can now free handedly draw border. 29. Powerful Cells #2 -Merging Cells : merging cells. Okay, So if you want a merger solving Excel, first of all, you select how many sodas you actually wanna work with. In this case, I'm gonna click and drag and work with these four. So it's just here, and then I'm gonna go to merge. Incent are on. As you can see, the cells have now been merged together. However, if I was too much, several souls that have day to have been them on now, Foster, go to merge incent on this message comes up sing merging serves only keeps up, left sole value and discards the offer values. So in other words, it will only keep the value for Thursday, and it will get rid of Friday. So if I select Okay, the two cells have now been much together. What is got rid of that previous value? Okay. Just to add on to emerging stars, actually, if we just actually input some data, we've been hair so type Friday. Okay, so you've got the word Friday, but if we want to just online that more with in the middle of the actual cell, we just go Teoh middle line on, as you can see, it's not centered on. You can play around with these really? So you can align it to the top as well. It's the bottom left right and said, Our sir. 30. The Benefits of Multiple Lines - Wrapping Text in Cells : Okay, so let's say you were using Excel on. You wanted to write several lines of text within the self. So if example, if we have, they sell I six a new simple our love excel. Okay, Okay. It's it's fun, right? So, as you can obviously see if I hit Enter. This has gone over into another self now normal Terry, that if you click the same self by six and you go to rap tax, as you can see now we have several lines of text within that cell. 31. Hidden Secret #1- Changing Merged Cells into Standard Cells : changing merge cells in standard cells. Okay, so let's say I have selected several cells, like so on. I've gone to emergency and are now They've been merged. Okay, on. I want to revert those changes back. So some people all I do is I just select the merge sell, go to merge in center on Bhola. I've got my standard cells back. So let's say if I merge solos that had values of in them say Wednesday first in Friday Now I go to move. Sent all. Okay, this message comes up and I select Okay, As you can see now the cells have merged. Bought only the wife Wednesday pairs Boy, if I ever Those changes No, I've got my standard cells back. However I'm missing. Vies for first and fried there 32. First Impressions Count - Changing Font Size, Type & Color : changing form, size, collar and text. Okay, so let's say I select the cell that has Wednesday. So So f two on. I wouldn't change the font size of this simply poor. If we go here, you can adjust the font size as follows. And if I just hover over different values as you can see, the foreign sizes increasing. Aled decreasing. So I'm going to select size 16 on we've changed font size of so f two. Okay, just don't to that if we go over hair, this allows you to change the actual fun. So as you can see, we've got several fronts we can actually choose from, so I'm going to select aerial black care on If I just double click here, I can adjust the cell also and just add to that hair we can change font color. So if we click the drop that our just here, we can also change the color of fun. So I'm just hovering over a few different colors on will go for this blue. Just Andi. There's also the option off selecting increased font size if you want to scale upwards of are actually going within this inbox box just here on. We can scale downwards like so on. Another option is to actually right click and you get the same phone options available as well. 33. Hidden Secret #2 - Applying The Delightful Date Format : Okay, so in this video, we're going to be using the date format. So I select herself on I import, Let's say today, 28th of March like so and I press enter. But if I now click on that cell and go to Formula Bar, you can now see that that's been populated as 20 a slash o free slash 2015. So if I click on another so on, I'm putting 28-0, free. This time on, I hit Enter and I select that. So again same thing has happened. It's been populated on just to show you another example. Now, if I did 28th of March, I do the year 1990 I pressed Antar on. I select it. It's now been populated of slashes board. Let's just say I want to change these formats so very useful. Web Actually selecting several cells is to do hold control. So I'm just gonna click hair. So if by hold control, Andi or actually I should have done that. So let's stuff back here. So I hold control on I click several cells that I want to work with. As you can see, they've all been selected. So now if I right click on I go to format, which is former cells which located here Andi, I go to dates within category. I now have a sample off the former that I can select. So if I was to go through these options, I can now change the format of the source that I've got here for our date. So let's say we we want this format. So we want the words march showing fully. So I'm gonna click. Okay with that? As you can see, all of these cells have now changed that former 34. Hidden Secret #3 - Why You Need Number Formats : Okay, So in this video, we're gonna focus on using number format. So if I select this so hate six on, I'm gonna just import few numbers and just use the arrow key on the keyboard is to go down . Okay. Actually conceived, we've got some numbers. So I'm going to select all of these on I'm going to go up here on this box is basically the number format box. So if I select this currently, as you can see, it's set to general, which is no specific format. But as you consume, got several different four months we can choose from. So if I select number now, these numbers are not two decimal places. And I can increase or decrease these decimal places by selecting these buttons, like so as well as that. I can also change him to currency. So we've got British pounds at the moment. Accounting, short date, long day time percentage fracture, and scientific and text on If I want to, I can click on more number formats. Where now I've got even more options available for me to choose from 35. Case Study: Table Please : Okay. So, hair, we're gonna create a table. As you can see, we've got a weekly plan on board. It doesn't look very good. So we're gonna have the best spice to our weekly planet. We're gonna spice things up quite a bit. So what we gotta do first is we got selective cells we actually wanna work with. So we're going to slept this range on their several ways. You can actually create a table. One method is to go to four murders table, which is located right here. Onda. As you can see, we've got several designs that we can actually work with. So for the sake, friends, I'm going to slip this design. Andi, at the moment, if we don't select my table has headers. We get head is above just here. We get some the forehead is that you can see. So to revert those changes we're gonna click on do Cape of in quick Access toolbar. Okay. On another method off Corian tables to do controlled key on your keyboard on As you can see , the same thing pops up again. So gonna Quality Council just showed last month for this to go to insert on a quick table here, Andi Again. Same options. So this time we are going to select to my table. Has headers. I'm gonna go to Okay on. As you can see, we now have a table. OK, so if we click somewhere else, that's not within the table. Okay? I don't know if you know it's bought. We had a design tap just a moment ago. So if we now click with in our table, as you can see, that design tab has now Kurt. So right. If we now select to give some content to our table, let's say at 7 a.m. We have breakfast. So I'm just gonna put breakfast here on just just add a bit more to our table. Will say we had lunch at one PM Andi are simply for 11 PM It's bed time, so we'll just put that time. Okay, So now if we select a cell so we'll work with so d 20 on if you go to the bottom right hand corner off this. So as you can see, there's a black cross now, So if we click and drag this across on, we release this same value now. supplies to allow the other cells that were highlighted. So we're gonna do the same for lunch. So we're gonna click and select, gonna go to the border around corner of this. I'm gonna dragon release, and we're gonna deal once more for breakfast. Okay. On a That is a simple but yet quick table with that we have to work with. 36. Action Step - Time To Format : nice work and well done on completing this section. You should now have a good understanding on formatting worksheets within Excel. So again, great job. So yep. You guessed it is that time again. Experiment. See what you can do with former and within Excel on. Just keep on progressing forward And with that said, I'll see you in the next section. 37. Overview: perfect printing and page set up. Okay, so event this section. We're going to be going over printing fundamentals as well as page layout on Prentice set up. So this is going to include features such as print tie tours on headers and footers on margin. So again, grab a note pad, grab a pen wherever you need on I'll see you in the next video. 38. Part #1 - Transform Your Worksheets with Print Titles : using print titles. Okay, so in this video, we're going to learn how to use print titles, some violence just to select a cell, just to show you how much date we're working with. And if we do a controlled down, as you can see, we've got 250 rows of day of what? That were actually working with so quiet off there. So if we do a control up now, right, we're back where we started. So we have print titles just to show you exactly how Prince Sites was work for a quickly. If I go too far and select print as you can see, we've got off us column headers just for page one. So if I go to next page and click care page to that doesn't exist, so we're gonna we're gonna actually have those Head is showing for Paige to free and onwards. So now if we go back on on the page layout tab, if we select print titles Onda we select Rose to repeat at top. Now you can actually select more than one row showing at the top by just clicking and dragging like so as you can see we got dollar $1 free hands. But for now, we're just gonna work with one room. So we're just gonna click on row one like so? And I got dollar $1.1. I'm going to click. OK, Here. Okay, So now if we go to fall and select print Okay, I would go to page two. As you can see, that's now been applied here on just against show you on page three. Sam has also been applied. 39. Bonus Tip #1 - Save Money with Print Area : using Prince area. OK, so if you always want to print within a certain range of self so just to show you, for example, if we just select de cells Onda we only always wanna print within this range of cells or we can use is is the feature print area. So on the page, lower tab. If we select print area here, we still accept print area. And now if you go to fall and print, as you can see, de cells are the only souls that will get printed. So if we go back now on and we want to revert those changes if we go to print area on, we select Claire for an area and now we go to fall imprint. We've now got our 24 pages back again. 40. Part #2 - Finding The Right Paper Size & Orientation : Okay, So in this video, we're going to focus on using paper size and orientation. So in order to use paper size first, you would ensure that you're under the page layout tub, And if you click this size, I couldn't just here. As you can see, we've got different paper sizes that we can choose from so minds currently set to a four. But just simply port select one to your liking on if you want the option of more paper sizes If you just click more paper sizes here on if we could paper size hair just here. We've got drop down box on. We've got more options. Okay, on once you found one to your liking simply port. Just click. OK, here. Right on. The next part is orientation. So of orientation simply port. You've just got two options, portrait or landscape. A good thing to do when actually selecting these is just to go to prom Prevue just to see what we currently look like. So at the moment, if we select a print hair, as you can see, this is a preview of what we've currently got. So just to compare if we go back we select orientation and now we select escape. I'm going to fall on print again. So as you can see, the page looks clearly different. So, again, this is just another good point to consider her. 41. Bonus Tip #2 - How Margins Will Maximise Your Printing : Okay, So, hair, we're gonna be focusing on using margins. Margins are basically the distance between the edge of a piece of paper and where your text starts. So in order to use margins, first of all, we go to page layout. Then what you want to do is you want to select margins just here. And as you can see, we've got some margins options available to us. So we have normal. We have wide and we have narrow hair and we also have our lost costume setting Onda. We have cost of margins just here. So if we select cost of margins coatless rum bottle against margins, that's costing margins from hair this time, Andi. Okay. As you can see, we've now got into more depth with this. So here we can actually adjust top left bottom and rights margins off a piece of paper. So there will always only be for as these are the only margins you can work with. So if we select north 0.5 are thats actually entered out your 0.5. So if you just literally click on these, you can adjust them like so so nor 0.5 in for all of these, Okay? We can also just ahead and four top. We're gonna cover that in another video. And now, if we select okay. And if we go too far and select print as you can see the distance between the edge of a piece of paper where text start is much more smaller now. So this way we can actually fit more columns onto our page. 42. Part #3 - Scaling: A Must for Perfect Printing : Okay, So in this video, we're gonna be focusing on using scaling. Now, what's scaring allows you to do allows you to zoom in and zoom out the print sheets off your printed work. So, for example, follows just two go to page layout. Now, as you can see, we've got skill icon just here. We can adjust it by clicking the arrows like so. So if we scale up, for example, by decreasing the percentage on if he was to go to Father and print, as you can see, that skill is now It's no shrinking, but just to give made that big clearly shrink a bit more. So we'll go all the way down to 35%. So if we go to print now, as you can see shrunk much, much some Warner So we can also do the opposite as well. We could actually increase the size we make here. 140% go to fall imprint. Now, as you can see now, the size is much larger. So if we go back again, we can also just to put this back on 100%. We can also scale to fit to one page in terms of height. So if we just leave us that for now, print as you can see or remain ah, high off for one page when printing on. We can also apply that with Whip as well. So now file imprint again. As you can see, the changes have been made. Andi, you can. You can play around with this much further, in fact, so you can scale it two pages in terms of high free pages as well as with with as well. So again, there's quite a lot of flexibility with this. As you do this, you just want to bear in mind that scale actually decrease, which will cause your work to shrink fervor. 43. Adding Authority - Headers & Footers : Okay, So in this video, we're gonna be focusing on using headers and footers in orderto actually use headers and forward. You would ensure that you you're under the page layout happen. So we you know, did you click on this arrow? Just here? Luck. So and then you're gonna go to head him for on here. You have the option off. Actually, selecting I'm ahead are from a range of options here. So, for example, if we was to select page one for header on again as of our four, let's say we use to select the option sheet one here. I went to print preview. These would appear for. As you can see, we've got page one as all header of what she wanted for for within our print preview. So if we go back now, Okay, I'm we go to that again. So let's just say we didn't want those options. We wanted to actually just put in whatever we felt like, too. So what we could do is who didn't select none for the head are on non for four on What you want to do is you wanna go to cost of a head off on from hair. We can put whatever as we please. Let's just say for the head off the left section, for example, which will be the top left weak import, just our names, some script, my name handful of you and then let's say, will save sheet for the center on. You also got the option off important over things from this category, such as former Turks we've got. We can insert page number so far as to select that that will bring up the page number from preview. We've also got inserting a day as well as the time we can insert file path as well, but insert far names she names as well as and pictures as well and even form a picture. So it's quiet. We've got quite a few options that we still looked OK for that. Andi again, we can do a costume for for as well. So just to throw some things in there were insert page number form left for the sun are will do one set a date on bomb right bull inserts well in certain time hair and now will select OK, and if we go to a print preview hair As you can see, we got my name. God, would she have got page number? Can go pay John Barr and for the bomb left four again. Good day. I'm going time. So again, everything's been updated as we wanted. So if we go back, we've also got the option off, actually Accessing, headed for by going. If we go to view on if we go to page layout just here on what we can do is, as you can see, that already in place. So this is a view where we actually get a print preview and it's much more easy attached at it because all you have to do is just hover over these areas and just double click on. We can just delete these values on. We can actually import whatever as we please as follows against If we just says she one, for example, or if you want in those similar options, as we had before such a page number time. If we go to design tab just here, we can actually throw those in there. So if I say current debt, for example, and then if I just to click somewhere else, such as just had. As you can see, we've got date showing Onda again. This can be applied for the fullest. So if we just scroll down Andi again, we go on our design type. We can also add a sheet named, for example, here on. We could also add far name on even a far half if we wanted Teoh as well as these other options here. So again, this is just a second method off adjusting their header on for throughout our pages within Excel. 44. Case Study - Now How Do I Print My Work? : Okay, so we've got our spread. She As you can see, I've got all our columns and all rows on. We're finally ready to print. Okay? So simply pull. All we need to do is go over to fall on to select print on. As you can see, we've got many different printer settings and options available to us. So here we can select the number of copies that we want to work with. Like so by just selecting these arrow keys or just important a number to our liking. Like so. Okay, 46 maybe a bit too much, but as you can see Yep. Pretty simple. Onda. We can also slept the printer that we want to work with, Just clicking a drop down here on we also print of properties available to us. Now, this is going to be different for each print or so and will only apply to the type of printer you're working with so again, or will be different from printing to printer printed properties. Okay, Now, on the settings. Currently, as you can see, you've got print active sheets, so this is gonna print all the pages for the work you're currently working within. So with our case, we've got 24 pages. So this is gonna front 24 pages for this work shoes. We've also got the option of printing and High Workbook, which will print a worksheets within the workbook on we've got print selection. So if we select print selection, as you can see, only these two cells are selected. So if we go back again, just this is all we've currently got to work with. But again, we can change that. We could actually select more on if we go to fall Unfriend again Now will print much more. Right, So on, if we put it back on print active sheets. Okay, We can also select what pages we want a print from soap from again from page one Teoh Page seven, for example. Like so Onda, we also got the option of printing related or uncle noted so collated. Just your usual would do each. It will do all pages in order. Usual order from one to free on anklet. It will print out all the first pages first in all the second pages and then all the third and so on, depending on the amount of corporate you actually print. Okay, so we also got the option of going landscape or portrait. Two of our orientation is also will put back on the landscape for now are we can also select the paper size we wanna work with like so. So as you can see, we've got all our different options here available, okay? And we can adjust margins as well so we could go wide, for example Narrow on dyear again. We've got some options there. Ondas Well, is that we can also adjust scaling as well so we can fit sheet onto one page. So if we select that, as you can see, this has now been shrunk and we can fit all columns. So this is just all columns on one page on. Now we have all our columns on one page. Andi again, we can also the fit will rose and one page. So yeah, you again. You wouldn't want to go send options because, as you can see, you would not be able to read that properly. So yeah, just again. Key things to bear in mind test. So now we're gonna go back to know scarcely on. We've also got the option of going to page South as well. So we select this, we can just saying, such as our orientation hair as well on the page on margin. So we can also adjust our margins here. Like so. And our head and footers were Onda. We armed the head on form. We can also actually edit their head and four star as well on the dis option on with sheet , we can see our print titles and upfront areas and other options available to us as well. So I'm just gonna click. OK, that and that's how you print on use print settings. 45. Action Step - Print Those Worksheets : another great job. Now that you complete this section, you should have a much better understanding off printing within Excel on different printing tours. So once again, well done. But don't just stop there. Experiment. Put what you've learned to the test and give this a shot. Once you've done not, I will see you in the next section. 46. Overview: master and functions and formulas. Okay, so in this section, we're going to be focusing on how to use some function as well as having a look at basic formulas and other functions such as the lookups Andi. If statements. So by the end of this section, you should have a good understanding off how functions as well as formulas work. So with that said, I'll see you in the next video. 47. Basic Formulas #1 - Addition & Subtraction Formulas : Okay, So in this video, we're going to be focusing on using addition and subtraction formula. So, as you can see, we've got free number columns just here, and we've got direct answer and so reference I'm gonna show you it's gonna be done with this of the direct answer with all formulas. In fact, they always start with an equal sign. So if we actually do equals 40 plus 20 plus two and then we hear enter as you consume. Go on, Answer 62 on. Now, if we go to equal So reference now, what is meant by so references were actually going to use the cells, these cells to get our on site. So again, we're going to start off with equals on by. We're gonna do B six ongoing Teoh aren't f six at G six and now the hit and talk. And as you can see, we've got the same answer. Now, the beauty of using so references is that if we was to change number pair, for example and we was to put 15 And here, entire, as you can see, arts has now been changed. And the cell I sick. So now if we go if we focus on doing a subtraction formula hair. So if we do equals 40 minus 20 minus two on we had enter as you conceive God wants of 18 hair on. Now, if we was does this so referent Suffredin equally e seven minus F seven minus g seven. And we had and home. As you can see, we have the same answer on. And that is how you so referencing Andi how you get direct answers for basic addition and subtraction formulas. 48. Basic Formulas #2 - Multiplication & Division Formulas : Okay, So in this video, we're gonna be focusing on using multiplication on division formulas if we go on a direct answer. And now, if we put equals on what we're gonna do is we're gonna put Ford here, and we're gonna used ass trick symbol. Now, the gastric symbol is what we use for multiplication. So we do that and then we say 20 I don't. We say times by two, and we have entire, as you can see, what the value won't 1006 100. So far. So reference answer. Now, if we did the equal sign on, we can now do instead of actually important. So reference Weaken, Select it by clicking the value we want. So as you can see, we've got e eight here on now when the import ass tricks. And now we're going to do number two pass tricks, Aunt, Now number free on. We're gonna hit Antar. And as you can see, we've got our answer. So if we used to slip that said again, as you can also see it disappears and formula bar like so So we can actually modify that. So and if we select formula bar, you can now see the cells start in use for this. So if we actually wanted to expand our formula slightly, what we can do is we can add Peru emphases around the formula that we've been working with . So, like, so Onda we can add an additional gastric sign outside of that on, we can say less times it's answered by two on it has doubled it. So that's just adding a little just a little bit more complication to our formula there. So Okay, we're gonna now do a direct answer for division. So again, we're going to be using that equal sign I'm gonna do for E. On this time we're going to use forward slash on this is what's used for division so wouldn't divide up by 20. And then we're going to divide that by two and will give us our answer of one. OK, so that's our direct answer here. And as for so reference, if we do equals on course, Andi, we're just gonna select these So equals Ford slash than equals that then forward slash on dso lost one on now I couldn't hear Antar, And as you can see it, we've got on so one 49. The SUM Function - The Core of Excel : in this video, we're going to be focusing on using the some function. So as you can see, we've got our table which highlights the amount of apples that have been sold of months, February and March on. We've got this done in weeks. Hair we're gonna do is we're gonna find out the total amount apple served February. So what you want to do is if you do equal some over bracket, I'm from here. What we can do is we can actually slept sell a home for week one on Weaken. Separate that with corner. So we keep on doing that. And if we press end, talk well, give us our total. But that's quite a long method of doing things. So if we delete this value, uh, on me, put equals some again open bracket. A much more efficient method is to click and drag our values on bond. As you can see, it's uses a colon. So with ranges Coghlan's are used on. If we hear enter, gives us all answer. Andi began. Just show you one more method of dinner. So if we delete that, we could just do equal some open bracket on, we just literally type in cell value. So f five coal on on an F eight but in close bracket and saw on as you can see again, that gives us all answer. So if we delete that another function we can actually use is the order some function which is found over health in our hometown. So if we select their, as you can see is or Matley sums values for us and we can here enter Onda, we have our answer. So if we go over to month march on, if we have to use also something function here, it only does to some some form Week four, as is a break. So in order to actually counteract that what we have to do is if we select her we had a comma Onda. We select our range here and now we enter. It gives us our value for for the Toto hair. So, as you can see, we now have arts are 50. Save At Least 33% of Your Time with Copying Formulas : Okay. So as you can see, we've got costume a list on it showing a list of products that were purchased along their price on the quantity feature them. So what, we're gonna do your hair? We're gonna actually work out the total price for one of these on. We're gonna use that former So it's applied to the rest of the cells we're gonna do is we're gonna use equal son and, um wouldn't say open bracket. We're gonna use his cell f five. I'm gonna say times by G five I don't close bracket. And when a hit and talk on, as you can see, we've got a quantity bought. It's been quite a long job having to do this for each of these. What we can actually do is if we go to the bottom right hand corner off this cell until it becomes a cross on. If we click and drag downwards on release, as you cannot see, this formula has been applied. Teoh. Each and every one of these cells on this is done by relative cell referencing Andi. It has now worked out the total price for each of these so fast to select one. Just show you as we've got her five times. What? G five. As you can see, we now have F six times by G six. Andi. As I keep on clicking on these, you can see the relationship there. 51. Important - Absolute Cell Referencing : Okay, so this time around, we want to find a total price for the quantity or five each of these products. So we always want our quantity to be five. So let's see what happens if we was Teoh to use a relative cell reference this time around . So if we do equal some open bracket if we do f five times by J two close bracket. Okay, We've gone so great for the first. So So if we drank these down Oh, okay. As you can see, we now have zeros. Let's actually look at this in the form of a bar. So as you can see that clearly correct, what? With this answer, we now have f six times by J free. So f six. Okay, j free. That's not what we want. So as you can see relative cell references, they shift downward. So if we look goto next one. Okay, f seven. Great. But now we have j four. Andi, that's not what we want. So in order to counter at this, if we first do controls it just to do this again, right? So, in order to counter out this what you want to use is an absolute so reference so of an absolute cell reference, it will always remain on the same cell J t. So now if we do equal some open bracket And if we do f five times by door law J dollar to closed bracket on, we hear Enter. OK, we've got on sight now just to go over this on informal boss. So what this dollar does it anchors to the column J and if then anchors to the road to so it always remain on cell J t. So now if we was to click and drag it across hair as you can now see, we have on XYZ 52. Skyrocket Your Results - Using Formulas Across Worksheets : Okay, so now we're gonna slightly take things up a notch. So this time around, we're going to use a full knot that involves using another worksheet. So, as you can see, we now have a quantity worksheet just here on. If we select this, we're gonna use this quantity or value 10. Andi, we're going to use it to find our total price for this so on, we're gonna apply it to the rest of D cells. So we're gonna do is we're gonna just select this on if we do Equal song open right here. Now, if we do f five Tom's blonde on what we wanted to use him. I'm going to use an absolute so reference on what we also include now is the name off the worksheet we're gonna work with. So if we put in quantity on you wouldn't end this of exclamation mark whenever you're putting in the name of work shoes on now where you want to do is we're gonna do a dollar de dolar to, as this is an absolute so reference And if we hit enter. As you can see, we now have new total price, whereas just against the check up again. Quantity was by 10. So this has now been times by 10. I've got the answer has So now if we scroll down like so, as you can see we now have on new values. 53. Bonus Step #1: VLOOKUPS : Okay, So in this video, we're going to be focusing on Villa cops. Now, the lookups use indexing to find look up values and to match them. So if we was to use computer speakers here, a computer, speakers, hair, for example, it would find for what we'd be looking for here is the price would find that hair on it would match it on. It would literally transfer that value over hair. So we're going to be going its depth of that. So in order to use them first, we're gonna select ourselves on on the formulas tab. If we go to look up in reference and then we select V. Look up here, it gives us off the look up functions arguments. First of all, we want a match we want to select to look about you too much upon. Sir, we're going to select computer speakers. Okay, so that's gonna be so I five, and now we want a table. A rare. So with the table of rays, the important thing is with the table over, you have to ensure that the first column always has the look up value. So in other words, these are the values were going to be matching on. So if we select this range hair, like so with these are the values were going to be matching upon. Because if we select advice from the D column, As you can see, these are names on these are products, so they wouldn't much. Okay, so the next thing we need to ensure is the column index number. Now, what is meant by this is basically of our table around from E 5 to 15. This would be column one on this would be column to on if we also highlight it highlighted this column, for example. So let's just say just to go back here if we just delete them just to show you. So if we highlight all of this range, this would have been calling for it. But as that this is not the case. I'm just going to leave that Andi, we're just gonna highlight these two, Okay? So in other words, we want our body to be returned on this, so that's gonna be calling to on our range. Look up for this week. We're going to put zero on this gives this Makes the range look cop force. In other words, what false means hair is The value will be exact. It will give an exact value so naff we select. Okay, we've now gone exact value for price here. So if we was to get all cross and just scroll down, it gives us again the rest of our values. But if I just controls that this Onda, we show the shorthand method of actually doing this. So if we do equals be Look up, Onda, We do open bracket here now, So we wanna based upon our look up values so we're gonna base it upon. This is gonna be five separate of a coma table array. So we want to be working with e five Colon Teoh F 15 separate with a novel coma. So I'll call him Index again cause it's the second column we want to focus on. That's gonna be number two on several comma. We want to use again an exact match. We don't want an approximate and exact, so we're gonna select zero. You can also import the words forces while, but we'll just do zero now. As you can see, we've now got all price. If I scroll down again, we've now go all of our values 54. Bonus Step #2: IF STATEMENTS : Okay, So in this video, we're going to be working with if statements now, if statements allows to set conditions, have says so. For example, as you can see, we've got the column titled Is Priced More Than 100. So what we're gonna do hair is for any values. What? The price is more than 100. We're gonna have output. Yes. And if not, we're gonna have output? No. So with that we want to do is select a cell. And now, on the formless tab. If you go to logical on, if you select if, as you can see now, we've got options available, sofa. Logical test you want to select so that you were going to be working with so hair. We're gonna select this cell five. And now what we're gonna do is, let's say if F five is more than 100 now, we're going to say yes if it is on. If not, we wanted to output no on hair. We're gonna slipped. Okay on as you conceive God, answer hair. So if we feel the rest of these while scrolling now, as you can see, we've got all our values bought. If you want to do this to short hand where? So if we delete all these values on DFA sake, things would changes to 200 as well. So what you'd have to do is okay. I would be equals if and then we open brackets now for a logical test again, So would put, if is more, Dan. So we select ourselves, which is going to be a five. So if f five is more than 200 looking to sell conditions now on when send your conditions, you want to be in quotation marks. So if F five is more than 100 we wanted to output. Yes, close quotations, comma. And if not, we wanted to output no close brackets. Onda conceivable on answer. So if we scroll down that has now been updated with the rest of values. 55. Action Step - Start Experimenting with Functions & Formulas : excellent work. You should now have a better comprehension off how functions and formulas work so as usual . Well done. But don't stop now. Start experimenting with these functions and formulas, test them out and see what you can come up with on with That said, I will see you in the next section. 56. Overview: maximize your dates with graphs and charts. Okay, so in this section, we're going to be looking at graphs and charts creation as well as mortifying graphs and charts. Different types of graphs and charts variation such a spark lines on different methods of how these graphs and charts can actually be created. So again, grab a note pad, grab a pen, just be ready and I'll see in the next video. 57. Graphs & Charts Part #1 - Creating Graphs & Charts The Quick Way : Okay, so in this video, we're going to be making a chart. So as you can see, we've got our data hair where it shows a list of animals as well as their quantity. So what we want to do is if we highlight this day off, like so on under the insert tab. Now, if we go over to the charts area, we can see that we've got different chance options available to us. So if we was to select here, we can select from a range of different bar charts, like so on again here. We also have the option off creating a line graph as well as a pie chart on several of options available to us. So if we go out to a bar chart on DWI select, this one, as you can see, has been created. So we've got we can change the style of this as well. So what Different charts starts available to us like so. So second's gonna go of this one on. We can change Ecologist hair. So God began. We got quite a few options to play around with her. So on. We can also change the layout if we go here. So again, different chart layouts for us to choose from. So I'm going to select this one. We can also change the chart Star hair, which is quiet and easier Quick with access in So again flashes to change that. Andi, just click back on that graph, OK, on. Let's just say, for example, we wasn't too sure hard to create a graph. So I'm gonna delete this now, Onda, We mean we needed a hand from Excel. So if we was to highlight all of his day again and go back on insert, we have the option of selective recommended charts on Excel will recommend a chart that they feel is most appropriate. So again, we could go of one of these options on If you want to choose from of options, we could click the off Chance tab. I've got a list of different char options available to us 58. Graphs & Charts Part #2 - Creating Graphs & Charts The Quicker Way : I'm going to show you a quick method of Korean graphs and charts. So if we select our day off, as you can see, a small icon has appeared. This is the quick analysis tour. So if I click this, we've got several different options for us to choose from another world actually accessing Ness's two against select all day on. And if we do control here, as you can see, these options have now come up once more. So if he was to select charts, we've got different chart options available to us. For now, we're just gonna work with this bar chart on as you can see, that's now been said, And if we go over it is plus Icon here we can choose different chart elements, so you consume, got the access, Access Tyler on chart style and so on. So if we have to add access title Andi, that's a legend. As you can see, these have now head. We can actually change the chart title. So if I just simply put clicking that in the highlight that I could put the word animals, for example, Andi, I can also edit access title some. Yeah, I could put the word quantity on. Let's just say I didn't want the legend anymore. I could just simply put click on it and press delete on my keyboard. 59. Bonus Step - Using Filters : We've also got the option off using filters for chart. So if we select the chart Philip's I call Hair. As you can see, we've got our values that we can play around with. So if used to de select cats and lizards and we click, apply, as you can see, these are no longer showing within our child. 60. Acing Pie Charts : in this video, we're going to be creating a pie chart. So we select our data is so on. If we go to insert tab on diesel at the pie chart option Onda, we select just a standard pie chart. As you can see, that's been inserted. So now if we was to go to chart elements on date labels, we should not add some day labels to our pie chart. Onda We can Adam differently. So if we was to select outside and here Andi used to apply the different charts star, we'll go this storm and care on. Now, if we go to change chart type, we can select this free the option to give a more freely effect on we also with a change of charter just a little bit as well. So if we pull him animals here, Andi, to make that look even more fanciers. So if we just re select again on the former tab, we can actually add some shape stars. If we just hover over, somebody's got some different options will select this option hair on. We can also apply. Some words are like so So you got some options. So off Select this option on now we've got pie chart 61. Sparklines - How Mini Graphs Will Revolutionise Your Worksheets : Okay, So in this video, we're going to be using spark lines now. Spark lines are many graphs that are found within a So So, as you can see, we've got our table of there are of our animals on the quantity for each month. So if we select this table, look so on on the insert type, if we go over to spark lines area here, we've got different grass we can work with. So if we select line graph, as you can see, it's got the day of rich. So this is the range that's already been selected. We want the location range. So if we just highlight next to our table like so, Andi, if we select okay, we've now got the spark lines imported in column G. So another method of fairness, in fact, is if we highlight our table again on we used a quick analysis tour, I'm regard spark lines on this time. If we select column as you can see, the spark lines have now been dead 62. Action Step - Create Those Graphs & Charts : another success well done on getting through that section. So now you should have a better on the standing off, how graphs and charts work. But please don't stop there. Keep on going forward on experiment, test out these graphs and charts and see what you can come up with. Once you've done that, I'll see you in the next section. 63. Overview: the power off pivot tables. Okay, this section is going to be all about pivot table. So will be creating pivot tables as well as mortifying pivot tables on looking at some of the features that we can do, such as filtering and sorting data. So again, grabbing no pad, grab a pen. Just be ready on or see you in the next video. 64. PPT #1 - Creating Superior Pivot Tables : in this video, we're going to be focusing on using pivot tables now. Pivot tables. The last breakdown analyzed. Complex They are. So, as you can see here, we've got our table of they are on. We've got some repetitive cells on pivot tables. Work very well with repetitive. So So if we highlight and select all of our they are. And now, if you go to insert only selects pivot table on what we want to do hair is for this. We do want to select a new work shoot. So if we select Okay, now we are taken to a pivot table. So from here, we can actually add some feel itself. We still excels, person. Let's let all of these for now, actually, just to show you what this looks like. And as you can see, these are all popular it. So I first de select a location month on account number. Now we're just left with cells. Person field on the mount filled on. As you can see, it highlights to some off amount, which has been raised by each sales person. So this can be a very valuable and useful way off breaking down and analyzing data 65. PPT #2 - Sorting & Using Pivot Tables : As you can see, we can add even more fields to our pivot table. So if we was to select the filled month now, what's happened here is a lot of money that's been raised for each month has been combined together. So orders duplicate values that we had before for the Mount Field. They've all been combined. So again, this is a very excellent whale off analyzing and just breaking down things even further on Dickie fingers with pivot tables, they present data either rose or in columns with this. If we was to move the month food into columns, as you can see, the layer off our pivot table is now presented differently. So with that said, we can now move back month food into Rose on. Now we're gonna move cells, persons into columns on again. The whole layer off our pivot table has been structured differently. With that said, we could also sort our pivot table if we go on their hair where it says road labels on. We select the drop down arrow. Andi, we've got the option of either sort in 80 said, or is there to eso? If we saw? Is that a which is to send him order? As you can see it. This has all been structured differently. 66. Action Step - Put Those Pivot Tables Into Practice : Congratulations. You now complete the section. You should now have a good understanding of how pivot tables work and function. So as usual. Well done. But don't just stop there. Put what you've learned to the test. Once you've done now, I will see you in the next section. 67. Overview: the art off conditional formatting. And this section we're going to be working with values. An example is will be changing the cells appearance based on its value as well as that will be using various rules such as top and bottom rules. So please be ready. Make sure you've got no pad a Penta hand on. I'll see you in the next video. 68. Phase #1 - Changing Cell Appearances Based on Their Value : in this video, we're going to visually change their parents of a self souls based on its values. So, as you can see, we've got data hair on. It's based upon a fruit list. So the first thing we actually want to do is we wanna select the day that we wanna work with. So if we select day 11 column G like, so on, if we go to conditional formatting, which is found on the home tab and we go to highlight cells rules, we're going to be working with a greater down rule. So if we select this as you can see by default, the value £5 has been putting on. From what we see, hair is any value that's more than £5. The value has a red feel on has dark red text, Paul. Just to show you, we can actually changes value Sophie's to put in free pounds. We can also change how the cells is affected. So we've got a few options here to play with. So if we was just to select the option red border, as you can see, any values that are high them free pounds have now got a red border. So if we select OK to confirm that those values have now been changed 69. Phase #2 - The Ease of Finding Duplicate Values : hair will be going over finding deeply value. So first of all, if we select the cells that we wanna work with in a column So we're going to select the souls within the equal, Um, like so. And if you go to conditional forming up here on under highlights cells rules, if we go hair to duplicate values, as you can see, if we select this drop down arrow, we've got the option off selecting unique values or do pick up values. Andi hair. We've got the option off changing how these values will appear. So if we select green filled with doctoring text, as you can see or duplicate values are now having a green fill with a darker in Texas, we select OK to confirm that these values have now been updated. 70. Rule #1 - Top vs Bottom Rules : hair. We're going to be using top and bottom roars. So if we highlight the sellers that were going to be working with on the idea column and we go to conditional formatting on top and bottom roars on, we select top 10 items. As you can see, the number 10 is in place by default, but we can adjust this. So if we was to select the number five here, it's currently showing a top five values within this column. Andi it showing up with a light red Phil and with dark red text. So if we select located up to confirm that, that's now been updated so we can undo these black. So on, if we go back to conditional formatting again on the top and bottom roars on now, if we slept top 10% we can show the top 10% values. But we don't have to show tempers, and we can adjust this like so. So if we say 25% on, we change how that's being displayed, we can say with a green full of dark green text hair, as you can see once more. These have been updated so we can select okay with that Onda, we can also add a novel raw. So if we just select these values again on go to conditional formatting on this time, if we select bottom 10% we can show the bottom 10% on. We can choose how that's displayed so we can say yellow full with dark yellow text on. As you can see, that's been updated on if we select. Okay, now we've confirmed that on our rules have now been updated. 71. Rule #2- How Being Average Rules : in this video, we're going to be focusing on using average rules. So, firstly, if we select souls that were going to be working with within the G column, like some on if we should let conditional formatting on top, Bond grows hair we can select above average on. As you can see, all values are above average. Have a light red filled with dark red texts. So we can also change these as well on. We could just say read text, for example on we could select OK to confirm these on. As you can see, these values have now been updated, but we could also apply second average rule. So if we highlight these cells again and if you go to conditional for my own top bomb rules on this time, if we select below average here, we can apply a rule where it will highlight all values that are below average. So if we select this time Greenfield with dark green text on, we select. Okay, As you can see now, we have to average rules that have been updated 72. Rule #3 - Removing Conditional Formatting Rules : in this video, we're going to be focusing on removing conditional formatting rules. So, firstly, if we select the souls that were going to be working with and now if you go to conditional for Maine here we have the clear rules option. So if we go to West has clear rules from selected cells. As you can see, all conditional forming has been removed from these cells. So now if we have to do and undo on if we de select ourselves on, we go to conditional for Maine and now forgot to clear rules again on we set clear rules from entire ship. As you can see a lot, conditional formatting rules have been removed from the entire shoot without having to highlight cells. 73. Bonus Step - Color Scales, Data Bars & Icon Sets : There are also other methods of conditional formatting that we can use for they are. So if we select ourselves and if we go to conditional formatting on we select day or bars, we can actually visually include day of balls for our cell values on as you can see, the hide cell values along the day a bottle beer. So if we undo those changes on, we go back to conditional forming, we can also use Carla skills as well. So we've got various Collis girls to choose from him on. The idea of college skills is again, the higher the value darker, the color will be Onda. We undo those changes on now if we select I concerts, we've also got different our concepts to play around with. So again, these are never visual indicator off how high or low of value Maybe 74. Action Step - Start Conditional Formatting Now : nice job. You should now be all set with conditional formatting. So again, great job with that said. Now, put what you've learned to the test experiment on. Just test out all the knowledge and information that you've gathered Andi with that said, I will see you in the next section. 75. Final Action Step - Putting It All Together : congratulations. You are now officially an Excel expert. With that said I would personally like to thank you on embarking on this journey through commitment, hard work and determination. You have shown, boy, it takes to acquire success so well done. But before we end things we need to ensure that you're in good hands. Have you gone over all the sections? Are there areas that you are unsure? But if there are, then go over the sections again, again. I like to thank you for taking part in this course and keep on propelling forward. I wish you all the best and I look forward to seeing oil group results.