Interested in a specific video? Purchase one of our ad free courses individually. Looking for even more? Gain access to exams, certificates, and exclusive content at learnitanytime. com. More information can be found in the video description below. Hi everyone. In this lesson, we want to overview how to navigate in Excel and get you familiar with some of the vocabulary that you'll actually see used in the platform. Microsoft Excel is a data analytics program that's been around for over a quarter century..
It's really the main application that people use to calculate and analyze their data in Excel. any business setting. Now, the first thing to know about Microsoft Excel is that everything is contained in cells, hence the name Excel. You'll see here that in my current spreadsheet, I have these little boxes. They're called cells. They allow me to designate data to a specific spot within this grid system. It's fantastic when you're trying to analyze and calculate data. You'll see that columns are known as letters, rows are known as numbers, and.
The way you locate a specific cell is to click in it and it will be known by its column letter and its row number. Now if you look up at the top of Microsoft Excel, it uses the same interface as the other Microsoft Office applications, like PowerPoint and Word. So you'll see that there are ribbons that you use to access different buttons. And when it comes to navigating, you click in a cell to locate the cell you want to type in. You can also select rows and columns by clicking on column letters, or row numbers. And if you ever need to know what cell you're in, you can come up and look in the top left hand corner..
You'll see again, the cell name box that will tell you the name of the cell that you're currently clicked inside of. You can select multiple cells. This is called a range by using your mouse or your keyboard. And you can select non adjacent ranges by selecting one group of cells, then holding down your control key on your keyboard to select a different group. Now the other critical thing to know about a spreadsheet is it also has sheets. Down in the bottom left hand corner, you'll see that my current workbook has three sheets in it. The reason for sheets are to allow you to organize data by sheet..
So join me for the next lesson. Hey, everyone. I'm ready to look at how to do data entry inside Microsoft Excel. I just have a blank workbook open, and if you're trying to follow along with me, feel free to open up Excel as well and do this as we talk about it. Now, all you need to do to begin doing data entry in cells is click in a cell and start typing. I'm going to click in cell B2. Remember, your cells are known by their column names. letter and row number. When I click in cell B2, I can simply start typing..
As soon as I hit enter, you'll notice that the data entry is complete. Now, another way to do data entry in Excel is to use the formula bar. It is used for two things, creating formulas and doing data entry. The formula bar is located right below the ribbons of Microsoft Excel. It can be turned off, but by default it should be turned on. You'll see this long white bar where you can do data entry. So I've clicked in another cell, C2. This time I'm going to go ahead and type in the topic that I want entered into my formula bar..
You'll notice that as I type, that whatever I type here in the formula bar gets entered in the cell below. Once I hit enter. You'll notice that that information gets put into the cell. So I can do data entry by clicking and typing or by using my formula bar. Now let's chat for just a minute about keyboard shortcuts. When you're navigating inside an Excel workbook, you can use your scroll bars, but they can be a fairly slow way to navigate. So I just want to make a few suggestions. One of them is you can actually use the cell name box next to your formula bar for fast ways to navigate..
If I know that I need to get down to a cell quite a ways down in my workbook, I could scroll, but scrolling is one of the slowest ways to navigate. So instead I'm going to click in the cell name box and enter in the column letter. In this case, the column letter is M. The row number is 500. And it doesn't matter if your column letter is uppercase or lowercase. I'm going to hit enter. And notice that my computer in Excel takes me all the way down to cell M500. Now, how do I get back to cell A1 quickly? I love the keyboard shortcut, Control Home..
This is on a PC. On an Apple computer, you'll need to use the Command button for this. But what you're going to do is look for the Page Up key on your keyboard. The Home button is always located right next to it. Hold down your Control key and then your Home key, and it will take you back to cell A1. A1 in any workbook. This is a fast way to use your keyboard as opposed to using the scroll bars because sometimes the scrolling will take longer than using those keyboard shortcuts. So just remember, get a new workbook open and practice.
Posts Related:
- How To Create Your Own Tournament Generator With Leaderboard In Excel Free Download + Masterclass
- How to setup Work from Home - Tips and tricks (corona covid19) *Free download* #stayhome
- DAX for Power BI Part 2.6 - Dates in Calculated Columns Office
Typing in cells and navigating. You'll get the hang of it very quickly. Join us for the next lesson.
Welcome back. We're ready to show you how to use rows and columns in your workbook to edit the data in your Excel spreadsheet. I'm using a practice file right now called practice two. Please remember you can use these practice files to follow along. They're available in the link in the description below the video. Now what I want to do is go into my workbook and I'm going to click on column F. When you click on the letter for a column, you'll notice that it selects.Everything in the column below. Now once everything is selected, if I hit the delete key on my keyboard, notice everything in that column will get deleted. Now if I come up and click on the undo button, remember top left hand corner, up on the toolbar, the quick access toolbar, it will bring it back. What if I wanted to insert a new column into my workbook? I'm going to come into the column and right click on column G. On a PC, when you right click on a column letter, you'll get a secondary menu that.
Gives you opportunities to do things like insert new columns or delete columns. Please remember that if you delete a column, Notice that everything including the content in the column will be deleted. I'm going to undo that. In this case, I want to insert a new column. So I'm going to right click. By default, when you insert new columns, they always insert to the left. So you'll see that the current column moves over the one that I had selected, and I get a new blank column. I'm going to go ahead and undo that. Rows work the exact same way. In this case, I want to select more than one row..
All I need to do is come in and I'm going to highlight rows seven through 11. You'll notice that I can select all the content in those rows just by left dragging on the row numbers. Now that I have these selected, I'm going to right click. and say insert. Now I currently have several rows selected. When I insert new rows, they'll go above and the same number of rows that I have selected will be inserted into my workbook. So you can see that by, again, working with rows and columns, you can manipulate large amounts of data in your workbook without having to individually select sections of the workbook with your mouse..
Because the columns and rows allow you to do it all at the same time. Feel free to use the practice files to play with rows and columns in a workbook of your own. Welcome back everybody. Let's talk about printing, saving, and opening spreadsheets. Now right now I have the practice file called Practice 5 Open. So feel free to use it as we explore these functionalities. When it comes to printing, I need to go up to my file ribbon to access the print option. I'm going to come to the top left hand corner because the file ribbon is the very first one on the ribbon tabs..
You'll notice that when you go to the file ribbon, it takes you to a part of Excel called the backstage view, where the rest of your spreadsheet is covered up. All the functionalities run down the left hand side. Print is right below save as. When you click on print, it takes you to a combination. Print task pane on the left and a print preview on the right. Now, I love this view because everything I need to preview and also update my print is right here. You'll see that you can do things like decide the number of copies you want to print, the printer that you're printing to, the portion of the workbook that.
You're going to print, and even do things like update your orientation. And I can view these changes as I make them and even update things like my margins right here inside the print dialog box. When I'm ready to print, I just click on the print button And my worksheet will suddenly be available on paper. Do note that the default is only to print the current sheet that you have selected. Now to exit print preview or the print dialog, I'm going to come to the top left hand corner and click on the back button. When I'm back in my workbook, you'll notice that changes that I make in print do not update the look of the workbook..
The only thing you may notice. is that you're going to suddenly see dashed lines in your workbook representing where page breaks are. And this happens after you go in and print a workbook. Now, what about saving this workbook? Let's say that I've come in and made a few changes and I'd like to make sure my workbook is saved. We're going to come up to the file ribbon tab again, go down to save or save as. Save is commonly used the first time that you save a workbook. Save As allows you to take an existing workbook, navigate to where you're saving it, give it a name, and when you click on Save, I'm going to type in my updated.
Name, that workbook file will be saved, and you can see the new name at the top. But saving in Excel is exactly the same as saving in, for example, PowerPoint for a presentation or Word for a Word document. Finally, how do I open up a file? Well, we're going to go back to the file ribbon tab again and come into open. This will take you into your computer where you can see different locations where you can save and open files. I'm going to browse, locate the file I want to open, either double click on it or select it and say, open, and then that file will open and I'll see its name up here at the top..
As always, remember, if you have multiple files open, a great way to switch between files is to come down to your windows taskbar.
Locate the Excel icon, and then you could switch between the different files by just clicking on them. And again, try this out in Excel on your own so you feel comfortable opening, saving, and printing files. Hi everybody, let's talk about formatting inside Microsoft Excel. Now what's really important in a spreadsheet is what's inside the cells. These are what we sometimes call your cell contents. Formatting is made to highlight or make it easier to understand the contents..Inside your cells. Right now I have practice file format one open. Feel free to use it to follow along as we try out these different formatting tools. There are three main things we want to talk about here. Applying color, alignment, and cell borders. Now first of all to apply any formatting you need to select the contents of your cells. In this spreadsheet I'm going to select cells B3 over to G3. Most of the formatting tools will be located on the Home Ribbon tab in the Font and Alignment groups. Or if you right click, you'll notice that you get a toolbar that has a lot of the same tools in it..
For this video, I'm going to be using the Ribbon options. Let's start with Color. I've selected my cells and the first thing I want to update is the color inside the cells. This is what we refer to as a font color. For this, I'll go to the font group and look for the capital letter A on the bottom row and I'll see that when I click on the arrow next to it, there are different colors I can apply to the writing of my cell. It's really important that when you apply font color, you ensure that the color you select is still going to allow people to easily read the contents of the cells. What about the background color of the cell? This is what we call fill color. For this, I'm going to highlight A4 down to A13, and directly next to the font.
Color button, you'll see a paint can. This is called fill color. This allows you to update the color inside the cells. Again, making sure that whatever fill color you pick does not make it difficult to read the text inside the cells. Now, alignment has everything to do with how things are aligned in the cells. For this, I actually want to go between rows four and five and make the row taller. The reason I want to do this, and notice that I'm hovering over row four and five, My mouse pointer turns into an arrow that points up and down, and I can drag down. This will make my row taller..
Each cell is like a box, so you actually have a left, center, and right alignment, and also a top, middle, and bottom. What I'm going to do is I'm going to highlight row four by just clicking on the number four. Remember, this will select everything in that row through the entire sheet that I'm on. Then I'm going to come to the alignment group. On the alignment group, you'll see that on the bottom, I have left, center and right alignments that I can use. I'm going to put the numbers in the center and then I have top, middle, and bottom,.
And I can use these to align something directly in the center of the cell. Now, in addition to that, you can also do alignments that are at a tilt. For this, I'm going to actually go in. and make row three taller by dragging it down. Then I'm going to highlight, again, cells B3 through G3. Up on that same alignment group, directly to the right hand side of the bottom alignment, you'll see a lowercase a and b button with an arrow pointing up and to the right. This allows you to actually tilt text inside the cells and put them at an angle. It's kind of fun, but again, make sure your cells or your row is.
Tall enough and the cells are wide enough for this to happen. Finally, cell borders. Now, it looks like I have borders turned on in my spreadsheet, but these are actually grid lines, and they don't print by default. To apply borders, I need to come in and highlight the cells I want to apply the borders to, and then directly to the left of the paint can or fill color. I'll see the borders button. When I click on it, it provides me with different borders that I can apply. I just select the border I want, click on it, and then when I click away, I'll see that border applied to the cells..
This is a border that will print. As always, try this out in one of the practice files or a workbook. Welcome back. Let's talk about formatting values or numbers inside of cells and also using one of my favorite tools to copy formats called the Format Painter or the Format Paintbrush. Now right now I'm in Practice File Format 2. Feel free to use it to follow along. Let's talk about formatting values or numbers. First of all, you'll see that in any spreadsheet, Values are always at the right..
Now remember that values include dates. And up here in this spreadsheet in cell B2, I actually have a date. And if I make the column a little bit wider, we'll notice that again, it aligns at the right. Text, which means any combination of letters and values together, or just letters themselves, will always align at the left. This is a tool to help you be able to view automatically a number, as opposed to something that is not a number. Now, how would I, for example, take these values and make them look like a currency because that's what they actually are? Well, first thing you need to do is highlight them. So I'm going to highlight cells B4 to G4..
Then on my home ribbon tab, I'm going to come to the number group. Now, Microsoft has conveniently put a dollar sign right on the second row of this group on the ribbon. When you click on it, it will automatically apply a dollar sign and two decimal places to all those values. Again, if you don't want the decimal places, notice that on the right hand side of the group, you have the ability to both increase and decrease decimals. Just be careful with this because if your values do have decimals and you remove them, the values will round up. I also want to apply this same format to my cells in B7 all the way over to G13..
So again, I'm going to select the values and go up and click on the dollar sign and apply a currency format to all those values in my spreadsheet. Now, what about dates? Right now, if I look at cell B2, I've made it a little bit wider, the column, to see the full date. Right now, this is called the short date format. Can I update this? Absolutely. I'm going to come in and go right to the drop down arrow next to the current field where it says date, because Excel recognizes this. And you'll notice that if I come in, it's already showing me a short date format that I currently have..
But I can update this to a long date format. Now what's common when you do this is that you're going to see number signs inside the cell. Anytime there are numbers in a cell and Microsoft Excel cannot show you all the information. It just shows you number signs because it never wants to show you incomplete information. A quick trick to fix this is to come right up and double click between the columns where you see those number signs and it will auto fit. the content so that you can see it. So now I have the long date format. Now what if I have a format, for example, these cells here don't have decimal.
Places and I'd like all the cells below to also not have decimal places. This is a great time to use the format painter or the format paintbrush. The first step is to select the cells that have the format that you like by going up and clicking on the format painter. Then I'm going to come in and I'm going to highlight the cells and notice when I do this, It makes my cell, or my mouse pointer in this case, look like a paintbrush. I'm going to highlight the cells that I want to copy the format to. When I release my mouse, it will have copied the formatting for me. This is a tool that you can use also in Microsoft Word and PowerPoint, and I use it all the time..
If you don't want it to turn off, Now, as always, try this in a spreadsheet of your own by, again, updating number and date formats and also playing with the Format Painter. Welcome back. Let's talk about Conditional Formatting and Merge and Center. Right now, I have Practice File Format 3 open. Feel free to use it to try this as we go. First of all, up in cell A1, I'd like to merge and center that title. I'm going to come in and highlight all the cells that I'd like to make into one. So in this case, cells A1 through G1..
One of my very favorite buttons, the merge and center button is on the home ribbon. In the Alignment Group, right under the wrap text or next to the wrap text button. When you click on this button, it takes cell A one and merges it so all the other cells, B one through G one, become part of it and it centers the content that was in cell A one. It's a fantastic way to quick, quickly center your title at the top of a workbook. Now the next thing that I want to do is I want to apply conditional formatting..
Conditional formatting is a way to make things pop inside of cells with formatting. I'm going to come in and highlight cells B7 all the way over to G10, basically all the expenses in the spreadsheet. Then to apply a conditional format. From my home ribbon tab, I'm going to come to the conditional formatting button. Now there are different kinds of conditional formats. We're going to try out a couple. The most common type is called highlight cell rules..
This allows you to have something formatted in your spreadsheet based on a criteria. For example, is something greater than. less than, between, or equal to. You can even do conditional formats that are based on text contained in a cell. We're going to start with greater than. I'm going to come in and say any value that is greater than 5, 000. You type that in, then click on okay. And You'll see that the conditional format turns the text and also the fill color in the cell red. Now, can you do multiple conditional formats?.
You can, but let's try a different one. For this, I'm going to go down to row 13 and highlight cells B13 over to G13. I'm going to go back up to the home ribbon tab in the styles group and select conditional formatting. This time I'm going to come down and show you a data bar. This is a conditional format where based on the data inside the cells, the values, the color will go further over. And you can see that there are different colors that you can pick. So in this case, I'm picking the green gradient fill. You'll see that for a cell that has a higher value, the color goes further over..
Now how do you edit a conditional format or clear it? I'm going to re highlight the cells in row 13 where I applied the conditional format. Go back to conditional formatting and come clear to the bottom of the menu to manage rules. Here in this box, I'll see the current conditional format that I selected. If I wanted to edit it, I could select it and click on edit rule. To clear a conditional format, the easiest way is to highlight the part of the spreadsheet where the conditional format is located. Go back to the conditional formatting button. and come down to clear rules. Just be careful that you select clear rules from selected cells because if you.
Pick clear rules from the entire sheet, we all know what's going to happen. You're going to lose all your conditional formats in the workbook. As always, try this out. It's a great way to make things pop or highlight things in cells. Welcome back. Let's talk about how we can easily see the top of our screen with the bottom in a large workbook. This practice file I've opened is called Navigate One. Feel free to use it to follow along. Now one of the challenges of Excel is how big a workbook can be. This particular workbook, if I scroll down in this sheet that I'm on, there are only about 200 rows..
But I would still spend a significant amount of time scrolling between the top and bottom if I'm trying to remember what my column headers are. Very common issue in Excel. Well, Microsoft has two different ways to deal with this issue. The first one is to split your screen. To do this, we're going to go up to the view ribbon. This ribbon is all about adjusting the view of your workbook sheet that you're in. And come to the window group. On the right hand side top of the window group, you should see a button that says Split. And what this does is it puts these gray lines in your workbook..
Basically what you're doing is splitting your workbook into what are like window screens. There's a vertical line that you can adjust by just resting your mouse pointer on it and dragging it to the right and left and a horizontal line as well. Now, what you'll see now is that you have. Two sets of vertical scroll bars and two sets of horizontal scroll bars. So what I can do here is stay at the top, in this top portion of the split, and scroll to the very bottom. And I can also be at the far left in one of the splits, and the far right in the other. So I'm able to align the spreadsheet and see it in different portions..
If you don't want one of the splits, for example, if you don't need the horizontal split, you can rest your mouse pointer on it, drag it down into the bottom of your screen, holding down your left mouse button as you do it, and you can basically just get rid of a split you don't want. To turn the splits off, you go back up to the ribbon and click on the split button and it will remove the split. Now I prefer a tool called freeze panes. This allows you to either freeze the top row on your work. Booksheet, or the top left column, or pick an apex point of a cell. For me, I'm going to go up and click in cell C2..
I'm currently at the very top of this sheet. And then I'm going to go back to the view ribbon tab, back to the window group, and look for freeze paints. There's actually a little snowflake on the button. When you click on it, it'll give you three different choices. Now, in this case, I'm not trying to freeze the very top row, even though I am, but I'm not trying to freeze the very far left column. I'm actually trying to freeze from cell C to above and to the left of that point. So I'm going to pick instead the option at the top that says freeze panes..
This means anything above and to the left of the cell that I have selected will be frozen in place. Now it's kind of hard to tell that this has happened, but notice if I look at the right hand side of the spreadsheet, I'd see a thin black line that goes above row two. And also if I look at everything to the left of column C, I can see the same thing. Now to really see this in action, you need to start scrolling down. So as I scroll down, I'm going to see that everything above row 2 stays fixed in place..
But the exciting part is when you start scrolling to the right. Because again, I didn't say the first column, I said everything to the left of cell C2. So when I start scrolling, Column A and B stay in place, but everything else is scrollable. So it's like you've frozen the spreadsheet in place. To turn it off, go back up to the freeze panes button again and say unfreeze panes and it turns off. As always, try this out in your own workbooks. These are tools that you'll use every day..
Hey, welcome back. Let's talk about some ways to save you time inside Microsoft Excel. For this, I have a practice file called Navigate to Open. Feel free to use it as you follow along. I want to start by talking about the Quick Access Toolbar shortcuts. I use these all the time, and don't forget that you can customize these to add shortcuts of your own. Now, the Quick Access Toolbar is actually one of the final toolbars still left in Microsoft Excel. It's important because on mine, this is where the Undo and Redo buttons are. Remember that if you go to the right hand side of the Quick Access Toolbar, you'll.
See a line with an arrow pointing down. When you click on this button, you'll see frequent shortcuts that you can add. I'm a big fan of shortcuts like the Print Preview and Print and the Spell Check. Also, Undo and Redo are usually pinned to your Quick Access Toolbar for you. If you see a shortcut that you want, or I should say, if you see a shortcut, That's not here, feel free to come down to more commands and add it. All you do to add the command is left click on it and you'll see the button get added. And these commands will always be here no matter what workbook file you open..
To remove them you just go back to the same arrow again. Come in and left click on the particular command you want to take off, but it's a great way to again, add your own shortcuts to Excel. Don't forget also that the quick access toolbar can be located above the ribbons, or you can come in and say show below the ribbon, and in this case, it'll be placed right above the ribbon. Your formula bar. I like to keep it in the top left corner. That way it doesn't get hidden, but it's up to you where you put it. Now, what other options do you have? We already talked about this, but I highly recommend learning a few critical.
