Introducing...Excel: 1 Hour Tutorial | No Previous Knowledge Needed | Zory Dirwai | Skillshare

Playback Speed

  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x

Introducing...Excel: 1 Hour Tutorial | No Previous Knowledge Needed

teacher avatar Zory Dirwai, Digital Marketer, Photographer & Creator

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Lessons in This Class

11 Lessons (1h 8m)
    • 1. Introduction To Course

    • 2. Excel Course Agenda

    • 3. The Quick Access Toolbar

    • 4. The Excel Ribbon

    • 5. Formatting Your Worksheet

    • 6. Creating Formulas

    • 7. Listing & Sorting in Excel

    • 8. Using Paste Special

    • 9. Pivot Tables (How-To Guide)

    • 10. Saving & Printing Your Work

    • 11. Course Complete & Outro

  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels
  • Beg/Int level
  • Int/Adv level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.





About This Class

Hello! Welcome to the first course of my new series.

Have you ever been intimidated by FORMULAS, confused by PIVOT TABLES and died inside at the mention of SORTING DATA? If you've answered "Yes" to any of these, this course was made for you.

Until not long ago, I was similarly bewildered by Excel and it's "quirks". But through practice and persistence I have not only seen it improve my daily productivity, but also become one of my my value-inducing subjects to teach.

Today, we are homing in on Microsoft Excel, a popular but often mis-used and intimidating application. In our beginner's tutorial we learn:

1) What Excel is used for and the specific menus (Excel Ribbon & Quick Access Toolbar)
2) Formatting and Formulas (SUM, AVERAGE etc...)
3) Pivot Tables (watch our 'How-To' section)
4) Listing & Sorting Your Data
5) Paste Special (& cutting your work by half!) and more...

So what are you waiting for? Kick your Excel Gremlins to the curb and impress your colleagues!

Meet Your Teacher

Teacher Profile Image

Zory Dirwai

Digital Marketer, Photographer & Creator


Class Ratings

Expectations Met?
  • Exceeded!
  • Yes
  • Somewhat
  • Not really
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.


1. Introduction To Course: Hi, my name is Audrey and I'm a digital Martin technician. And today we are going to cover a wildly popular, very misunderstood application, Microsoft Excel. Now, many people find this too intimidating, especially using some of the more advanced techniques. But in this lesson and in this class, what we're going to do is we're going to break it down in 30 minutes or maybe slightly more unhealthy to get a foundational understanding when exactly how to use this wildly popular and useful tool itself. So it's time to shake off your Excel anxieties as we jump straight in to this course head-on. And I look forward to seeing you in the first lesson. 2. Excel Course Agenda: As I mentioned, my name is Yuri and today we are going to cover an introduction to Excel. Now you might ask yourself, what is Excel? Excel is often referred to as Microsoft Word's ugly little brother. I want to show you today how useful it is. And I want to start off by first defining what Excel is. So according to Microsoft, Excel is a Microsoft Office program used to organize numbers, data, formulas, and functions within spreadsheets. It's a worksheet that allows you to manipulate data and display it in a way that is helpful to you. Now you might ask yourself, who actually uses Excel now, as I've stated before, it's not just for data analysts and those within finance, but you could also use it within a marketing function to display data that pertains to maybe social media trends that you are experiencing engagement likes some people even use it as a budgeting tool because of its formulas and functions. Now what we're gonna do is we're going to go over the agenda just so you know exactly what we're going to do. So we're going to start off by looking at the basic layout of a workbook, defining what a workbook is versus what a work she is. And also looking at the Excel ribbon we wrote, we're then going to go onto the basics of formatting data, which will then cover the formulas, different formulas that you can use in Excel, like adding light, the sum function, and also dividing and averages. And we're going to give those and it'll go. And then we're going to look at how to list unsought data as well. So in ascending and descending order as well as alphabetically or numerically. We then going to look at the basics of the dreaded pivot table, which I hope to simplify for you. And then finally, we're going to look at how to save and print your dates are effectively within Excel. So hopefully that should give you a good basis and foundation to then begin to build upon your knowledge of how to use Excel. I'll see you in the next lesson. 3. The Quick Access Toolbar: So welcome back. We're going to dive straight in to exactly what Excel will look like to you. It may look slightly different depending on the version of Excel that you have and whether you're using Windows or maybe an Apple product. So when I talk about workbook, what I'm actually referring to is the Excel file as a whole. So almost picture the Excel workbook as a bucket. And within that bucket, you've got the Excel worksheets. The worksheets are basically another word for the tabs that you see at the bottom of the screen just over here. So as you can see, I've got a number of tabs ranging from 1.1 to 1.6. These represent the Excel worksheets. Now the workbook is the whole thing. So it encompasses all of these tabs and everything that you do within that particular Excel file or spreadsheet that you're working in. So there are a few other kind of components that we need to be aware of when working with Excel. There is also a Quick Access Toolbar, which is the just this top level navigation just over here. It's usually highlighted in green. Although if you're working on a, an Apple product or anything other than Windows, or maybe an older version of Excel, things might look a bit different. However, generally speaking, this is the view that you should see. So just kind of working through this quick access toolbar here, you've got the obligatory close and minimize buttons just on the top right-hand corner. And then you've got an interesting button here called Ribbon Display Options. Say if you were to click here, you'd be able to hide what is called the Excel ribbon, which is just underneath. And then bring them back up again and show the tabs and commands. So there are a range of options just here for you, Just based around what you see on your screen. Obviously, I've got my login details just here. But I've also got a search bar. And again, you can search particular options. And actions that you want to perform on Excel are all available through this search bar or even recent files you've saved. Speaking of files that you would have saved. You also have the option of renaming your workbook. So you can rename it just here, Moscow to Excel for beginners tutorial sheet. So we're going to stay with that. And then just to the left of that, you've got the option to customize your Quick Access Toolbar, which again is the green top-level nav that we're working through now. So there's a number of things that you can add to it. The on already there. And again, things like creating a new worksheet and workbook. You can also toggle an open workbook option as well if it's not already there. And also something that you might want to consider, spellcheck, and just various options. There are more commands as well, but you can not too, if you so desire. Finally, you've got the undo and redo buttons, which are really useful, especially if it's your first few times really using Excel extensively. In the past, I found myself using the undo redo button quite a lot and I still do to this day. Then you have the save function. So there is an autosave option just here, but if that's toggled off, then just remember to use the Save button occasion and he just to make sure that none of your data resin lost within your workbook or worksheets. So that's the quick access toolbar. 4. The Excel Ribbon: But just below you see a gray ribbon that stretches across just underneath this top-level NADPH, and that is what we call the Excel ribbon. Now, there are a number of options on here and also tabs, but we're just going to go through this Home tab initially. So just from left to right, you have this paste copy and format option, which if you've used any of the MS Office tools or applications like PowerPoint or Word, you'll be very familiar with these. And it should almost feel like home. No pun intended. You've also got the option to change the font and also bold and italics and underline things based on importance. And then you've got the, which is really important, the fill color and font color options. Now the reason why I say this is important is because you can differentiate between different types of data within your data and worksheets. So that's going to become increasingly important as the lesson progresses. But do remember, you can do that through this menu. You've also got here the option to align it in a certain way, just here. Which again is very useful when you're trying to present your data in a certain way. Wrap Text and merge center. We'll get back to those in a little bit. And then you've also got these Number Format options which are really good. So just a quick tip. You can see basically what the gist of these options are just below. So as you can see, clipboard font, alignment number, you've got styles, his cell styles. You've even got a cell option where it allows you to delete rows and columns. And then you've got an editing function, just OVA, which includes AutoSum, which again is very useful. A clear cell button and also a sort and filter button as well. Finally, you have this analyzed data button. Just a few more things just to outline, just in this Home tab. You've also got a share option here as well, which again allow you to share your workbooks and worksheets with other members of staff, for other colleagues or possibly students. And then you've got an option to comment on a particular workbook or worksheet as well. Moving on. You haven't Insert option two. And again, things like pivot tables, just regular Excel tables that you would want to create within your worksheets. You've got illustrations just here again, if you wanted to add pictures and shapes and icons to what you were doing, you've got add-ins as well, which being honest, I don't use too often. But we've also got Recommended Charts and just charts in general, just, hey, you can create quick tables and graphs and charts based on the data that you have in your worksheet. Okay, moving on to tours, sparklines and filters again, these are kind of more specific visual aids for people. Timeline and in particular will prove very useful to you, especially when you want to figure out a certain date, range or present data within a certain date. Links. Again, very useful if you want to link outside of the workbook and worksheet. Again, you've got a comment, option, text and equation and symbol. So that was the Insert tab. Just a few more tabs to go through. Before we move on to our next lesson. You have a page layout section here, which again is more about how your page is going to look once printed or how it looks currently. So again, logins, which is all about how much data on how much information you want to fit within a certain sheet. Orientation again, is very important to, again, you can, you can either display via portrait or landscape. And that will determine how much you can put on a certain X0 and will definitely make the difference when it comes to printing, that there is an option to change the size based on the aspect ratio or use case for your worksheet. Moving onto formulas very quickly, you've got the option to play with a range of formulas. And the great thing about this tool bar is sometimes when you are typing in formulas, they can take a little while to do. But you've got an AutoSum option here, which you've got recently used options just here. And it breaks X0, breaks it down, breaks down every single, pretty much every formula from financial to logical today and time. Two maps and trigonometry loads of options there for you. And again, in these other tabs too, you've got data is more to do with how your data looks and how you want to sort and filter your data. You got a review option again, once you've completed a certain worksheet, you may want to spell, check, or translate. So there are various options there. And then just view what you see in terms of your worksheet. All to do with kind of, you know, how zoomed in you are into a worksheet where the page break is. Other than help. That's pretty much it. Again, you've got a file tab here when it comes to saving or exporting and publishing your data, you can do all of that through this tab here. So that pretty much covers the layout of Excel. 5. Formatting Your Worksheet: Welcome to our second lesson. Our second lesson is around formatting data. So what we're gonna do in this lesson is methodically learn how to present our Excel data in a way that other people can understand it. So what we're gonna do is we're going to go into Excel. So at the moment, our sheet looks like this. So just on the top here. I've just made a note here just to say is essential to make your types of formatting a different color. So as you can see here, what I do is hard-coded numbers, IEEE numbers that I've typed in myself or added to a table that aren't a part of a formula are in blue. So as you can see here, than one hundred and two hundred is in blue, those that are in formulas will stay in black. So 300 here is obviously a 100 plus 200. Say if I was to click into a, you'd see that before, which is B and four, just here on the axis. And C four are added together to make 300. You'd know is that this particular bit of data is in black. The reason why is it's obviously a part of a formula, you know, tag or make your, your data or a different color. If that suits, you. Just pick blue because it's an easy column, first of all, referencing a different sheet which is intro. So as you can see, Excel tutorial for 2021 obviously references this title just here, but it's in a different worksheet. So what we've done here is we've just highlighted it in green and bolded it to make it very clear, right? The next part we want to cover is if you wanted to add a comma or take away a coma from a particular number. So there is a Comma Style button. If, for example, you know your number or a number within the cell didn't have commas. You can add comments to it quite easily through here. The other thing you could do as well is round a number up to two decimal places. So I can do that through justice Increase Decimal button. So there we go. We've rounded it up to, and again, I could decrease the decimal places as well by two as well. Just a quick note on percentages. So Excel makes it really easy for you to be able to convert a number to a percentage. I've got the example of hair as of 0.6. And what I'll do here is just literally just go through this a number sub section within the Home tab and then just click on percent style. And it easily converts to 60%. Align text to the right and left. Again, very easy to do on Excel. So you just need to click into this, align left and align right, depending on what you wanted to do. Just put cha-cha Yao for the middle. Yeah, I'm a bit corny. Apologies for the for the corny jokes. The final step here I've mentioned is center across section. Instead of merging cells, now, you may be familiar with merged cell function. And the reason why you might want to merge a cell is possibly that your text is overlapping into the cell anyway. And you just want to make that clear. But what tends to happen through you merging your cells and centering is you can completely delete and destroyed the cells that you are merging over. A better way of doing this is to center across selection. So you can do this through the Format button, which is in your tabs. If that's not available. Why do is I right-click and then go into Format Cells. Many gives me the option within alignment to center across selection. So once I select that and click OK, it will literally center across the selection, but it will still, um, keep the cells and not delete them. So that's just a short trick that I've learned. Just a second section here, really quick, just around selecting data. So depending on whether you're using a Mac or you're using a Windows device, you can use command or control. Just hold that down and drag across your selection to select a particular to select a particular portion of your worksheet. So I've selected these cells, the arrays by also want to select these cells, the arrays as well next to it. So what I'm gonna do is I'm going to select it using my mouse cursor. And then I'm going to press down on Control and then select the rest of it as well. So I've selected two different portions of the worksheet and the cells. And again, I can apply various changes to them at the same time. There's a really cool feature within Excel called the autofill feature. Now this allows you to auto-fill data on using just Excel predicting what you're going to add to the corresponding cells. So I'm going to use the example of 1234. What comes after that five, right? But what Excel's gonna do is it's gonna guess what comes after it. And it gives you 5, 6, and 7. Just a quick demonstration on how I did that. I dragged into 1, 2, 3, and 4, selected those using my left button on my mouse. And then if you see these, this small cross at the bottom, I literally drag that down to give me 56 and seven. So it's as simple as that. You don't have to worry about typing in all the rest of it. It works with all different types of numbers. So maybe out of 45675 in there as well. And there you go. It's predicted what comes next after that. So it also works with dates. So you've got January, February, March, April, what comes after that may. Well, Excel is able to predict or the dates as well. So I'll just drag this down. And again, this can go on forever. So that's all I have to say about formatting. I hope you've managed to get a lot from this lesson. The next lesson that will be speaking about and working through is formulas. So I look forward to seeing you on that one. 6. Creating Formulas: Welcome to our third lesson, which is covering how to use formulas. Now I know this is probably the part of Excel that most people absolutely free cow over because it just brings back memories of algebra. But if you look at it and if you work through and learn it methodically, a really isn't anything to be scared of is actually very useful for you to learn as a skill. So we're going to dive straight into it as you always do, and just have a look at what we have in our worksheet here. But I want you to focus just on the top here where we see why Safeway wage husband wage investments, child benefit and total income. As you can see from our formatting lesson previous, we spoke about how we format certain types of data. So you can see here that all of these are highlighted are blue, which means that I've physically put these in. This is not a part of a formula per se, but these are just been typed in by myself. You can see just at the end here, total income, which is 4,540. Now, um, it's clear to see that this is basically the sum of all four of these figures here. And how we came to that point was it was generated by a formula. Now, formulas are displayed in two places, usually within Excel, that is in the cell itself, and also in this formula area just over here. You can add the formulas in it. In either section, it doesn't really matter. But while I want you to focus on his hair and just how the formula has been setup. So usually, when you type in a formula, you begin the formula by telling Excel is a formula. And how you do it is you use an equal sign. So we're just going to use an equal sign just here as an example. So what we're gonna do is we're going to add the husband and wife wage up together just as an example. So we're just going to type equals, and then we're just going to put some brackets here. And then just type in H three, name of the cell for the wife wage. And as he can see, as I've typed in, is highlighted the wife wage cell in blue, which is exactly what we want. And then we're just going to at it using addition on our keyboard. And we're going to add it to i3, which excels automatically highlighted in red. So you can already see this is working. I'm excited. What we're gonna do is we go into close brackets here, and then we're just going to press Enter or tab. And then we have the sum of those two cells within this, this cell. So that's brilliant used because that tells me that my formula, which I've created works. And that's brilliant because what can then happen is if any of these change because this wife wage is connected to the sum in AI for as well as L3. If I change this to say 2000, so wife gets a promotion. Then as you can see, both of those cells have been updated because those formulas are connected to each of those other cells. So that's the great thing about Excel. If you change something in one area, usually what happens is it changes in another. So that's good if it's connected. So how do I make this easier? Well, the great thing about it is is that you don't have to always type in hate 3 plus I3 plus J4 plus q 7. No, you don't need to always do that. What you can do is actually use a sum function. So you have formulas and then you have functions, functions of things like sum, average, and basically they're shortcuts to allow you to do what you need to do within, within Excel by fulfilling a certain function, hence the name. So what we're gonna do is we're going to type in sum here. And then instead of typing in plus h3, we're just going to do a coma up. And we end up with the same result. So that tells me that I don't need to use add all the time. I could use some in front of my brackets to achieve the same effect. So that's no doubt gonna save us some time. Moving on. The great thing about Excel as well is you can add a certain number of cells together. So for example, if I had you look at expenses, just hit if I had Number of expenses in descending order. And I wanted to add them all up. I could do one of two things I could Eva. Just focus your attention just at the bottom here. I can even look at the bottom just here. So I've got order one to 55 highlighted. And it's come up with some just at the bottom over here. So Excel is already beginning to work its way through. But if I wanted to add the sum together, then what I would do is I would create this formula, some K8, the main K 12. So that's come up with the sum of those five orders. So that's a really, really easy and quick way to be able to just add up a group of figures in ascending or descending order. What if I wanted an average? So averages are interesting because maybe I want to figure out the average amount that I spend on each order just to give myself a figure. So within my expenses list here, type average first and then brackets. And then what I've done, all I've done is I've just dragged from K to K 12 and then close brackets and it's giving me 71. So that's the average of all five of those orders. If I wanted to divide by five as well. Well, what could I do? It's a very easy formula to create actually. So what I would do is I would do equals. So I've got the sum here already. So I'm gonna do is equals k 13. And then I'm just going to put a forward slash and then a five just to get that figure. So that total, IE, the sum of those five orders, I've divided by 5 to get 71. So this is really easy. So we've learned how to add, work out an average and also divide using our formulas that that's going to allow us to do so many things moving forward. Now there are other things that you can do within Excel. For example. You can select certain characters within the cells using the left formula. You can also do a thing called concatenate as well, which means adding two characters from separate cells together. So again, if you wanted, for example, to say Wife and then husband wage, instead of just saying wife wage and then husband wage, you could do that automatically through Excel. Well, I'm going to do is I'm going to attach a sheet with a number of formulas and the functions of those formulas in for you to begin to try and play with, um, and again, attach a task to it as well. So the important thing is just to use these four formulas and get used to using them as well. Because again, practice makes perfect. Finally, I want to just turn your attention to locking a cell reference. Now, this might come into play if for example, you have a, a figure that is attached to a certain formula and you want to move that to another area. Now, as you've seen just over here, the total divided by 5. I've moved it to a L. But the issue is, is it hasn't kept and retained that formula and that sum. Now if I wanted to retain that some well, I would do is within this formula, I would simply use the dollar sign just before K3, and then I would drag it as well. So this becomes helpful if for example, you've got a table full of figures and you wanted to drag and drop a certain figure into another cell. But you just wanted to retain everything that was in the first one. In the second one. Also, you know, you didn't want to lose the function of a particular cell. This can happen quite a lot if you're working with a lot of figures on a table and you just wanted to make quick change as well. This is a way of locking that particular cell and all of its functions within and allowing you to drag and drop. So this is called an absolute reference and it's very useful moving forward within within using and creating tables. The next lesson is going to be on listing and sorting. And I look forward to meeting you in the next lesson. 7. Listing & Sorting in Excel: Welcome back everybody. This lesson is going to be a really quick one, and it's around listing and sorting on Excel. Now, what is listing and sorting? You ask, well, we've got a table here just to give you an example, everybody's into crypto and stocks now. So I just thought I'd use this as a perfect example to share with you. So in this table that I've created, there are a number of investments that are highlighted on here. And this is a mock investment portfolio. This isn't mine, although probably wish it was looking at the numbers. So we've got a number of stocks in here, x RP, gold, light coin. We've also got Bitcoin on here as well, as well as tests Tesla and a theory. And what we wanna do here is we want to see which stock is valued the most and which stock is valued as the least. Now how we can do that through listing and sorting is we can use sort and filter function, which is just within home. There are other ways of accessing the same function. On Excel. You can go on to data and then use the filter section or sought. But what we're gonna do is we're going to sort. So we're going to click on market value of stock and krypton. What we want to do is we want to list it in ascending to descending order. So you want to see what the most valuable stock that we have is down to the least valuable. So how we do that is we click into the market value of the stock on crypto. And we can go into largest and smallest. Right here. It's just re-positioned certain parts of our table. But what we can see here is Bitcoin is on the top here with forty one thousand, five hundred and sixty six and forty nine pence, which is the market value of Bitcoin. This isn't a true value. It may have increased or decreased, but just at the time of recording, that's what it was. So as you can see here, this is the market value, MMR. Next one down is a theorem. Um, so that's 411 Bitcoin and Ethereum coin. And then you've got the rest just down here. The next down is Tesla stock, et cetera. But the important thing is, is we've now listed our data, or sorry, sorted our data or in a certain way. And you know, there are other things that you can do here as well. So if we were to select this date range, so if we were to select this, this portion of our table, and we were to click sort. We can again do a smallest to largest. And it will give us the least valued stock based on market value, which is x RP. What type of stock is it is crypto. So there you go. So there are a number of ways that you can do it, ascending or descending. You can also filter as well. So you can sort, you can do things by like sort by font color or sought by fill color as well. The great thing about custom saw is you're able to sort with multiple filters as well. So if that is something that you want to do, you can also do that through this custom sort function. Books just here. So how access state was I right-clicked. I went onto sort and then went down to custom sort. And again, you're able to add levels to your SWOT. So we might want to sort by cell value initially. But then you might want to sort by, might want to then sort by eta z. You know, obviously we can make it as specific as we need to be. So that's just a really quick way of sorting your data and listing your data as well. Hopefully that was helpful. And we'll move on to our next lesson, which is on pivot tables. 8. Using Paste Special: Welcome to our sixth lesson, which is going to cover the paste special function. Now, if you're in a situation where you have a specific value, but you want to take that value and put it in an, into another cell, but you don't want the formatting from that first cell. You can do what is called a paste special. And it also works inversely if you just want to take the formatting and not the value, but we're going to dive in and see what that actually means in our examples. So in our worksheet just here, you can see the I have three different instances here, all using the same formula. So H plus I. And obviously depending on the, the row number, that will vary. But you can see here that we have various values within the cell, but the l values which are just over here, um, ah, slightly different. And that is because of paste special function. So as you can see here on the first three plus three equal to six, you've got this value within here. And then we've literally glosses very simple sum formula that basically lists this. Now, you can do a straight of control and copy and then paste into another cell. And obviously it will give you that same value with the formatting. But what if we wanted to say, for example, use this value which is in J5 seats, but we didn't want any of the formatting that came with it. Well, we could then do is use what's called a paste special. So what we're gonna do is we're going to Control C. And you can also do it by right-clicking and then clicking Copy. And then what we're going to do is we're going to right-click into the destination cell and then go into paste special. Now pays special, woo, bring up a number of options that you can choose. Well, I'm going to do is I'm going to within paste, just click into the values and then click, Okay. So now all we've done is we've taken just the value and left the formatting. I is no longer green and then pasted it into our M5. So, so what if we wanted to do it the other way? If we wanted to take the formatting but change the value, we can do that exact same thing too. So we've got number 10 here as an example, but say I have ten in here. And we'll just put two just to further home the point in. So we've got 33 equal to six, and then we've got our formula just here, which is all correct. But what we're gonna do is we're going to take control C and then click into these. I've dragged both tens in here and then I'm going to right-click, go into paste special, and then take formats. Choose formats, which is in this pace list here and then click Okay, so what is now done is it's just taken the, the formatting from that cell and then added it into those two other cells that have tens in them. And that's basically it. That is how you use paste special to just take a certain characteristic of a cell and apply it to another. Rather than taking the whole thing. 9. Pivot Tables (How-To Guide): Hello and welcome back. This lesson is going to cover the dreaded pivot table. We're going to make it as easy as possible for you to understand and begin to implement with examples. So without further ado, we're going to jump straight into it. Now, first of all, you might be asking, what is a pivot table? And it's a fair question to ask. Well, Pivot Table is traditionally an analytical tool within Microsoft Excel that allows you to summarize your data in seconds. So you may want to send a report off, but currently, your table just features a bunch of random numbers that are unordered. They're not listed or sorted in any particular way. And it's very difficult to be able to summarize that data effectively. We'll pivot tables allow you to do that in a very, very quick and clever fashion. So we're going to dive straight into how we use those pivot tables and create one. So the very first thing that you're going to want to do is make sure that your dataset is ready to use. And when I say ready, it just means that they're on any blank columns or rows within self. So for example, I'm just going to create one just for the sake of it. So this column one doesn't have any dates are in it whatsoever. So what we're gonna do is we're going to get rid of that column. And we can do the same for any rows that feature that don't necessarily have anything in them. So now that that dataset is complete, I'm just going to tell you what the dataset is as well. Actually, it is a Facebook Insights for a particular business school over the course of two to three months. So we're going to use this dataset and analyze it. So now that we've established what that dataset is, what we're going to do is we're going to convert it into a pivot table. So firstly, what we're going to do is we're going to highlight this table. If it's not already a table, by the way, you can easily make something that isn't already a table, a table by clicking on table, which is in the Insert tab. So now once that's done, we're going to click anywhere into the table because Excel already recognizes it as a table. So we're not going to need to do anything special there or highlight everything. We just need to highlight one cell. And then from there, what we're going to do is click on pivot table, which is just at the top by underneath the Insert tab. And this option will come up. So it will say pivot table from table or range. Now what we're going to do here is we've already selected a table, a man, it's come up as Table 3. But we're going to focus on these options just at the bottom here. So it says choose where you want your pivot table to be placed. Now, very simple. Do you want to in a new worksheet? I a new tab? Or do you want to in our existing worksheet are existing top we want to write next to our older table. So what we're going to do is we're going to click into existing tab. And it will come up with the option to choose a location. Now, It's important to note that if you try and create a pivot table without choosing a location, you automatically get an error that says destination reference is not valid. So what you're going to need to do is click into this location part and then click into any cell that you want this pivot table to appear in. So now once you've done that, you should be able to click Okay, and then the pivot table will automatically generate. What you may see first is not a complete table. So it is very, very important to note that you won't see a complete table first, what you are most likely to see is something along the lines of this. Um, so it's a prompt to build a report. Choose fields from the pivot table field list. The field pivot table field list will come to the right of the page, which is basically this gray area just over here. And what it will allow you to do, or what it does is pull in the titles from your table. So for example, we've got our Facebook Insights here. We've got daily total impressions were no is how daily total impressions on the first table also features as a pivot table field. So you can click into that and then add it into your pivot table. So it's brilliant how it does this automatically. It's worth noting as well that as with most things, Excel comes with its own separate language. And we've already discussed formatting formulas, rows and columns. But Pivot Tables has its own set of language, languages as well in specific terms. So namely Rho fields, which are things like dates and times, months of the year, or even years themselves. So if I was to click into date, you'd see the row fields that appears just on the left. The thing about role fields is they are usually to the left of what is then called a value field. Now a value field are things like the market value of something. And that would feature to the right of a row field. So let's just click into some, some row fields just to demonstrate. So I've clicked into all of them, but as you can see, you know, the, it doesn't look great. And this isn't how I want my data to show. So we're just going to choose two or three of them for the moment. And what we're gonna do is we're going to look down here and there's option to drag your fields between these areas below. So what we're gonna do is we're going to unselect those apart from day and then drag maybe new likes into value rather than rows. And as you can see, a value field is created count of daily new likes. Now, you can see here the, obviously the data here is wrong. Count of daily new likes. You've got a bunch of ones here. When if you look at our table, that's not how our data, our data is presented. So what is going on here? Well, obviously there is an error here. The error is in, in Excel. It's just what we've asked our pivot table to do. So always be aware of if you are especially dragging and dropping anything into the value field that your data is displayed correctly. So what we're gonna do is we're going to change that from count to some, which should be able to rectify this era. So you're just going to click into here and then go into Value Field Settings and then click sum instead of count. And we've got the sum of daily new likes rather than an account just here. And as you can see, it's now displaying correctly. Brilliant. So we're going to drag a few more in. So I want to look at daily total reach as well as the daily total impressions. So our drag these in here and similar to the daily new lights, I'm going to change count to sum, to get the true value. Daily. They go. So now I've got all of my data within this pivot table, and I've got a grand total, which is just here. So there are a few more things to kind of mentioned within here. If you didn't want to say, see every single day of those couple of months. What you could do is specify a particular date range to see. And you can do that through filters. So what I'll do is I'll drag Date into filters. And now what it's done is it's brought up my date option just to the top over here. So you've got date and then you've got all, and then you've got the sum of the daily new likes of the entire table. Just here. So some of daily new, like some of the daily total reach and just basically given me a grand total of everything and ignored any of the individual portions. But what if I wanted to look at maybe the 21st of September as an example or the 23rd. I'll just click into all. Then click into the 23rd of the ninth. And then once again, it's giving me a the total on that day. So it's not giving me a total of the entire month, but just for that day. As you can see, we didn't get any likes on that day. But we got a total reach of a 120 people and impressions of a 170 from those 120 people. So again, you can play around with different datasets, which I'll provide as kind of worksheets for you to look through bought pivot tables, as you can see, a really, really, really good at summarizing your data, making sense of it all, and just making sure that you've got all of the correct information within a table ready for you to send to a client or a line manager. A few more things to mention as well. Just to look out for if you were to update your pivot table, you just need to be aware of the fact that as well as updating it on your table, you would need to do it on your pivot table also. So the thing about the pivot table is unlike formulas, it doesn't automatically update. So if I was to change a value in here, so say for example, the daily told reach and make it 500 instead. Notice how the daily total reach on that particular day. So just to let you see, so the ninth of September was 422 according to our pivot table. So that was this daily total reach there. But we've actually changed it to 500 on our original table, but our pivot table hasn't reflected that. So what can we do to make sure that our pivot table is updated? Well, very simply, we right-click on that particular cell that needs updating and then refresh. And then that should refresh the entire table. And again change the grand total value. So that's everything, all the basics that you need to know about pivot tables. I hope that was helpful to you and make sure that you post your discussions are any questions that you have further questions in the discussion box. And I look forward to seeing you soon. 10. Saving & Printing Your Work: Our penultimate lesson, I know you're sad. Bach, all good things come to an end. We're going to speak about saving and also printing in Excel. Now again, very, very important, especially if, for example, you're in a situation where you need to print an Excel worksheet to bring into a meeting. We want to be able to do that correctly. So this is what I'm going to show you here. So we're back in our listing and sorting tab, which is 1.4. And what I've just done here is I've taken this initial table which had all of my investments and crypto currencies. And I've just put it into, you know, just kind of aligned it closer to the left of the page itself. Now I've done that for a reason. And the reason is is that when it comes to actually printing this information, I was to go to file and then print. You would see that our initial table that had our information in it no longer is visible within the first page and they files just to print that page, which is what he set out at the moment, then we would only be able to see everything that is to the left of this checkered line, which is just over here between g and h. So notice how everything to the left of that line we can't see in page 1, everything to the right of that. We cannot see IR original the original location of the table. So what I've done is I've literally just copied and pasted the table into the first page. And now we can see that page and print everything that needs to be printed in there. Again, we're in page layout, which is in our Excel ribbon. If you remember from before, is a tab that features a lot of the options that we would need to print our, our information correctly. So just here to the left, we've got themes. We can change the colors of things, you know, alter the colors of our tables if we so desire or wish. Just going to leave it how it is for now again, we can do the same thing for are the fonts on our tables and our pages. Oh, what we can also do as well is what we can also do as well, is play around with the margins as well. So the margins will determine how much information that you can put on a particular page. If you want to make it narrow than that would just mean that we can put more information on a page when it comes to printing. If you want to make it normal or wide, it just means there's a bit less information that we can add to the page orientation. Again, that will affect where this page break is, which is in-between G and H. So if we were to change it to landscape, then you would see that that page break would jump, um, and it will give us more of a width rather than a length. Sizes is very interesting here as well. Because again, it gives you some predetermined aspect ratios depending on the style and the size of paper that you want to print on. So we usually print on a four. But if you wanted to print on an A3 sheet, which is slightly larger than, again, that is something that you could change through here. Clicking on these will change where that that page break is. So that again is something to keep in mind. So there is again a page break at the bottom and that you can insert and set as well manually. If you ever wanted to put a background in an Excel spreadsheet or worksheet, I haven't found a use case for this, but if you're particularly artistic or creative, you could add a background in there and add some personality to your worksheet. And again, you've gone option for print titles two. So width, again, you know, you can do is determined automatically by Excel. But again, you could change that. And you can also change the scale of what you're printing too. So say for example, you want to scale it to just 30 percent. So your table is literally 30%. Within that particular page. You could do that or you could just keep it at a 100 as it usually is, grid lines. Again, if you wanted to print without the grid lines and not make it look so much like an Excel worksheet, then you could. And similar with the headings as well. If you didn't want to print your worksheet with headings, you can take those out. I'm bringing forward and sending backwards is literally just an option for if you say had a, an image that was in your worksheet and it was in front of a table. If you wanted to send it behind the table, you could. Or if you had two images that were piled on top of each other within your worksheet. Or you can send one to the back or forward depending on what you wanted to do with it. Right. I think our pages ready to print. We're just going to print page one for now. So what we're gonna do is we're gonna go into file, the file tab, and then scroll down to print. And then as you can see, our table is basically viewable just on here. And it gives you basically a preview of what you'd expect to print. As you can see, it's got pages one of three. See what the rest of the table on there, which is not what I want. So what I'm gonna do is I'm just going to go into settings and just go from pages one to one. So that should just mean the eye only print that one page. Just above that. Actually, if I was to click into Microsoft print to PDF, there are various other options just here. I haven't seen a fax machine for a long time, but if you do have a fax machine and then Good on you, you've got that option there to use it. And again, you can increase the number of copies just above. A few more options just here to cover. Again, print to active sheets is really important because our active sheet is the one that we're currently in. And by clicking on print active sheets, it enables us just to focus on that one worksheet. But if we wanted to print the entire workbook itself, then again, that is another option I know is how the Print Preview has now changed. So as opposed to just three pages is now giving me 21 pages to print from, Aunt choose from. And then finally print selection. So say for example, if there was a certain cell group within our worksheet that I wanted to print off and I didn't want to print anything else. I could then select those cells and then select in print settings, print selection, and it would just print off those particular cells. So again, I'm just going to go to Print active sheets because that's why I actually want to do pop portrait orientation again, you can print in portrait or you can print in landscape. A4. Just the aspect ratios we've already covered, and margins as well. This is a really interesting option, fit all rows to one page. So basically, this is all about the scale of what you're printing. So I've clicked here into no scaling, which is basically a, what you see is what you get. Whatever I put into the worksheet is what I'm eventually going to see as printed. If I say, for example, wanted to fit the entire sheet into one page, I bring in the other table and not need to print or three pages all at one time, I'll click on fishy into one page. And there you go. Even though it's shrunk, my information, I've basically allowed Excel to squeeze everything into that one page. There are few other options as well. I could fit all the columns into one page 2, or I could fit all the rows into one page. There are a few more kind of advanced options just here that are specific to printing. Either print quality or the paper size. The margins that you use can custom set those if you wish. But for now, you should be fine to use those to print off your worksheets just before we end. Few comments on saving. So depending on the version of Excel that you're using, this might look slightly different. But generally speaking, on Windows machines, it should look like this. So you are Excel gives you almost a prompt as two, where it thinks that you're file suits, usually somewhere in your desktop. You can again change that by clicking on this, changed the name of your workbook, and even change the format, which I wouldn't recommend unless you wanted to print a PDF. You can create a new folder if you don't have a home for your workbook or worksheets already. And again, you can almost see a preview of everything that you would have selected above. So that's how you save and print your files and worksheets on Excel. 11. Course Complete & Outro: Hello and welcome to our conclusion to this course on Excel for beginners. I just want to say first of all, thank you so much for being so attentive. Over the last few lessons. We've covered a lot and hopefully you've managed to get as much value out of this course as possible. If you have a peaceful free to leave a comment or a review or just to, to, to, to just say thank you maybe or maybe even point a few tips and tricks that you've learned along the way of that would be much appreciated. And I'm always in the comments looking at any type of interaction. That view is half. So please do that just to kind of give you a breakdown of what we've covered. Over the last few lessons. We spoke briefly about the layout of Excel. We then went on to talk about formatting. Formatting your data to be able to make it view unviewable correctly. We then spoke about formulas, different types of formulas that we could use to manipulate certain bits of data within our worksheets. We then spoke about the different listing and sorting options bats that Excel has, which again will prove very useful for you in future. We spoke about pivot tables, the dreaded pivot tables that people fear. And hopefully we've alleviated some of those fees as well. And then finally, we spoke about pasting the paste special function and saving and printing. So hopefully throughout all of that is giving you a basic understanding of how to use Excel effectively. And I look forward to seeing you on our next courses. Don't forget to leave a comment. It's been a pleasure. My name Sorry. Been your instruct off and take care and see you soon.