Beginning Excel - Mastering the Basics | Douglas Bush | Skillshare

Playback Speed


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

Beginning Excel - Mastering the Basics

teacher avatar Douglas Bush

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

49 Lessons (4h 35m)
    • 1. Section 1.0 - Welcome

      3:05
    • 2. Section 2.0 - Getting Started

      0:38
    • 3. Section 2.1 - User Interface

      5:55
    • 4. Section 2.2 - Quick Access Toolbar

      5:46
    • 5. Section 2.3 - Mouse Cursors

      3:48
    • 6. Section 2.4 - Basic File Operations

      5:35
    • 7. Section 3.0 - Creating Spreadsheets

      0:40
    • 8. Section 3.1 - Design Tips

      1:53
    • 9. Section 3.2 - Formatting Titles and Headings

      3:33
    • 10. Section 3.3 - Formatting Text

      5:06
    • 11. Section 3.4 - Formatting Values

      3:45
    • 12. Section 3.5 - Adding Graphics

      5:06
    • 13. Section 3.6 - Filling in a Series of Words, Values, or Dates

      8:26
    • 14. Section 3.7 - Adding Data to Multiple Sheets Simultaneously

      2:46
    • 15. Section 3.8 - Adding Notes and Comments

      6:19
    • 16. Section 3.9 - Adding Hyperlinks

      5:22
    • 17. Section 3.10 - Project 1 - 12-Month Calendar

      13:26
    • 18. Section 4.0 - Editing Spreadsheets

      0:38
    • 19. Section 4.1 - Inserting and Deleting Rows and Columns

      4:13
    • 20. Section 4.2 - Renaming Sheets and Changing Tab Colors

      3:17
    • 21. Section 4.3 - Moving Sheets

      1:38
    • 22. Section 4.4 - Moving Cell Contents

      3:59
    • 23. Section 4.5 - Clearing Cell Contents

      2:02
    • 24. Section 5.0 - Formulas & Functions

      0:41
    • 25. Section 5.1 - Using AutoSum

      0:52
    • 26. Section 5.2 - Basic Formulas

      5:09
    • 27. Section 5.3 - Basic Functions

      4:49
    • 28. Section 5.4 - Copying Formulas and Functions

      6:33
    • 29. Section 5.5 - Fixing Common Formula Errors

      15:51
    • 30. Section 5.6 - Project 2 - Track Sales

      22:36
    • 31. Section 5.7 - Project 3 - Weekly Payroll

      23:10
    • 32. Section 6.0 - Printing Issues

      0:23
    • 33. Section 6.1 - Fixing Common Printing Issues

      9:47
    • 34. Section 6.2 - Adding Headers, Footers, & Page Numbers

      15:14
    • 35. Section 6.3 - Repeat Headings on All Printed Pages

      1:38
    • 36. Section 7.0 - Charts

      0:21
    • 37. Section 7.1 - Keyboard Shortcuts for Charts

      2:50
    • 38. Section 7.2 - Pie and Doughnut Charts

      9:26
    • 39. Section 7.3 - Column and Bar Charts

      12:45
    • 40. Section 7.4 - Line Charts

      9:22
    • 41. Section 7.5 - Project 4 - Column Charts

      7:39
    • 42. Section 8.0 - Working with Large Spreadsheets

      0:17
    • 43. Section 8.1 - Navigating with Keyboard Shortcuts

      2:57
    • 44. Section 8.2 - Selecting Cells with Keyboard Shortcuts

      4:25
    • 45. Section 8.3 - Using Freeze Panes to Lock Headings in Place

      3:37
    • 46. Section 8.4 - Using Split Window Feature to See Different Sections of Spreadsheet

      3:49
    • 47. Section 8.5 - Hiding and Unhiding Columns and Rows

      3:39
    • 48. Section 8.6 - Project 5 - Working with a Large Spreadsheet

      10:13
    • 49. Section 9.0 - Conclusion

      0:28
  • --
  • 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.

80

Students

--

Projects

About This Class

Hello, and welcome to Beginning Excel – Mastering the Basics. This online course is based on a hands-on Beginning Excel class I’ve been teaching since 1992. More than ten thousand students from all walks of life have successfully completed that course – and you can too! Only now, you can attend in the comfort and safety of your own home or office.

Section 1 - Welcome:

This course is designed for people with little or no experience with Excel, and for those looking for a quick refresher. It includes a series of video lessons that explain key concepts in a clear, step-by-step manner. You can watch as I explain and demonstrate each topic. Feel free to skip topics you already know.

Each lesson builds on skills learned covered in previous lessons, and each section includes at least one exercise so you can practice what you’ve learned. After completing this course, you’ll be able to create a wide variety of different spreadsheets and charts. And you’ll learn many useful tips, tricks, and shortcuts along the way.

As you may already know, Excel is used to store, organize, and analyze text and numeric data. You can use Excel to track sales, do payroll, create profit and loss reports, invoices, budgets, calendars, lists, and much more.

Section 2 - Getting Started:

We cover the Excel User Interface, so you are familiar with important terms and what each part of the Excel screen is for. Then, we learn how to create a custom Quick Access Toolbar, so all our favorite commands are just one click away.

Next, we explain what each mouse cursor represents and when it is used. We follow up with basic file operations and some handy keyboard shortcuts.

Section 3 - Creating Spreadsheets:

First, we discuss design tips and "best practices" for Excel spreadsheets. Then, we cover the basics of formatting titles, headings, text, and values. Next, we learn how to add graphics, notes, comments, and hyperlinks. We show you how to fill in word, value, and date patterns and how to enter the same data on multiple spreadsheets simultaneously.

We complete this section with project. We use Excel to create a twelve-month calendar using many of the skills we learned in this section. You can just watch - or follow along!

Section 4 - Editing Spreadsheets:

In this section we learn how edit an Excel spreadsheet: We'll insert and delete columns and rows, move and copy sheets, and rename and change the color of sheet tabs.

Section 5 - Formulas & Functions:

We'll learn how to use AutoSum to total columns and rows. Next, we'll cover writing formulas so we can add, subtract, multiply, divide, and calculate percentages. The, we'll learn four simple functions: Sum, Average, Max, and Min. We'll also learn how to copy formulas and functions and fix common formula errors.

We complete this section with two projects that incorporate skills we just learned. You can watch or follow along.

Section 6 - Printing Spreadsheets:

In this section we learn how to improve the printed output of a spreadsheet and how to fix common printing errors. We'll add headers, footers, and page numbers to a spreadsheet and learn how to make headings appear on all printed pages.

Section 7 - Creating Charts:

We cover the five most common Excel charts. Learn how to create and edit Pie, Doughnut, Column, Bar, and Line Charts. We'll finish this section with a project based on column charts.

Section 8 - Working with Large Spreadsheets:

Working with large Excel spreadsheets can be challenging so we cover some handy keyboard shortcuts for navigating and selecting cells. We'll learn how to use Freeze Panes to make column and rows headings visible as we scroll through a spreadsheet. Then, we'll use the Split Window feature to view different parts of a large spreadsheet. Finally, we'll learn how to hide and unhide columns and rows for viewing and printing purposes. We close out the section with a project so we can practice what we've learned.

Section 9 - Conclusion:

We wrap up the course and look forward to using what we've learned.

 

Meet Your Teacher

Teacher Profile Image

Douglas Bush

Teacher

Hello, my name is Douglas Bush. I've been a software instructor since 1992 and I specialize in Microsoft Office and Quickbooks training. I've trained over 20,000 students from all walks of life: individuals and groups from the private sector, government, corporate, and military. Last year I was hired to create some online training videos and  found I enjoyed it so much I decided to move all my in-person courses into the digital realm. Thanks to recent events, I now have the time to pursue this goal.

See full profile

Class Ratings

Expectations Met?
  • Exceeded!
    0%
  • Yes
    0%
  • Somewhat
    0%
  • Not really
    0%
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.

Transcripts

1. Section 1.0 - Welcome: Hello and welcome to beginning Excel mastering the Basics. This course is designed for people with little or no experience with Excel. And for those looking for a quick refresher. After completing this course, you'll have the skills and confidence to create a variety different spreadsheets and charts. First we'll cover the Excel user interface and getting around. Then we'll create a custom Quick Access Toolbar. So all our favorite commands are just one click away. Next, we'll discuss the different mouse cursors and what they're used for. We'll talk about basic file operations. Then we'll cover best practices for the layout and design of spreadsheets. Will learn how to center a tidal and wrap column headings. Then we'll cover formatting text and values. We'll learn how to add, subtract, multiply, divide, and calculate percentages, as well as learn how to fix common formula errors. We'll cover several basic functions such as sum, average, max, and min. And we'll learn how to create the five most common charts, including pie. Column. And line charts, will offer some printing tips and how to get the best results. And learn tips and tricks for working with large spreadsheets. So let's get started. 2. Section 2.0 - Getting Started: In this section, we'll explore the user interface and become familiar with some important terms. Then we'll learn how to create our own Quick Access Toolbar. So our favorite commands are just one click away. Then we'll see what each mouse cursor represents and when to use it. And finally, we'll cover the most common file operations and learn some handy keyboard shortcuts. 3. Section 2.1 - User Interface: In this lesson, we'll explore the Excel user interface. Starting in Excel 2013. The program displays the backstage view when it first opens. The backstage view is really the File menu. From here, we can create a new blank workbook from scratch or base it on the template. We can also open a recently used file from the list below, or search for my name. We can click the pin button to add frequently used workbooks to the pinned list. To see the change, we need to exit backstage view and then reopen it. We can unpin a file at any time. By clicking the unpin button. We'd like to create a new workbook. So we'll click the blank workbook button. The title bar appears at the top of the screen. It shows the name of the file you have opened and the program you're using. In the upper left-hand corner, the title bar, you may see auto save if you're using a more recent version of Excel. This feature can be turned on or off. And next to auto save is the Quick Access Toolbar, which allows you to create your own custom toolbar, will cover the Quick Access Toolbar in a separate lesson. In the upper right-hand corner of the title bar or the Maximize, Minimize and close buttons. And depending on which version of Excel you have, you may see additional items on the title bar, such as your account name. The main method of navigation is through tabs, which are really just menus. The tabs are located directly below the title bar. You can click a tab to display the corresponding ribbon. Ribbon is just a horizontal bar with a series of related commands organized into command groups. You can think of a ribbon is a horizontal menu with buttons. The ribbon may be displayed or hidden by pressing control F1. Each ribbon is divided up into command groups, which are really just categories. Some command groups include a small arrow in the lower right-hand corner. Clicking this button will display additional commands in that category. Below the ribbon, you'll see the formula bar, which displays the contents of whatever cell the select. On the far left is the name box, which displays the name of the current cell or address. You can use the Name Box to navigate to a cell. Simply type in the cell address in the name box and press enter to go to that cell. Most of the screen is made up of cells. This is where we enter text, value, formulas and other things. Each cell has a unique address which is made up of as column, letter and row number. Cell addresses or cell references as they're sometimes called, are very important. When we start building formulas will use cell addresses rather than actual values. In other words, instead of typing two plus two, we might type A1 plus B1. More on that. Later. In the lower left-hand corner of the screen, you'll see a sheet tab. Each tab represents a different spreadsheet, much like a page in a book. In the lower right-hand corner, you'll see the horizontal scrollbar. Hand off to the far right. You'll see the vertical scrollbar. Finally, the status bar appears along the very bottom of your screen. It contains view buttons and zoom control features in the lower right-hand corner. And you can right-click the status bar to add or remove features. 4. Section 2.2 - Quick Access Toolbar: In this lesson, we'll learn about the Quick Access Toolbar, which allows you to create your own custom toolbar. This way you can find the features you use most often and access them with a single click. The Quick Access Toolbar is located in the upper left-hand corner of the screen. But it's a good idea to move it closer to your spreadsheet so it's easier to see. We'll click the Customize Quick Access toolbar button and select show below the ribbon. The Quick Access Toolbar now terms gray and it's directly above our spreadsheet. To add a button to the Quick Access Toolbar. Simply right-click it and choose Add to Quick Access Toolbar. To remove a button, right-click it, and choose Remove from Quick Access Toolbar. You can also add an entire group of related commands. For example, let's go to the Page Layout tab in the upper left-hand corner. And instead of adding each individual button separately, will point to the name of the group Page Setup. And then right-click and add that to the Quick Access Toolbar. At first it looks like we've added only a single button. But if we click it, you can see that the entire command group is there. This way we don't have to search for those commands. There are always one click away. Another way to add things to the Quick Access Toolbar is to select the customize button and place a checkmark next to any item in this menu. For example, if we'd like to include new, we can left-click that option. And the button appears. Will also select open and quick sprint. To remove any of those items. We can go back to the customize button and uncheck the item. Another way to add buttons is to go to the customize button. Select more commands. On the right-hand side of the screen. These are the buttons that are currently on the Quick Access Toolbar. To the left of that. These are commands that we could add. We can select different categories. For example, we might choose features that are on the review tab, on the Developer tab. Or we could select all commands. Will select one, click the Add button, and now will appear on our Quick Access Toolbar. We can also change the position of that button. If we move it up, we're actually moving it further to the left on the Quick Access Toolbar. And if we move it down, we're actually moving it to the right. Let's click OK. And there's our new button. To return to the default settings. Will click the Customize button. Select More Commands. And in the lower right-hand corner, we'll select the Reset button. And Reset Only the Quick Access Toolbar. Let's answer Yes. So we're back to the default setting. And we can click OK. To return the Quick Access Toolbar to its original position. Will go back to the customize button and select show above the ribbon. So now we know how to create our own custom toolbar. 5. Section 2.3 - Mouse Cursors: There are many different mouse cursors in Excel and each one has a different purpose. In this lesson, we'll learn what each mouse cursor represents and when to use. The left arrow or a pointer is used to select buttons and tabs. The large plus sign is used for highlighting or selecting cells. This is typically used when formatting, moving, cutting, or copying cells. The width adjustment cursor appears at the top of the spreadsheet. In-between column headings, we can click and hold down the left mouse button to adjust the width of the column. The height adjustment cursor appears along the left margin of a spreadsheet and it allows us to adjust the height of a room. We can click and drag to increase the height or decrease the height. The black down arrowhead is the column selector. A single click will select the entire column. To select multiple columns, we can click and hold down the left mouse button and drag over the column headings. To deselect. We'll click inside any cell. We can also select non-contiguous columns using the control key. Will left-click on the first column, hold down the Control key, and select additional columns. The black right arrow key is the row selector. If we click once with the left mouse button, we can select the entire row. To select multiple rows. Will hold down the left mouse button and drag to highlight an additional rows. To de-select. We can click in any cell to select individual rows. We can use the Control key. Let's select row four. Hold down the Control key and select row 11 to four headed arrow with the left pointer is used to move cells. The cursor appears around the edge of selected cells. We can hold down the left mouse button and drag to change the position of ourselves. The small plus sign called the fill handle, is used to fill or a copy things. This cursor appears in the lower right-hand corner of a cell. We can hold down the left mouse button and drag down the comb to fill or copy the formula. 6. Section 2.4 - Basic File Operations: This tutorial will cover basic file operations such as open, print, close. To create a new file. We select the file tab in the upper left. Then choose nu. Now we can either select blank workbook or locate a template online. We'll select this template and click the Create button to download it. Using a template can save a lot of time and effort. And at the very least, we can get good ideas that we can incorporate into our own projects. To save a file. Google up to the File tab. Select save. Then browse to the location where we'd like to save the file, name our file. And then click the Save button. To close the file, will go up to the File tab and select close. To open a file. To the file tab. Select Open. We'll browse, locate the file we want, select it and click the open button. To print a file. We go up to the File tab. Select Print, select whatever options we want, and click the print button. Now we'll learn some handy keyboard shortcuts for performing basic file operations. To create a new blank workbook. Press control, control file. The Enter key. To close the file. To open a file. Control. 7. Section 3.0 - Creating Spreadsheets: In this section we'll cover design tips and best practices for creating spreadsheets. Then we'll learn how to format text and values and add graphics. We'll learn some shortcuts for filling in Word, value and date patterns. And learn how to enter data on multiple sheets simultaneously. Will add comments and hyperlinks to our spreadsheet. And finally, we'll create a 12-month calendar project that incorporates everything we've learned so far. 8. Section 3.1 - Design Tips: In this lesson, we'll learn some best practices for creating a spreadsheet. When designing a spreadsheet, it's a good idea not to be too creative. By that. I mean that people expect to see things in certain places. If you ignore this fact, other people may be confused when they look at your spreadsheet. Here are some suggestions. Place your data in a grid or block with no blank rows or columns. There is one exception. You should include a blank row between your data and your total throw. Otherwise, you may have problems when sorting data. Calculation should be in the right-hand columns or bottom rows. Us borders or fill colors to visually organize your data. In general, use dark text against a light background. One exception is it's okay to reverse this for titles. Try to limit yourself to two to three different fonts. Also, don't use more than two or three colors, and avoid bright or distracting colors. Alternating row colors is always a good idea. You can use the Format as Table feature to accomplish this. 9. Section 3.2 - Formatting Titles and Headings: In this lesson, we'll learn how to format text. Let's say we'd like to center our title at the top of our spreadsheet. We can click in cell A1 and highlight over two k1. You may be familiar with merge and center. That's located in the alignment section. Clicking that is simple, but you may encounter merge errors later on. So in general, I don't recommend using merging center. Let's click the button to unmatched those cells. To achieve the same effect without the downside of those merge errors, we can go to the Dialog Launcher button in the lower right-hand corner of the alignment section. And where it says text alignment, horizontal. We'll change this setting from general to center or cross selection. And click OK. So we achieved the same effect. But these cells are still individual separate cells. They haven't been merged together, which means we won't encounter those merge errors. Now let's apply some formatting to our title. We can change the font, the font size. We can also add borders. Very common option is the Old Borders feature. This will place a border around the outside perimeter of their cells. We can also add a fill color or background color. Had even changed the font color. If we decide to our column headings in row three are cutoff. What we're gonna do is wrap the text in those cells. So we'll highlight a3, k3. And in the alignment section, let's select Wrap Text. This way we're able to fit more columns on the page. While we have the cell's selected. We're going to apply borders, a fill color. And we'll center those words within each cell. There are some of the most common text formatting features that we'll be using in our projects. 10. Section 3.3 - Formatting Text: In this lesson, we'll learn how to format and align text and add borders and background colors to cells. If you're already familiar with formatting text in Microsoft Word, you'll find that Excel is very similar. In this exercise, we will cover the most common text formatting features. Let's start by selecting cells B11 through before. And first we'll change the font from Calibri to Arial. Then we'll increase the font size from 11 to 12. We could've increased the font size using the increased font size button. And we could decrease it by clicking the Decrease Font Size button. Now we'll add bold, italic and underline formatting. Lets include borders, will select all borders. So we placed borders around the perimeter of each cell. And we'll finish by adding a fill color and changing the font color. Now will change the alignment of our text. But first, we need to stretch the width of column B. And we need to increase the height of the first four rows. And we'll go back and highlight B1 through before. Currently our text is aligned to the left and the bottom of the cell. Let's change the horizontal alignment. Instead of left alignment. Most Select Center. And then we'll try right align. Above. We can change the vertical alignment. This will move the text to the top of the cell. This will place it in the middle. And this button will place it at the bottom of the cell. If you'd like to perfectly center, your text, will go with middle align center. Now we'll experiment with some orientation options. Let's click the orientation button and we'll check out Angle Counterclockwise. Let's go back and choose Angle Clockwise. Next, we'll try Vertical Text. Let's rotate the text up and rotate the text down. The last two options are handy when you're trying to retain narrow columns. To return to the original orientation. Will go back to the orientation button. Select format cells. Alignment. Hand will change the orientation from 90 to 0 degrees. And click OK. And before we look at the last two options, let's return our horizontal alignment to left. If we'd like to indent our text, we can click the indent button, which moves it to the right. And to move the text to the left, we'll click unintended. So now we know how to format and align text. 11. Section 3.4 - Formatting Values: In this lesson, we'll learn about formatting numbers. There are many different number formats available, and it's a good idea to see what each one looks like. By default, all cells have general formatting. If we type in the number two and then press Enter to move down to the next cell. This is what general format looks like. A typical number. In cell B2 will try number format, which includes the decimal place and two zeros. When we type in the number two, as soon as we move out of the cell, we see the difference. In cell B3 will try currency formatting. When we type the number two in that cell and then press enter, we see how Excel includes the dollar sign. Besides currency formatting, there's also accounting formatting. Will try that and type the number two to see the difference. The currency formatting will change of course, depending on what country you live in. There are two different options for formatting percentages. If we select presented from the menu and type into two, we notice that it comes out as 2%. But if we click the percent Style button, the decimal place and zeros will be dropped. Sometimes a value has too many decimal places and we'd like to round it off. For example, when I type in this number and move out of that cell. Let's say like to round that number up to 7.94. In the number section, I can select Decrease Decimal. And if I click it two more times, I can actually round the number off to eight. It's important to note that 7.938 is still there behind the scenes. All I've done is make it appear to be the number eight. If we'd like to display additional decimal places, to see a more precise number, we can click the increase Decimal button. And finally, we can apply AMA style by selecting this button. If we type in the number 1000000 and move on to that cell, Comma Style includes the comma, the decimal place, and two zeros. 12. Section 3.5 - Adding Graphics: In this lesson, we'll learn how to insert a graphic into a spreadsheet. We'll add a logo and a background. Logos are often added to the tidal section. If your company has a logo, you can always go to the website, right-click the logo and save it to your desktop, and then inserted. You may also be able to copy and paste it. In this example will be inserting an online picture. Will go up to the Insert tab. And we have the option to choose picture or icons. If you have an older version of Excel, they used the term clip art. We'll select pictures. And then online pictures. Let's type in the word logo and press enter. Here are just a few examples of logos. But select one. Then click the Insert button. Like any other picture, we can grab the corner and resize it. We can also select the label that comes along with it. Click the border and press delete. And now we'll drag the logo into position. And we may do a little more resizing. Now let's see what that looks like in print preview. We'll go to File Print. Next, we'll add a background to our spreadsheet. Normally when we add a background, it's much too large and it takes up the entire screen. Let me show you how to deal with that issue. To add a background image, will go to the Page Layout tab and select the background button. Again, will go to Bing image search. This time we'll click to background category. And of course she wanted to choose a background that's not too distracting. Will select the first one. And click Insert. Obviously, the background appears in blank cells as well. So what we'll do is we'll hide those columns to make it less distracting. Will left-click on column L, then hold down control and shift and press the right arrow key one time. This will select every column to the right of Column L. Now we can right-click any column heading and select Hide. We'll do the same thing with rows. Let's select row 14. Mooc, press control, shift down arrow. Then Right-click any row number and hide it. Now the background image is less distracting. If we ever designed to remove the background. Of course we would go back to Page Layout and select the button, Delete background. So now we know how to add graphics through an Excel spreadsheet. 13. Section 3.6 - Filling in a Series of Words, Values, or Dates: In this lesson, we'll learn how to fill in patterns of words, dates, and numbers. We already know that we can use the fill handle to fill in formulas. But it's also used for quite a few other things. Here are a few examples. Let's say we need to track things by the month. We'll type in January and cell A1 then rule or the lower right-hand corner. And when we see the fill handle, we can hold down the left mouse button and drag it down to row 12. This pattern is built right into Excel. Another common one is the abbreviations for January. We'll type JAM and B1 and use the fill handle to copy that down. Incidentally, this also works going across the row. For example, if I chose January in B1, I can fill that across. So it works either direction. I'm going to undo that. There are few other word patterns that we should be aware of. In C1 will type Monday and fill that down. If we were to drag that down further, Excel will cycle through that series of words over and over and over again. Again. I'll undo that. The abbreviation for Monday also works. We'll type MO N, d1 and fill that down. This also works for other types of patterns. For example, in y1, we'll type Q, T R, short for quarter, followed by a space. And then the number one. If we fill this down to row 12, we can see that Excel understands that there are four quarters in a year. Here's another pattern. In F1. We'll type year, followed by a space and the number one and fill it down. So as you can see, these are great time savers. Besides working on word patterns, Phil also works on date and number patterns. For this next example, will stretch the column. And let's say we'd like to enter a series of dates. We will start using a universal date format, at least in the United States. This will be January first 2020. When we fill it down. Excel recognizes that it's a date and fills in the pattern for us. Now if we don't like the date format, we can always go up to the Format field and change that. For example, maybe we prefer long date format. I'm going to click in G1 and fill that series in. Again. The reason why I did that is I wanted this autofill options button to show up again. If we select the drop-down arrow to the right, we have quite a few different options. For example, we could fill in the years Or we could fill in the months. We can even fill in the weekdays. Notice that it skips Saturdays and Sundays. Excel can also fill in patterns of numbers. For example, let's say I need to enter 1234, et cetera. If I fill that down to row 12, initially, Excel copies the number one. But I can go to the autofill options button and change that to fill series. To save a few extra steps. I could have entered it this way. Let me start with the number one. But this time I'll hold down the Control key. As I fill that down. That force is Excel to fill in the pattern immediately. We can also fill in patterns like this. If we need to fill in a pattern such as ten, 20-30, then we need to indicate what the pattern is. Highlight the two cells that make up the pattern. Then use the fill handle to fill in the pattern. This can be used for any pattern of numbers. For example, Let's say we want to increase by five each time. Highlight the two that make up that pattern, and then fill. So now we know how to use the fill feature to fill in words, dates, and numbers. 14. Section 3.7 - Adding Data to Multiple Sheets Simultaneously: In this lesson, we'll learn how to add data to multiple sheets simultaneously using the grouping feature. Grouping is much faster than copying sheets, and it works for formatting formulas. And more. Lets say we want to track January through December sales. Each sheet will have the same layout, formatting, and formulas. The quickest, easiest way to accomplish this is to select the January sheet, hold down the Shift key, and select the December sheet. Notice in the title bar it mentions group. To ungroup sheets. We should be able to left-click any individual sheet. Will select those again. And now everything we apply will appear on every sheet. Now Let's de-select the sheets by single clicking anyone. And if we look at any one of these 12 sheets, notice the formatting and text appears on each one. So again, the supplies to Borders, formatting, formulas, functions, et cetera, to great time-saver. And it's much faster than creating one sheet and then copying it. So now we know how to use the grouping feature to add data to multiple sheets simultaneously. 15. Section 3.8 - Adding Notes and Comments: In this lesson, we'll learn how to work with notes and comments. Notes are used for reminders, additional information, documentation, or instructions. In older versions of Excel. Notes recalled comments, which has led to some confusion. We can right-click any cell to add a node. When we click outside the Nope, it disappears. The red corner indicates a note. And if we hover over that red homeowner, the node appears. We can right-click the red corner and either edit, delete, or select Show Hide nodes. If we select Show Hide note, we can make it so the note is always visible. Nodes can be moved, resized, and deleted. If we have multiple nodes, we can navigate back and forth between them. In this case, instead of right-clicking, we can go up to the review tab, click the Notes button, and we'll add a new node. That way. Let's click outside that. Now we'll quickly add two more notes. First, we'll go to the cell where the original node is located. And if we go back to the nodes button, we now have options and allow us to navigate between the nodes. Let's click on next node. We'll click the nodes button again. Move on to the next node and the next one. And of course we can also go to previous nodes. We have the option of displaying all the notes or hiding all the notes. Comments as they're used in newer versions of Excel are threaded conversations, meaning other users may respond to our comments. Comments are useful when we're trying to resolve issues relating to our spreadsheet. Just like notes, we can right-click a cell. In this case, we'll select new comment. And we see it mentions start a conversation. After type we are comments. We need to click the green Post button. And then we can click outside the comment. In newer versions of Excel, comments have a purple corner. And just like notes, we can roll over that corner to view the comment. And as I mentioned a moment ago, these can be threaded conversations, meaning users can reply to comments. And we can follow the entire conversation this way. Let's click outside the comments. If we're on the review tab, we have several options here for comments. We can show the comments, hide them. We can move to the next comment or the previous comment. We can also roll directly over a comment. And we see the button on the right with the three dots. This allows us to either delete the thread or we can say that it's resolved. So deleting a course would remove it entirely. Resolve, would leave the comment, but it would indicate that the problem was resolved. In this example, we'll delete the thread. So now we know how to work with notes and comments. 16. Section 3.9 - Adding Hyperlinks: In this lesson, we'll learn how to insert hyperlinks into a spreadsheet. We can add hyperlinks to sheets within the same workbook or two completely separate files. We can also link to websites and email addresses. In the first example, we'll link to a sheet within this same file. From the Insert tab. Will go to the link button and select Insert Link. On the left side of the screen. We'll make sure we select Place in this document. An Excel displays. Different sheets within this file. Will be linking to the widgets are a spreadsheet. So we'll select that and click OK. On some computers, it may be necessary to hold down the Control key when you click this link. We could have created a link on this sheet that would take us back the original sheet. In the second example, we'll link to a different file. So we'll select the hyperlink text. Go back to the link button, and choose Insert link. On the left side of the screen, we will select existing file or a webpage and will choose a file at random. Here. Below this shows the link. And we'll click OK. Let's try out the link. You'll oftentimes get security warning. Offices concerned that there may be a virus in the file. But if it's a file that you created, you know, it's safe. So we can just click OK. Let's close that file. Next will be creating a link to a website. At work. This may be a link to a website that relates to your spreadsheet. In this example, we will just link to Google. After selecting the link text, will go to the link button and select Insert link. On the left-hand side of the screen, we'll choose existing file or web page. And in the address field will type www.google.com. We noticed that excel includes HTTP colon forward slash, forward slash in the address, which is fine. We'll accept that. And click OK. Now let's test out that link. We can close Google. And the final example will be a link to an email address. When we click this link, it will open up our default email client. After selecting the text, will go to the link button and select Insert link. On the left with a select email address. And we can include not only the email address, but also the subject line. We noticed that it includes mail to in addition to our email address, it's important that we leave that as is. If we were to delete that, it will not work correctly. I'm going to leave the subject field blank and click the OK button. We'll try out the link. And we see it opens up our email client. We can close that. So now we know how to insert hyperlinks in our spreadsheets. 17. Section 3.10 - Project 1 - 12-Month Calendar: In this project will create a twelv month calendar. So first, we need to create 12 sheets. We enlighten each sheet that have the same layout. So to save time, we'll select them all more. Hold down the Shift key and select Sheet one. With all 12 sheets selected, anything we do to one sheet will apply to all. First, we'll type the word month in cell A1. Eventually will replace this with the names of the individual months. But to start with, we will highlight A1 over the G1, Because each week will contain seven days. Instead of using merge and center, will go to the Alignments section. Click the down arrow. And where it says text alignment horizontal will change the setting from general to center cross selection. We can also change the formatting. I'll select aerial as the font and 28 as the font size. I can also include a fill color and borders will be filling in the days of the week. We'll start with Sunday in A3. Rule, our mouse over the lower right-hand corner of cell A3 and left drag that over to G3. Wednesday is truncated or a cutoff. So what we'll do is we'll change the width of our columns, but we're not going to change just the width of column D. We'd like each column to be the same width, like we see in a typical calendar. So we'll highlight Column a through column G. Let's roll to the vertical line in between any one of those columns. And where we see the column with adjustment cursor. Let's hold down the left mouse button and stretch sat. Now we'll apply basic formatting to the column headings. Will highlight a3 over the G3. And first, we'll center those words. Then we'll apply borders. And a fill color. Since there may be up to five weeks in any given month. Let's highlight rows four through eight. We'll apply borders to those cells. And now we'd like to change the height of those rows. So row over here to the left margin. When we see the row selection tool, we'll click and drag to highlight all five rows. Then will release the mouse button. And this time we'll look for the row height adjustment cursor. And we'll drag that down to adjust the size. At this point, we don't need the gridlines. So we'll go up to the View tab and D select that option. So now we have a basic template for each month. Let's go back to the home tab. Because we have all 12 sheets selected. If we click on any one, we see they're identical. Now will go in and individualize them. We'll start by right-clicking sheet one, choosing rename. And then repeat that for all the other sheets. Now let's change the generic title month to January. The one problem with this approach is when we press enter, excel interprets it as a date. Now if we don't want that to happen, here's a little trick. Anything that's preceded by an apostrophe will be treated as text. So this time I'll type apostrophe, January 2021. And we see that Excel treats it as text rather than as a date. Let's repeat that process. With other months. Encoded February. We can sell a1 and type apostrophe. February 2021. Now we return to the January spreadsheet. In January 2021. The first of the month is on a Friday. So we'll type in the number one. We noticed that the number one appears in the lower right-hand corner of the cell, we'd like that to appear in the upper left-hand corner. And if we plan ahead, we'd like that to be true for all 12 months. So here again, we will select all of our sheets. Let's hold down the Shift key and select December. Will highlight a for Dow, the G8, and will make two changes here. First, we'll align all of our numbers to the left. And directly above that will align all of our numbers to the top of the cell. It's important for us to de-select the other sheets at this point. To save us from having to type in each number individually, we can use a feature that we learned about in a previous lesson. We can fill in a pattern of numbers. To do that will roll down to the lower right-hand corner, that cell. And we'll look for the fill handle. Let's drag that to the right. Now by default, Excel is set up to copy numbers. We learned earlier that we can rule or mouse over the autofill options button and change the setting from copy cells to fill series. Sunday is obviously the third of the month. So we'll type in the number three, grab the corner, and drag over to column G. Now every time we do this, we need to go to the autofill options button and tell Excel to fill a series. But here's a little shortcut. When we do the next row. In A6, we'll start with the number ten. If we hold down the Control key. As we drag this across, this force is Excel to fill in a pattern immediately, which saves us a couple of steps. Now we'll go to A7, type in the 17th. Hold down the Control key and fill that across. To complete the month. We'll type 24 in cell A8. And drag that over. We notice that the numbers are appearing directly below each cell. Now we do have a problem here. Obviously, there are 31 days in January. So what we'll do is we'll grab the corner of g eight. Drag that down one row. We will delete the numbers that are there. And let's type in 31. Row nine is not the same height as the other rows. We could try to match it by eye. But here's a little trick to see the height of row. We can actually roll our mouse over the horizontal line that separates the two rows. And if we left click and hold down our mouse button, it's telling us that our row is 71 pixels high. There are several ways that we could match that. One method involves rolling down over the horizontal line between row 910. If we click and drag down the screen, we can match the number this way. So now we repeat the same basic process with the other months of the year. And we have an annual calendar. 18. Section 4.0 - Editing Spreadsheets: In this section we'll learn how to insert and delete columns, rows and sheets. Plus we'll cover some of those tricky edits that can cause problems if they're not done correctly. Will also talk about renaming sheets and changing their tab colors. And finally, we'll go beyond the basic cut and paste and copy and paste features to explore some of the unique options in Excel used for moving data around on a spreadsheet. And of course, more tips and tricks. 19. Section 4.1 - Inserting and Deleting Rows and Columns: In this lesson, we'll learn how to insert and delete columns and rows. The best way to insert a row is to rule directly over the row number. In this case, I'd like to insert a row above row 20. So I'll right-click on the row number and select, insert. To delete a row. I'll roll directly over the number 20. Right-click and delete. To insert multiple rows. I'll use my left mouse button to highlight the cells. Now, I'll roll over anyone in the room numbers. Right-click and choose insert. To delete multiple rows. I'll use my left mouse button to highlight the row headings. Let go of the left mouse button. Right-click any one of those row numbers and select, delete. It's similar if we're working with columns. If we'd like to insert a column in between column C and D, We would right-click on the letter D and select, Insert. And notice this pushes all the other column over to the right. To delete the column. We'll rule directly over the column letter, right-click and delete. And just as we inserted multiple rows, we can also insert multiple columns. With our left mouse button. Will select several columns. Release the left mouse button and Right-click any one of those column headings. And choose insert. To delete multiple columns. Will highlight with the left mouse button. Right-click any one of the column headings and choose Delete. I prefer that method because it's the quickest and easiest approach. If you ever right-clicked in an individual cell and chosen Insert. You've probably seen this dialogue box before. It takes a second to think about exactly what it is you want to accomplish. If I'm inserting a column, I have to select that. And click OK. If I was to right-click inside any cell and choose Delete, I'll see a similar dialog box. Again, I have to do a couple of extra steps here. I'd like to delete an entire column and click. Okay? So now we know how to insert and delete columns and rows. 20. Section 4.2 - Renaming Sheets and Changing Tab Colors: In this lesson, we'll learn how to rename sheets and change the tab colors. Now easy to remember which spreadsheet is which. But renaming our sheet tabs makes it easier and quicker to locate the sheet we're looking for. All we need to do is right-click the tab and select Rename. Then we type in the new name and click in any cell to complete the process. For example, let's right-click Sheet four. Will choose Rename. And let's call that balance sheet. And then click the cell to complete the renaming process. Will do the same thing was achieved three, we'll right-click it. And let's rename it. Profit and Loss. Click in any cell. Now we'll right-click on xi2 and rename that in voice. And rename sheet one time sheet. We can also color code the sheet tabs. Oftentimes is quicker to pick out a color than it is to pick out a word. Let's right-click the timesheet tan, and rule up to TAM color. And we'll select a color for that. At first, the color looks rather faded. But that's because it's the active sheet. If we select a different sheet, we'll see the true color. Let's repeat that process. On the other sheets. Will right-click the invoice tab, go to Tab Color and select a different color for that one. And do the same thing for Profit and Loss and balance sheet. So now will have an easier time spotting the sheet that we want. So now we know how to rename sheets and change the tab colors. 21. Section 4.3 - Moving Sheets: In this lesson, we'll learn how to move sheets. Sometimes we need to rearrange our sheets. Although we could right-click a tab, select Move, or Copy, choose where to place it, and click OK. That's too many steps. An easier way to move a sheet is to simply left-click the tab and then drag and drop it into position. Sometimes we need to move multiple sheets. In this example, we'd like to move invoice and balance sheet to the right of the schedule worksheet. That's easy. We'll select invoice, hold down the Control key, and select balance sheet. Then will release the control key and use the left mouse button to drag them into position. So now we know how to move sheets. 22. Section 4.4 - Moving Cell Contents: In this lesson, we'll learn about cut and paste, copy and paste, moving cells and inserting cut cells. You're probably already familiar with cut and paste and copy and paste. But just in case you're not. Here's a quick demonstration. Cut and paste is used for moving cells. We can highlight the cells we'd like to move. Then either right-click and choose cut. Or we can choose the cut button in the upper left part of the screen. Now we'll move our cursor to the cell where we'd like to paste, right-click again and choose Paste. So cut and paste move cells from one location to another. To copy and paste is very similar. We start by highlighting the cells, but copy and paste will duplicate the cells. Now that we've highlighted, we'll right-click and select Copy. Then we'll go to the cell where we'd like to paste the copy, right-click and select Paste. But Excel includes a couple of other features that you may not be familiar with. Let's demonstrate those. Another way to move cells involves highlighting them. Then rolling your mouse pointer over the green border around the highlighted cells. And when we see the move cursor, we simply hold down the left mouse button and drag the cells to the new position. Let's use that technique to move them back where they were originally. Another feature that's unique to Excel is insert cat cells. Let's say I'd like to take total and inserted in between average and highest. First, I'll left-click on row eight to select it. I'll follow that by right-clicking on the number eight and selecting cut. Then I'll right-click the number ten and choose the option insert cut cells. This can also be done with column. Will left-click on the letter H to select column H. Right-click and choose cut. And let's say we'd like to place that in-between extended price and sales tax. So we'll right-click the letter g and choose Insert cut cells. This also works with multiple rows and multiple columns. So now we know about cut and paste, copy and paste, feature and insert cat cells. 23. Section 4.5 - Clearing Cell Contents: In this lesson, we'll learn the difference between delete, backspace and clear cells. If we'd like to remove the contents of cells A1 through A4, the best option is the delete key. But let's undo that. If we reach poor the backspace key, that will only delete the contents of the first cell. If our cells also contain formatting, then the delete key is not the best choice. If we'd like to clear these cells completely. Pressing delete will only remove the contents. The formatting remains. To completely clear the cells. The best choice would be the clear option. When we select Clear all this will remove the contents and the formatting. Occasionally, you'll want to retain the contents, but remove the formatting. Clear is perfect for situations like that. We'll go back to the clear button and this time choose clear formats. So the contents remain, but the formatting has been removed. So now we know the difference between delete, backspace and clear cells. 24. Section 5.0 - Formulas & Functions: In this section, we'll get to work with basic formulas so we can add, subtract, multiply, divide, and calculate percentages. These skills can be used to build many different types of spreadsheets. In addition to learning about formulas, will also cover for popular functions. Sum, average, max, and min. Then will discuss common error messages and how to deal with them. And finally, we'll put all this to work in two different spreadsheet projects. 25. Section 5.1 - Using AutoSum: In this lesson, we'll learn how to use autosome. Let's say we'd like to total all our columns. Autosome is the easiest way to do that. We just highlight the cells we wish to total and include the row where we'd like to total to appear. In this case row eight. Now we either click Auto sum or use the keyboard shortcut all equals. So now we know how to use autosome. 26. Section 5.2 - Basic Formulas: In this lesson, we'll learn how to create basic formulas. In Excel. Formulas allow us to add, subtract, multiply, divide, and many other things. All formulas start with the equal sign. There are no exceptions to this. Most formulas rely on cell references or cell addresses. There are rare exceptions. But in general, we refer to addresses rather than actual numbers. Formulas use the following math operators. The plus sign to add, the minus sign to subtract the star or asterisk to multiply and the forward slash to divide. Let's learn how to create some of the most basic Excel formulas. In this example, we're working with two numbers. In cell A1. We have ten. N is cell B1, we have two. In the first exercise, we'd like to add those two numbers together, and we want the result to appear in C1. We know that a formula must start with the equal sign. And instead of using the values ten plus two, we will use the addresses where those values are located. To add those two numbers together. We would type in this formula. Once we move out of cell C1, will see this result. Now let's say we want to do a subtraction problem. We will use the same two numbers. We'd like to take ten and subtract two from it. Again, we know our formula must start with the equal sign. And in this example, we'll take address A1 and subtract address B1 from it. Once we move out of that cell, we will see the results. Next, we'll perform a multiplication problem. In this example, we'd like to multiply ten times two. Again, we follow the same syntax, but in this case we use the multiply sign. Remember that's the star or asterisk. Next, we'll do a division problem. In this case will take ten and divide it by two. We follow the same basic format, but we substitute the forward slash, which represents divide ten divided by two is five. Excel also allows us to calculate percentages. Let's say we have $100 and we need to figure out what 7% of a $100 is. On a calculator, we would multiply 100 times 7%. And we do something similar in Excel. Notice that we multiply a1 times b1. And of course, 7% of a $100 is $7. So there are a few basic Excel formulas will be using those concepts in the different projects that we create in this course. 27. Section 5.3 - Basic Functions: In this lesson, we'll learn how to create some basic functions. There are over 300 functions in Excel, ranging from simple to very complex. Here we'll learn how to use average, max and min. Earlier when we used autosome, we were actually using the sum function. Now we're going to learn how to create a few basic functions. Will click in cell B9. Here we'd like to see the average selling price for our products. To create. This function will go up to the autosome button, but this time we'll click the drop down arrow just to the right of it. And we'll choose the average function. Now Excel latches on to the nearest number, and this is actually an error. We want Excel to average cells B14 through B6. So with the mouse, we'll highlight those. Now it's very important after you've done that to press the Enter key. As we can see, the average selling price was $75. We'd like to see averages for all the other columns as well. So a quick shortcut is to roll over the lower right-hand corner of cell B9. Look for the fill handle and Excel will correctly calculate the average for all of those columns. You may notice a formatting issue in the quantity sold column. It's displayed as $24. That happened because when we filled across the row, Excel filled everything, the function and the formatting. Lets select cell E9 and will change from currency formatting to General. And that will fix the problem. Now for the highest and lowest values, I'm using different terminology than Excel uses. I'm thinking in terms of the highest product price or the lowest product price, but Excel uses the terms maximum or minimum. The abbreviate that to max or min. So where I'm thinking of the highest price, Excel is thinking of the maximum value. So in b ten, when I go up to the autosome button and click the drop-down arrow, I don't see the word highest. I'll be using the function called max. Again, Excel has highlighted the wrong cells. It's important for me to correct that. I want to know the highest value in cells before through B6. And remember I mentioned, it's important to press the Enter key after doing that. So we can see that Excel correctly indicates that $100 is the highest or maximum value. To copy that across will go to the fill handle. Again in column E, we have a slight formatting issue will change from currency formatting to general. And now let's figure out the lowest price or minimum value. Highlight the correct cells and press Enter will fill it across. And correct the formatting issue for quantity sold. So now we know how to use the average max and min functions. 28. Section 5.4 - Copying Formulas and Functions: In this lesson, we'll learn how to copy formulas and functions. In Excel. We only need to enter a formula or a function once. Then we can use the fill handle to copy the formula or function to additional cells. Here's an example. In cell D4, we'd like to calculate the selling price. So we'll enter the following formula. Before. Product price minus the cash discount. In C4. In the lower right-hand corner of cell D4, we see a little green box. And if we roll our mouse pointer directly over that, the mouse pointer changes to a small black plus sine, which is called the fill handle. At this point, we can double-click to fill the formula down the column. And Excel stops when it reaches the first blank row. Let's do that again with the extended price. The extended price will be the selling price times the quantity sold. In other words, equals D four times E4. And we'll double-click the corner again. So that's a quick way to fill in a number of formulas. Now, it's true that we could have dragged with the mouse to fill in that formula. We'll try that approach in column H. Here, will take the extended price in F four and multiply it times the tax rate of 7.75%. Instead of double-clicking, we'll click and drag with the mouse. Although you can click and drag to fill in a formula, I suggest double-clicking withdrew mouse. Dragging with the mouse is fine for small spreadsheets. But double-clicking is a much better method to use when working with large spreadsheets. Here's an example. In column m. We need to calculate the net profit. We'll type in equals J2, the subtotal minus L2. The sales commission that we paid. If we attempt to click and drag with this, we quickly see it's not. A very practical approach. Will undo that. Here's an example where double-clicking is a much better approach. Will roll over the lower right-hand corner of cell M2. When we see the fill handle, we'll double-click. An Excel has filled in that column from top to bottom, all the way down to row 841. So enlarge spreadsheet, we should definitely use the double-click method. We can also copy functions. In this example, we'll copy the sum, average, max and min functions across the row. In cell B8. We'll add autosome and press the Enter key. Now we'd like to have totals for each column. So we'll use the fill handle to copy that function over to Jay. And we'll do the same thing with the average max and min functions. Starting in B9. We'll click the drop-down arrow to the right of autosome and select average. We'll show Excel which sells. We'd like to average and press enter. And then we can fill that across the row. And we'll quickly do the same for max and min. Here's a handy tip. We could have selected all for functions and filled them across the row at the same time. Let me demonstrate. If we select B8 down to be 11. We noticed the green corner in the lower right-hand corner of cell B11. Once we see the fill handle, we can fill that across two column j. So now we know how to copy formulas and functions. 29. Section 5.5 - Fixing Common Formula Errors: In this lesson, we'll learn how to fix some of the most common formula errors. If we forget to include the equal sign in our formula, we'll see this result. Excel doesn't recognize it as a formula. And any formulas that reference this cell will display the value error. To fix this will add the equals sign. And it's a good idea to press Enter after making any changes to a formula. Another common error involves reversing the column letter n row number in a cell address. Excel will offer to correct this error. All we need to do is accept the suggestion by clicking yes. When typing in an addition formula, we may forget to hold down the Shift key when entering the plus sign. This will result in either a true or false Error message. We can fix this by replacing the equal sign with the addition sign and press enter. When we're done. When typing in a formula, we may accidentally enter a value instead of a cell address. If we copy that formula down the column, all the results will be incorrect. Here's an example. I should type D for, which references the address where the hours are located. But by accident, I entered in the hours rather than the address where those hours are located. If I fill this formula down the column, all employees will show 32 hours. To correct this. I'll remove the value and replace it with the address where that value is located in this example. So d4. Now, each formula will include the hours in column D, which is correct. Another mistake that is easy to make involves referring to a cell and rows containing text rather than a value. Here's an example. If I multiply C4 times D3, D3 is in error, I can't multiply a value times text. So when I move out of that cell, I get the value error. Excel was expecting a value, and instead, I pointed to a cell containing text. It's simple to correct this. I go back to the cell with the error. And this time I'll include the correct address, D4. And when I press Enter, that takes care of the problem. When a cell contains a green corner. There's several possible explanations. We may have been inconsistent in our formula, or perhaps our formula doesn't include nearby cells. In the first example, we see a green corner in cell C7. If we roll over that green corner and left-click it, we see an error sign. When we point to the error of signed, it says the formula in this cell differs from the formulas in this area of the spreadsheet. If we look at the formula bar, we see that this formula is written equals E seven minus seven minus g seven minus H7. Minus seven, minus j seven. It is the correct result. But that formula differs from the cells nearby. Let's click on the cell directly above. In this formula, it was written equals E6 minus K6, and other words, gross pay minus total taxes. If we click in the cell directly below L seven, it was written in the same way. Gross pay minus total taxes. So in Column L, all the formulas written one way except that one in cell C7. And that's why we're seeing the green corner. We were inconsistent when we entered that formula. To eliminate the error. We can click in that cell role over the error sign and select the drop-down arrow. And will simply copy the formula from above. And that will solve the problem. In a second example, we'll encounter green corners. When we add new records to the spreadsheet. We'll right-click on row four and insert. A new row. Will go to the Format button, the Insert Options button. And we'd like to format that new row the same as the row below. And let's see what happens when we enter in a new record. Watch what happens in cell C42. See how the green corner appears. If we roll over that cell and select the error message. Excel is telling us that this formula omits adjacent cells. If we look in the formula bar, we see how it reads equals sum c5 through C 13. Well, this $17 that we just entered is in cell C4. So it was not included in the autosome. If we select update formula to include cells, notice how the $104 goes up to $121. So there are two examples where we might encounter that green corner. And that's how we can solve that issue. In column L, We see pound signs or hash marks. They indicate that the numbers in those cells are too wide to be displayed. To fix this, all we need to do is stretch. The column. Another error that we come across involves typing over and wiping out a formula. Before typing over any value in a spreadsheet, we should first check the formula bar to see if it's just a value or is it the result of a formula? We should never replace a number that so the result of a formula. If we do, the formula will be wiped out and Excel can no longer calculate the correct results. Here's an example. The $15 in cell C4 is just a value. We can see that by looking at the formula bar up above. The same goes for the 32 hours in cell D4. It would be safe to type over those values. For example, let's say Wilson gets arrays and is now earning $16 an hour. We noticed that the formulas all updated. The same thing is true for cell D4. If we replace that number with something else, the formulas still work. But it would be a mistake for us to replace the $480 that we see in cell E4. If we look at the formula bar up above column D and E, we see that it's not just the number four AT that number was calculated by a formula. Let's see what happens if we accidentally type over the $480. At first, everything looks fine. But see what happens if we change these values in column C and D. Notice nothing from column E on over to column L adjusts because we wiped out the calculating feature. Let's undo that. Now, for 80 is the result of a formula. So before replacing any values in a spreadsheet, click in that cell. Check the formula bar. If it's the result of a formula, do not type over it. If you do, Excel will be unable to perform calculations in that cell. If we delete cells referenced in a formula, we will see the href error. This means Excel is unable to reference the cell addresses in the formula. Here's an example. Let's say we delete column H. State withholding is one of the taxes. And when we delete this, we encountered several ref, errors. So in other words, there are formulas now that cannot locate the cells that are referenced in the formulas. Let's say we click in cell J4. And if we look in the formula bar, we can see what happened. So we need to be careful about deleting columns. If there are formulas that reference those cells, you will see ref errors like this. Let's say we need that column back. We can always undo. Now if we do need to delete that column, that means we would have to edit the formulas so they no longer look for that cell address. This time when we delete the column, will go over and edit the formulas. So they no longer reference the missing column. Press enter. And we can now fill that formula down and correct the mistake. So now we know how to correct some common formula errors. 30. Section 5.6 - Project 2 - Track Sales: This project will track sales for a retail store. Will be tracking products, prices, quantity, sold, taxes, grand total, and average selling price. The name of our company is widgets or us. We'll skip a row between the title and the column headings. Each one of our column headings will contain two words. In a previous lesson, we learned how to use the wrap text feature. The advantage using that is we can fit more columns on a page that way. Let's highlight a3 over the J3. And in the alignment section, we'll choose the wrap text feature. I like to apply the wrap text feature before typing because that way my column headings will look correct immediately. In A3 will be tracking product name. We can earn or tab over to B3. Next, we'll track product price. Tab. We'll be offering our customers a cash discount to enter that in. So S3. And if we take the product price and subtract the cash discount, we end up with the selling price will have a calculation in column D to figure that out. We'd also like to track the quantity sold. And in column F will be using a term, extended price, which can be thought of as the subtotal. Extended price is basically the selling price of an item times the quantity sold. In column G, we are tracking sales tax. Because I live in California in the United States. I will be typing in ca, short for California. Owned by sales tax. For you it may be VAT, value added tax. Or perhaps in your state there is no tax. In column H will track the shipping charge. In I. Three will be adding up the grand total. And finally, in J3, we will calculate the average price. Now we'll apply some formatting to our spreadsheet. First, let's highlight the title in cell A1 over to J1. And we learned earlier that instead of using merge and center, it's usually better to use central across selection. So we'll click the Dialog Launcher button, little down arrow in the lower right-hand corner of the alignment section. And we'll change the setting from general to center across selection. And click OK. While we still have the cell selected, will increase the font size. Apply all borders, and include a fill color. Now let's format our column headings. We can highlight A3 to J3. Will apply all borders. A fill color, and center. The text to begin with will be selling three sizes of widgets, small, medium, and large. So we'll enter the names and column a. It's a good idea to place borders around any cells that contain data. So we'll be highlighting a four through A6 and on over to J4 through J6. Let's apply the all borders feature. And we'd like to total each column. So we'll be adding a row titled totals. Because each column will have a total and it will appear on row eight, would be a good idea to highlight those cells and apply borders. You'll notice that I skipped row seven. In a previous lesson I mentioned that it's a good idea to separate your last row of data from your totals row. Otherwise, when we sort things that totals row can cause problems. Now that we have the basic layout for our spreadsheet, we don't really need the gridlines. So at this point will go up to the View tab and uncheck the option to show the grid lines. It looks a little cleaner and more professional. Most of our columns will include dollar amounts, with the exception of column E, quantity sold. So we'll live column E formatted for general. But let's highlight before through d eight. And we'll switch from general to currency formatting. Now we'll highlight F4 through J8 and do the same thing. First, let's enter the prices for our products. Because we preformatted for currency, all we need to do is to type in the values. And as soon as we move out of that cell, Excel applies the currency formatting, which saves us some typing. The medium widget cells for $75 and the large cells for a 100 will be offering a cash discount. In S4. If a customer pays cash for their small widget, will give them $10 off. For a medium, $20 OFF, and $30 for the large. Now we need a formula to calculate the selling price. In the lesson on formulas, we learned that we normally use cell addresses rather than actual numbers or values. The selling price would be the product price minus the cash discount. So we would enter it this way. We'd start with the equal sign. The first product price is in cell B4. And we'd like to subtract the cash discount, which is in cell C4. To save us from having to enter formulas for medium and large widgets, we can fill or copy this formula down the column. If we roll over the lower right-hand corner of cell D4, we see the small plus sine, which is called the fill handle. And at this point we can either click and drag or I prefer to double-click. And that will fill the formula down that column. If we looked at the selling prices, we can see that Excel did the math correctly. Let's take a look at what's happening behind the scenes. If we click on the $40, we see the original formula in the formula bar up above. B four minus C for when we fill in a formula, Excel automatically updates the row numbers for us. If we click on the fifty-five dollars, we see that the addresses that have been updated to b5 minus S5. And the same thing happened here in the cell where the $70 appears. Now it's B6 minus C6. Even if we had 10 thousand products, Excel would correctly update that formula for all 10 thousand rows. In column E will be tracking the quantity sold. So in this case, there won't be a calculation. Let's say we sold 17 small, 20 for medium and large. To calculate the extended price, we do need a formula. I mentioned a few minutes ago that extended price is like subtotal. In this case, the selling price times the quantity sold. So we start with the equal sign. The selling price is in cell D4. And we'll multiply that using this star or asterisk times the quantity in IE four. To save time will fill the formula down. Next we'll be calculating the sales tax or value-added tax. Where I live. It's 7.75%. It will be 7.75% based on the extended price. So the formula in this case will be equals f for the extended price times the tax rate. In this example, I'll be typing the tax rate directly into the formula. If you use this method, make sure you include the decimal point and the percent sign. Our role over the lower right-hand corner. Look for the fill handle and double-click. The shipping charge in column H will just be the amount that we charge to ship out that number of widgets. Let's say shipping was $56 for the small widgets, $85 for the medium. And 93 for the large. In other words, it cost $56 to ship out 17 small widgets. Now we will calculate the grand total. For the small widget. The grand total would be the extended price plus the tax plus the shipping charge. There are several ways we could write this formula. The example I'll show you right now will be equals F4 plus g four plus h4. Double-click the corner. To calculate the average selling price, we need to take the grand total that we collected and divide that number by the quantity that we sold. In Excel terms. That would be I for the grand total, divided by E for the quantity sold. And double-click the corner to fill it down. We'd like to total all of our columns. The quickest easiest way to do that is to highlight all the cells using the big white plus sign, the highlighter. And will include the row where we'd like to see the total row eight. We can either click the autosome button in the upper right, or we can use the keyboard shortcut. All equals. To complete our spreadsheet project. Will calculate the average, highest and the lowest values in each column. To do that, we'll be using some simple functions. In cell A9 will enter the word average. Directly below that. Will enter the word highest and lowest. Since we expect to see values and all the cells will highlight them and apply borders. Now we're going to learn how to create a few basic functions. There are over 300 functions in Excel, and some are quite complex. But in this course will be covering just a few basic ones. Now when we totaled each column, we actually used a function. If we click in cell B8 and look in the formula bar, you can see that it says equals some b for colon B7. So basically excel summed up cells before through B7. The average function works much the same way. We'll click in cell B9. Here we'd like to see the average selling price for our products. To create. This function will go up to the autosome button, but this time we'll click the drop down arrow just to the right of it. And we'll choose the average function. Now Excel latches on to the nearest number, and this is actually an error. We want Excel to average cells before through B6. So with the mouse will highlight those. Now it's very important after you've done that to press the Enter key. As we can see, the average selling price was $75. We'd like to see averages for all the other columns as well. So a quick shortcut is to roll over the lower right-hand corner of cell B9. Look for the fill handle and drag it. And Excel will correctly calculate the average for all of those columns. You may notice a formatting issue in the quantity sold column. It's displayed as $24, which is incorrect. That happened because when we filled across the row, Excel filled everything, the function and the formatting. Lets select cell E9 and will change from currency formatting to General. And that will fix the problem. Now for the highest and lowest values, I'm using different terminology than Excel uses. I'm thinking in terms of the highest product price or the lowest product price. But Excel uses the terms maximum or minimum. The abbreviate that to max or min. So where I'm thinking of the highest price, Excel is thinking of the maximum value. So in b ten, when I go up to the autosome button and click the drop-down arrow, I don't see the word highest. I'll be using the function called max. Again, Excel has highlighted the wrong cells. It's important for me to correct that. I want to know the highest value in cells before through B6. And remember I mentioned, it's important to press the Enter key. After doing that. So we can see that Excel correctly indicates that $100 is the highest or maximum value. To copy that across, we'll go to the fill handle. Again in column E, we have a slight formatting issue. Will change from currency formatting to General. And now let's figure out the lowest price or minimum value. Highlight the correct cells and press enter. Fill it across and correct the formatting issue for quantity sold. So there's an example of a spreadsheet and then incorporates many of the techniques that we covered in individual lessons. To complete this, we could go down to the sheet tab, right-click it, will rename it. And to complete renaming, it will click in an empty cell. And we can also change the tab color. We'll right-click that. Select Tab Color and choose our favorite color. So there's another project, tracking sales for a store. 31. Section 5.7 - Project 3 - Weekly Payroll: In this project, we'll be tracking payroll for employees at a bank. The name of our bank will go in cell A1. First National Bank. We'll skip down to row three. And column headings will be tracking employee name, hourly rate, gross pay, taxes. Each column heading will have two words. So we'll apply Wrap Text before we type in any column headings. Let's highlight a three over two k three. And select Wrap Text. In A3 will be tracking employee number. And then we'll move to the right to cell B3. The word employee is broken up across two lines. To fix that will go to the column adjustment cursor. And drag to the right. In B3 will be tracking employee name. We encounter the same problem here. So we'll stretch that cone to adjust. Next will be tracking hourly rate. In column D, will track hours worked. And if we multiply hourly rate times hours, word will end up with gross pay. The gross pay heading didn't wrap because it didn't contain enough characters. Sometimes it's necessary to force the rapping. To do that. We'll double-click in the cell and make sure our cursor is blinking just to the left of the word that we'd like to rap. And on the keyboard will press alt, enter one time. This will manually force the text to wrap. Now we'll move to F3. And here we'll begin subtracting the different payroll taxes. This example is from the state of California. In the United States. Obviously your tax situation will be different. The first tax will be withholding is combed federal withholding. When we move to sell G3. Pitch obvious, we need to stretch Column F. Next we'll be subtracting Social Security. In column H. State withholding. We can stretch column H. Next we'll enter upon unemployment insurance. We can stretch column H. In column j will enter disability insurance. And finally in column K will be tracking net pay. After all the taxes are withheld. Net pay of course did not wrap. So will manually rapid. Let's double-click that cell. Makes sure the cursor is to the left of the word pay. And press alt, enter. To wrap it. We notice that it appears there's a blank row above each column heading. Rows three is a little taller than it should be. We can go over here to the horizontal line between row 34. Click hold and drag, and adjust the height of that row. Now we will apply some basic formatting to our spreadsheet. We'll start by highlighting a one over k. One will go to the Alignments section. Click the little drop down arrow. And we'll change the text alignment from general to central across selection. And click OK. While the cells are still highlighted. It will change the font size and all mortars and a fill color. Now let's format our column headings. We can highlight a3, k3, apply all borders, a fill color. And we'll center the text. To start with. We won't enter any employee numbers. First, we'll go to cell B4 and begin entering the last names of our employees. Let's highlight a four through eight on over to K4 through. All of these cells will contain data. And it's a good idea to place borders around those cells to make it easier to read. Next, we'll add a total row. Just below that. We'll be tracking the average. Also the highest and the lowest values. So for each column will know the total, the average, the highest and lowest value. Let's highlight a ten through a 13 and over to column K. And we'll add borders to all those cells. Now that we have the basic layout, we can hide the grid lines. Will head up to the View tab and select the option called gridlines, and then return to the home tab. Most of our columns will contain currency, with the exception of column D. Hours worked. Let's highlight C4 to see 13. Then we'll hold down the Control key and highlight y4 through K 13. And we'll change the formatting from general to currency. Between Column a and B, we have this vertical line. We don't really need that. So I'd like to show you how to remove a border on one side of a cell. I'm going to highlight a ten through 13. And I'd like to remove the border on the right side of those cells. To do that, I'll click the little drop-down arrow to the right of all borders. And then roll down to the bottom of the menu where it says more borders. In the center of this dialogue box, we see that borders that are currently applied to those cells. And easy way to remove the right border would be to select it. Then click OK. And now we'll click in any cell to de-select. It's a minor change, but I think it looks a little better that way. In a previous lesson, we learned how to enter a number and then fill in a pattern. In this example, our first employee is employee number one hundred ten hundred. Our second employee is 100001. Third 1 is 103, et cetera. To save us from having to type in each number individually, will fill or copy a series. If we hold down the Control key, this force is Excel to fill in a series of values. As we hold down control. Drag the mouse down to sell eight, and then release the mouse. Now will enter in hourly rates. We're creating a weekly payroll here. So let's include the hours are employees work this week. Now we'll start creating formulas to calculate the gross. The taxes, and net pay. Grooms pay would be the hourly rate times the hours worked. So we'll start with the equal sign. The first hourly rate is in cell C4. And we multiply using the star or asterisk, will be multiplying the hourly rate times hours worked. And that is located in cell D4. In earlier lessons, we learned that we can fill in a series of formulas by rolling the mouse over the lower right-hand corner of the cell until we see the fill handle. That small plus sign. If we double-click, Excel will fill that formula down the column. Columns f, g, h, i, and j are all taxes that will be withheld from gross pay. In this example, they'll be a percentage of gross pay. For federal withholding will enter the following formula. Equals E for gross pay. A tax rate of 15%. Double-click to fill that down. So security will be 6.2%. So we'll type equals e four times 6.2%. And double-click to fill that down. I should mention that these are not official tax rates. We're just making up percentages for this exercise. For State withholding. We'll enter equals E for gross pay times 7% and fill it down. For unemployment insurance will type equals e four times 1.8%. And for disability insurance, 2.3%. So equals e four times 2.3%. For the last column, net pay. We need to come up with a formula that will take gross pay and subtract all the taxes. There are two ways that we could approach this formula. Let me show you a method number one now. So net will be gross pay in E four. Minus federal withholding in F4 minus Social Security in G4, minus h4, State withholding, minus i4, unemployment, and minus disability in J4. Again, this is only one method for approaching this problem. I'll show you another way of approaching this in a few minutes. Now we'll roll over the lower right-hand corner of cell K4. Look for the fill handle and double-click. Now we'd like to see totals for each column. Will highlight S4 over two K T. So all the numbers we'd like to total, including the row where we'd like to total to appear. And we'll click the autosome button. We could have also used the keyboard shortcut, all equals. Next, we'll use a couple of very simple functions to calculate the average highest and lowest values for each column. In C 11. We'll go up to the drop down arrow just to the right of autosome. And select average will correct that by highlighting C4 down to see. And it's very important that we press enter. To complete that operation. We can select that cell role to the lower right hand corner and with the fill handle, copy that over to column k. Now we'll figure out the highest value. And you may recall if you did the previous exercise, that Excel has a function called max, which is short for maximum. And it will determine the maximum or highest value. So starting and C 12 will go up to the autosome button and click the drop down arrow to the right of that. Select Max will highlight the cells we'd like Excel to look at. That'll be C4 down to C8. And press Enter. Let's fill that across the column k. And now we'll figure out the lowest or minimum value for each column. Starting in C 13 will go up to the drop down arrow to the right of Auto Sum and select min, which is short for minimum. Let's highlight the cells we want Excel to work on. Press enter and fill it across. Now we do have a minor formatting issue in column D. When we filled average, max and min over from column C, Excel copied over the currency formatting. Obviously hours worked should not be formatted for currency. That's a simple fix. We can highlight those three cells and change to general format. To finish our project will rename the sheet. Let's right-click sheet one. Select Rename. And we'll call this first national bank. Click in a blank cell to complete renaming it. And will also change the tab color. We'll right-click the tab, select Tab Color and choose a color. So here's an example of using Excel to create a basic payroll spreadsheet. 32. Section 6.0 - Printing Issues: In this segment of the course, we'll learn how to improve the printed appearance of our spreadsheets. Will cover adjusting margins, centering, adding page numbers, headers and footers and more. Will also learn how to make column and row headings print on each page. 33. Section 6.1 - Fixing Common Printing Issues: In this lesson, we'll learn how to print a spreadsheet. It's always a good idea to preview your spreadsheet before sending it off to the printer. To do that, we go up to the File tab in the upper left and select Print. Looking at our spreadsheet, it's clear that several columns are missing. Down in the lower left-hand corner. We see that it tells us, we're looking at page one of two. If we click the right arrow, we see three columns would print out on page two. Obviously, this would not look very good. So we'd like to fix that. On the left-hand side of the screen. The first thing we'll try is changing from portrait to landscape orientation. Portrait indicates the page is taller than it is wide. Landscape, the page is wider than it is tall. Let's try that setting. We can see that's a definite improvement. It does appear that are right margin is slightly wider than the left. To fix that, we can go to the normal margins option over here on the left. Roll down to the bottom of the menu where it says custom margins. And instead of changing these values here, the easy way to center a page is to choose this option center on-page horizontally. When we click OK, our problems are solved. There are a few other minor issues that we may want to change. For example, the employee numbers are right next to the employee names. This is because numbers aligned to the right and text aligns to the left. That's an issue we can't fix here. We will have to go up to the arrow in the upper left-hand corner. And we're going to highlight our numbers in a four through eight and center them. We might choose to do the same thing with hours worked. We can highlight D4 down to d 13 and center those. We also notice that the average is 34.8. Now we may be okay with that, but perhaps we'd like to simplify that number and rounded off. We can always go up to the Decrease Decimal button, and that will round it up to 35. Now let's take one more look. In print preview. We can see that the employee numbers are not crowding. The employee names. Also, hours worked are centered within that column and we're ready to print this spreadsheet. Now let's take a look at another example. We'll take a look at this spreadsheet in print preview and see if it needs any work. So we'll click File Print. We have the same problem we had with the previous example. At least one column is cutoff. Let's change from portrait orientation to landscape. Everything now fits on the page, but the spreadsheet is a bit too small. To scale that up. We'll go over to the no scaling option. The settings that we're seeing all mentioned shrink. In other words, they'll take large spreadsheets and attempt to fit them on one page. We need to do just the opposite. We need to make this spreadsheet larger so it fills out the page. To do that, we'll go down to custom scaling options. And we can see that currently the scaling is at 100%. Let's try 130%. And click the OK button. Now that fills out the page and looks much better. We still have a slightly wider margin over here on the right side. So let's go to normal margins. Select custom margins at the bottom of the menu. And just like we did with the previous example, we will select center on page horizontally. I generally don't select the option to center on-page vertically unless it's a very large spreadsheet. The only thing we might change is to center the numbers in the quantity sold Com. Let's close out a print preview. Will highlight those cells and select center. Incidentally, you'll notice that there are dashed lines indicating the edge of the page. Those lines only appear once you've gone to print preview. If we were to save this file, close it, and reopen it, we would not see those lines. Let's take one last quick look in print preview and see what we think. You may notice that some of the borders around the cells appear thicker than other borders. That is not really an issue. Don't worry about that. It's basically a problem with our monitor our monitors having trouble displaying those lines correctly. If we were to send this off to the printer right now, the lines would be if we uniform width. So print preview is pretty accurate, but it's not 100%. Let's close this one. And we'll take a look at another example. Let's see how our calendar would print out. Who go to file and select print. This would most likely look better if we change to landscaped view. So that's the first thing we'll try. Now we need to scale up our calendar so it fills out the entire page. Let's go down and select no scaling. And then custom scaling options. I'll try a 130% to start. Sometimes you have to experiment. So I think this could be a bit larger. I'll go back to custom scaling, custom scaling options. And I'll try a 140% and let's go back one more time. And let's see if a 150% work. That's a good fit. The right margin does look slightly wider than the left. So I'm gonna go in and change that. Now because this calendar almost fills out the entire page. I'm also going to center it vertically. So I'll go down to custom margins and select center on page horizontally and vertically. That should look pretty good. And then click OK. So here are some examples of how you can print preview is spreadsheet before you send it off to the printer and make minor adjustments to improve the appearance of your spreadsheet. 34. Section 6.2 - Adding Headers, Footers, & Page Numbers: In this lesson, we'll learn how to add headers and footers to a large spreadsheet. Let's go up to the File tab. And first of all, we'll print preview our spreadsheet. At the bottom of the screen. We see it would take 80 pages to print this spreadsheet. Let's say we'd like to fit that onto fewer pages. If we go into where it says no scaling over here on the left, fit sheet on one page would be a bad choice because it would shrink what is now on 80 pages down to a single page. And of course, it would be completely unreadable. The text would be too small to fit all columns on one page could work. We'll take a look at that in just a second. Fit all rows on one-page. This spreadsheet has over 800 rows of data. Clearly, that would also be a bad choice. So how can we reduce the number of pages this requires while still keeping it readable? Well, first, let's try switching to landscaped view. We see this doesn't help much. We reduced it down to 78 pages, but that's not much of an improvement. But if we look at the columns, we noticed that most of the columns are wider than you really need to be. So we're going to try one of the options we just looked at a moment ago. We'll go back to where it says no scaling. And let's see what happens if we choose fit all columns on one page. And just a warning, this will make the text quite small. So this is definitely a trade off. It's going to be a little harder to read. But you'll notice that we now fit all those records on a total of 13 pages. Now the main part of this lesson is about headers and footers. Let's say we were to print this out. And the 13 pages get separated. Trying to put them back together in the correct order would be difficult because these records really are not sorted. It's just a series of random rows of data. And it hasn't been sorted by customer or by date, by company. So it would be very difficult to put those pages back in the original order. So the first thing we're going to learn how to do is add page numbers. Over here on the left-hand side of the screen, we'll click the hyperlink call Page Setup. In the upper left will select the tab, header and footer. As I'm sure you know, a header appears at the top or the head of the page and a footer appears at the bottom or the foot of the page. It's pretty common for page numbers to appear at the bottom of the page. So we'll select footer. Now there are quite a few different options available here. And what's interesting is that some of these have actually picked up the name of the file and the name of the sheet tab. For example. This one recognizes that the name of the file is large spreadsheet. This actually displays a path to where that file is located. The first photo that we'll try is the one called Page one of question mark. So in this example, it would be page one of 13, Page two of 13, et cetera. And they give us a preview. To accept that, we'll click OK. At the bottom of the screen in very small type. It says page one of 13. And if we were to move onto a next page, Excel automatically inserts the correct page number as we do that. So this might be a good solution for this particular spreadsheet. Now let's explore some other headers and footers will go back to the page setup link and click the header photo tab. You may have seen spreadsheets or even word documents where they placed the path at the bottom of this sheet in the footer. In other words, they show where the file is located and the name of that file. In our example, this is the path to this file. The reason why you might want to include that is maybe somebody's looking at the printed copy of this and we'd like to access that file. This will tell them what drive and what folder, and even the name of that file. We'll click OK. And that's the path. Now we'll learn how to create a custom footer. We'll go back and click Page Setup. Select the header footer tab. And to start with, we will select None. To build our own custom footer. Of course, we'll select the button called custom footer. And we can place things in the left, center, and right sections. We can add text, or we can use these ten buttons up above to build our custom footer. For example, let's say I'd like to include some text. Now if I'd like to format that, I can highlight it. And this first button on the left with the big capital a allows us to format. So these are pretty standard options. We can bold it, increase the font size, change the font color, and so forth. And click OK. When we're done. Let's click OK and then click on custom porter once again will delete that. This time, will click in the center section. The next two buttons allow us to indicate the current page and the total number of pages. Earlier we use the example where it was page one of 13, Page two of 13. Basically, we're going to build that from scratch. To do that, we'll type the word page followed by a space. And we'd like Excel to insert the current page number. To do that, we'll click this button. And Excel adds a field code. We're going to follow that with a space, type in the word of and add one more space. Now we'd like Excel to indicate the total number of pages in the spreadsheet. So for that, we'll click the second button. It looks a little odd, but basically we're saying Page one of 13. To click OK. And click OK again. We can see at the bottom center our custom footer. Let's go back to page setup again. Header footer, custom footer. And we'll delete what we just created a moment ago. This time we'll click in the right section. And the next two buttons we use will insert the date and time. So we'll click on Insert date. Again, that's a field code. Will press spacebar to put a space between the date and the time. And then click. The time button. So this will include the current date and time. Click OK. And here's a preview. Okay, we'll go back to page setup, header, footer, and custom quarter. Let's remove that. The next three buttons basically describes the path to where the file is located. The second one, search the name of the file. And the third one, the name of the sheet tab. We'll click over here in the left section. We actually did this one earlier. This shows the long path that drive the folder and the file name. So we'll select that and click OK. So here's the path to where that file is located. Will go up the custom footer. Remove that. The second button in that group will insert just the filename. We'll click OK. And as you can see, the name of the file was large spreadsheet. We'll go back to custom footer, delete that. And the last one in that group of three will insert the sheet tab, which was sales data. Will go back the custom footer and delete that. Before I show you the last two buttons, I'm actually going to close out of the custom footer and instead go over to where it says custom header. You'll notice that the window is identical. So everything that we just learned applies here. The only difference is it will appear at the top of the page. The example I'd like to show you involves a logo. Let's say we'd like to insert the company logo in the header. So we'll click this button. Let's do a Bing image search. And we'll search for a logo. Will just select this first one and click Insert. Now the one problem is most images that we insert are very large. So what I just did is I highlighted the item. And now I'm gonna go over here to format. And this shows that the image currently is about four inches tall, which is much too big. I'm going to reduce that to about half an inch. Even that might be too large. I'm going to try four tenths of an inch. Click OK. Okay. And okay, one more time. So we might adjust that. It could stand to be a bit larger. So we'll go back to page Senate, header, footer, custom header. And let's click Format Picture. And I'm gonna try making that, oh, about seven tenths of an edge. Click OK. And there we go. So now we know how to create custom headers and footers. 35. Section 6.3 - Repeat Headings on All Printed Pages: In this lesson, we'll learn how to make column and row headings appear on all printed pages. When printing multi-page spreadsheets, it's a good idea to repeat column or row headings on each page. Otherwise, users must go back to page one to see the column or row headings. In this example, we'd like to repeat our column headings located in row one. To do that, we'll select the page layout tab. Then we'll click the Print Titles button. The easiest way to select row one is to click once inside it. This tells Excel we'd like that row to print on every page. Instead of clicking OK, we'll select Print Preview to see if it worked. We see the column headings on page one. And if we scroll to other pages within our spreadsheet, are column headings appear on them as well. So now we know how to print column and row headings on all pages. 36. Section 7.0 - Charts: In this part of the course will cover charts. We'll learn some handy keyboard shortcuts for creating charts and selecting the data to be included in charts. Will also get a chance to create a column line pi n bar charts and learn how to format them. 37. Section 7.1 - Keyboard Shortcuts for Charts: In this lesson, we'll learn some useful keyboard shortcuts for working with charts. You may already be familiar with the Control key. In many programs, we use it to select multiple items. And it works much the same way in Excel when we're selecting data to include in a chart. In this example, we want to create a chart that compares our employee's gross and net pay. First, we need to include their names. So we'll highlight B30 through B11. Next, we'll hold down the Control key and highlight the gross pays in E3 through E11. And while still continuing to hold down the Control key, will now highlight net pay in K3 through K 11. Now that we've selected the data, it's time to create a chart. Most Excel users go to the insert tab to select the chart type. But we're planning to create a column chart. So here's a quicker way. If we press Alt F1, we create a chart on the same sheet is our spreadsheet. Let's go back and highlight the same data as before. Using the control key will highlight employee names, gross pay and net pay. Another handy keyboard shortcut is F11. When we press F11, Excel creates a column chart on a new sheet. Now let's go back to the original sheet. One other keyboard shortcut we should know about is the delete key. If we select the border of any chart and press the Delete key gets removed from the spreadsheet. So now we know several useful keyboard shortcuts when working with column charts. 38. Section 7.2 - Pie and Doughnut Charts: In this lesson, we'll learn how to create pie charts and donut charts. Pie charts are used to display a single series of data. For example, a pie chart might represent 100% of total sales, while each slice represents a percentage of total sales. Here are some typical examples of pie charts. This example displays product sales in terms of quantity sold. We sold a total of 72 widgets, 31 large, 24 medium, and 17 small. This example displays product sales in terms of percentage. So what's the difference between a pie chart and a doughnut chart? A doughnut chart is basically a pie chart with a hole in the middle. The main advantage is that we can place text such as the title inside the doughnut hole, thereby saving space. This example displays the same information as the first pie chart. The only difference is that if we place the tidal inside the doughnut hole, this donut chart displays the same information the second pie chart contained. Again, we placed the tidal inside the doughnut hole. Let's say we'd like to see how many widgets we've sold or what percentage of our sales each one represents. First, will highlight the product names in A3, A6. Then hold down the Control key and select the quantities in E3 through E6. After releasing the Control key, we will select the Insert tab in the upper left. Then click the pie chart button. Next, we will select the 2D pi. And let's move that chart to a separate page. On the chart design TAM will select the button called Move chart. And we'll select new sheet. We could rename them to chart if we wanted to, but we'll accept that name for right now. And click OK. First, we'll change the title from quantity sold to product sales. Will highlight the title. Google up to the Home tab in the upper left, and increase the font size to 36. When we click outside the title. The pie chart readjust. Full, remove the legend. To do that, we'll click the chart elements button. And D select legend. In place of a legend, we'll be adding data labels. To customize our data labels. We'll click the button that points to the right and then select more options. Besides the value, we'd also like to include the category name. And instead of using a comma as the separator, we would like to start a new line. So the category name will be above and the value will appear below. Now let's change the position of the labels. Will center them within each slice of pie. And next will go up to the Home tab. Will change the font size 220, and the font color to white. Now we can click outside the chart had closed the Format Shape panel. Next we'll create some variations on our chart. Let's go down to the tab and right-click it. And will choose move or copy. From there we'll select, Create a Copy and click OK. On this one will change the settings to display the percentage of sales. So first, we'll select the chart. Will go up to the Chart Elements button. Roll over to Data Labels, and click the right pointing arrow. And then select more options. The only change will make here is we'll remove value and replace it with percentage. Now we can close the panel. Can click outside the Charleston. Next we'll create a doughnut chart. First we'll select this chart. Go up to the chart designed button, and then click on Change Chart Type II section. We have the option of selecting a donut. And when we do, we just click OK. And it will convert that into a doughnut chart. In this example, we're going to remove the title. So we'll select that and press delete, which makes the donut chart slightly larger. And in the center of the doughnut will add a text box with our title. So let's go up to the Insert tab. Will choose textbox and draw one in the center of our doughnut chart. We'd like our title to be centered vertically and horizontally. So first, we'll make those changes center vertically and horizontally. We'll type in product sales. We'd like to make the text a little larger. So we can highlight that. Let's increase the font size to 32. And we'll change the font color to dark gray. And then click outside the chart. So now we know how to create pi and donut charts. 39. Section 7.3 - Column and Bar Charts: In this lesson, we'll learn how to create column and bar charts. Column charts are often used to compare items or show changes over time. Here are some examples. Here. We used a 2D clustered column chart to compare sales of different products over a period of four quarters. This example is just a 3D version of the previous chart. This one is also a 3D version of the first chart. We looked at. This 2D stacked column chart groups each product into a separate column and stacks the four quarters within each of those columns. For example, the apples column contains 100% of all Apple Sales. Within that column. Apple sales are broken down by quarter. This example is just a 3D version of the previous chart. In the 2D 100-percent stacked column chart, the first column shows 100% of Apple's sales, broken down by quarter. Each quarter represents a percentage of total Apple Sales. This chart is simply a 3D version of the previous one. A bar chart is basically a column chart turned on its side. Here are a few examples of bar charts. This first example is a 2D bar chart which compares sales for 20192020 by quarter. This chart is just a 3D version of the previous chart. In this example, 20192020 sales are stacked or combined in one bar. N grouped by quarter. First quarter bar represents total combined sales for 20192020. This is simply a 3D version of the previous chart. This example of a 100% bar chart in which each bar represents 100% of sales for 2019 through 2020, broken down by a quarter. First quarter sales for 201983 thousand compared to first quarter sales in 2020, which were a 103 thousand. This chart is simply a 3D example of the previous one. In this exercise. And we'd like to compare sales of different items over a period of time. We need to see how many of each item we sold in each quarter. We'll be using a 2D clustered column chart, which presents the information simply and clearly. First, we'll highlight A1 through F5 and then press the F11 keyboard shortcut we learned earlier. We could stop here, but let's explore some of the design and layout options. If we click the Add Chart Element button in the upper left, we can add, remove and format parts of our chart. Let's select axes. And we will see the difference between Hi Mary horizontal and primary vertical axes. Now will point to Axis Titles and compare the difference between Primary Horizontal and Primary Vertical. Next, we'll look at chart title. We could choose not to have a chart title, place it above the chart, or overlaid the chart. Next, we'll take a look at data labels. Currently we have none selected. We can place those labels in the center, inside the top end, inside the base, outside the top end. And even as data call-outs. Next, we'll take a look at data tables. Data tables are handy because they present the data in a second way. We see a table appear below our charts, giving people two different ways to comprehend the data. We'll skip error bars because they don't apply in this example. By default, we have primary major and horizontal grid lines. We could have primary major, vertical. We can have minor horizontal grid lines and minor vertical grid lines. By default, our legend is at the bottom of our chart. We could place it on the right, on the top, or to the left. And finally, we can add trend lines. Next we will explore some quick layouts. These are pretty self-explanatory. All we do is decide which one we prefer. We can also change the color scheme. Will click the Change colors button and check out some of the color schemes that are available. And finally, we'll explore some of the chart styles. So now we know how to create a basic column chart. In this example, we'll learn how to create bar charts. Here we're going to compare sales between 20192020, quarter by quarter. We'll start out by selecting a2 through C6 and pressing F 11. Just start with a column chart. Will go up to the Change Chart Type button. And over on the left will select bar charts. Will be creating a two-dimensional bar chart. So we'll select the default option and click OK. As we saw in the previous lesson on column charts, we can go to the Add Chart Element button. Quick layout, change colors. And we can choose different chart styles. Will be using the Chart Element button and we will be adding data labels. By default, they appear to the right of each bar, but we'd like to make a few changes. Let's point to the Aram and we'll place them centered inside each bar. Then will roll down to more options. We'd like to include the series name, will change the separator to space. And then let's format the text. Currently the text is pretty dark, difficult to see. So we'll click the Text option button. Go to text fill. And we'll change the text color to white. Now we'll do the same thing with the other bar. We'll include the series name here. Change the separator from a comma space, then go up to Text Options and will change our text color to white. And then close. We can also change this into a different type of bar chart. Let's go up to the Change Chart Type button. And we'll change this to 100% stacked bar. And click. Okay. So this is a different way of viewing our data. We can see in the first quarter, we generated more income in 2020 than we did in 2019. So now we know how to create column and bar charts. 40. Section 7.4 - Line Charts: In this lesson, we'll learn how to create line charts. Line charts are often used to track change over time. Line charts work best for situations where we have a limited number of data points we wish to chart. For example, a line chart would be perfect for tracking sales by the month, quarter, or year. Line charts can also plot multiple datasets which is useful for comparison, such as comparing sales between different years. Here are a few common examples of line charts. This first example, Track sales by month over the period of a year. Data call-outs had been added to make it easier to read. This line chart contains multiple data series. It tracks and compares sales for 20192020. This example is a variation of the previous one. It also tracks and compares 20192020 sales. But it also includes a data table below the chart, giving users to ways to view the data. Now, let's learn how to create a line chart. In this exercise, we'll track annual sales with a line chart. First, let's select cells D2 to E 13. Now we'll go to the insert tab and select the Line Chart button. Will choose the 2D line chart. And then click the Move chart button in the upper right. So we can place it on a new sheet. Now we'll explore some of the different chart element options that are available. In most cases there isn't a right or wrong answer. We simply choose the options we think will work best. In the upper left. We'll click the Add Chart Element button. And we'll quickly run through the options. If we 0.2 axes. First will try Primary Horizontal and then Primary Vertical. Now we'll take a look at the axis titles. We can add Primary Horizontal, or primary vertical. Next we'll explore the options for R chart title. We could say none. We can place it above the chart or Overlay Chart. Next, we'll take a look at data labels. Currently we have none selected. But we can place the labels in the center of each point to the left there, right above, below. Or we can even add data call-outs. Next, we'll take a look at data tables. A data table is handy because it gives people two different ways of looking at the data. Currently we have none selected. But we can choose use legend with keys. Without keys will skip error bars because they don't apply to this chart. Now we'll check out grid line options. Currently we have primary major horizontal grid lines. We can also include primary major vertical gridlines, minor horizontal grid lines, and minor vertical grid lines. Next, we'll take a look at legend options. Right now we have no legend showing. But we could display it off to the right at the top, left or the bottom. Because the legend only tracks one item and all it says is series one. We really don't need a legend for this chart. Next we'll take a look at lines. We can add drop lines, vertical lines that extend down to the base of our chart. High-low lines do not affect this particular chart. And finally, we'll take a look at trend lines. We will be skipping up and down bars, say don't relate to this chart type. Next we'll take a look at quick layout. These are self-explanatory. We simply choose the one that we like best. Next, we'll change colors. This will only affect the color of the line. And finally, we'll take a look at some chart styles. In this exercise, we'll create a line chart to track and compare sales. In 20192020 will highlight J1 through L 13, and then go up to the insert tab and click the line chart button. Will try something different this time. Let's go with the second option. Now we'll click Move chart and place it on a separate page. And for this one, we'll add data call outs. Who grew up to Add Chart Element. Rule down to Data Labels. And we'll select Data call-outs. So now we know how to create and basic line charts. 41. Section 7.5 - Project 4 - Column Charts: In this project will create and format several different column charts to chart and the entire spreadsheet, we can click inside any cell containing data and a handy keyboard shortcut to create a new column chart is Function Key F11. This automatically creates a new sheet with a column chart that includes everything in the spreadsheet. Another useful keyboard shortcut is F1. This creates a column chart on the same sheet as the original spreadsheet. We can reposition that. If we decide to move this chart to a separate page, we can always do up to the move chart button at the top. Currently, this chart is considered an object inside the a to Z office supplies sheet. But we could easily add it to a new sheet. We can accept this name or we can change that and click OK. We can reverse that process to, let's say we have a chart that occupies a separate sheet by itself, but we'd like to move it and combine it with the original spreadsheet. We can go back to that move chart button. Let's move that back to the a to Z office supplies sheet. And click OK. To delete a chart, we simply left-click the border and press Delete. Sometimes we'd like to create a chart that only includes some of the data in a spreadsheet. For example, let's say we'd like to show the employees and their monthly salaries. With the highlighter to big wide plus sign will highlight the column headings along with the data. And let's press Alt F1. Notice that the chart focuses only on the names and the monthly salaries, and it picked up the title from column C. Let's delete that. Let's say we'd like to select columns that are not next to one another. In this example, we'll include employee name and their total compensation. We first select the employee names with the mouse. Now we hold down the Control key and highlight the total compensation section. We can release the control key and press F1. Let's delete that chart. In our next example, we'll go the First National Bank, and we'd like to include the employee name, their gross pay, and their net pay. Let's start by highlighting B3 down to be 11. We'll hold down the Control key and highlight E3 through 11. And K3 through K 11. Released the Control key. And let's press Alt F1. There are two values displayed for each employee. At the bottom of the chart, we see the legend. The blue column represents gross pay and the red column represents net pay. We can actually combine those two values into one column. To do that. Let's go up to the button called Change Chart Type. And at the top we see seven different column chart styles. The default setting is a clustered column. The second option is what we call a stacked column. This is where we would include two or more values in each column. Let's click the OK button to see the change. We'll go back to Change Chart Type. The 100% stacked column option won't look very interesting because everyone's gross pay goes up to the 100% mark. And since we're withholding the same amount of taxes from each employee, the relative percentage of gross to net is exactly the same. So this would be a poor choice for this data. Will go back to change chart type. The remaining charts are three-dimensional. This one 3D clustered column is basically a three-dimensional version of the original chart. If we go back to change chart type. The next one is basically a 3D version of the stacked chart with two values in each column. We click Change Chart Type again. This one won't be very interesting because it's just a 3D version of the 100% stacked columns. And we'll go back to Change Chart Type one more time. And this basically places one column behind the other. After looking at the different chart types, I'll go back and select number for the 3D clustered column. In this project, we got a chance to create several different column charts. 42. Section 8.0 - Working with Large Spreadsheets: In this section, we'll cover tips and tricks for managing large spreadsheets. Will learn shortcuts for selecting a Navigating, as well as how to make column in row headings remain visible as we scroll through a spreadsheet. 43. Section 8.1 - Navigating with Keyboard Shortcuts: In this lesson, we'll learn some keyboard shortcuts for getting around enlarge spreadsheets. Instead of scrolling, which takes a long time to navigate. We'd like to learn a couple of keyboard shortcuts. Starting out in cell A1. We'd like to go to the very last cell containing data in that column. We can press Control down arrow one time. To return to the very first cell will press Control up arrow. If we'd like to move from the first column of data to the last column, will just press Control right arrow. And control left arrow will take us to the first column of data. If we needed to go to the lower right hand corner of our spreadsheet, Control End will take us there. And if we'd like to get back to cell A1, control home would do that. If we'd like to go to a specific cell. We can press function key f file, type in the address and press enter. To return to cell A1 will press Control Home. To navigate from cell to cell. Course, we can use the arrow keys or the mouse. Here we're using the up, down, left and right arrow keys. Perhaps you're used to tabbing to move to the cell to the right. If you hold down shift and press Tab, this will move you one cell to the left. If you use the pressing Enter to move down one row, Shift Enter will take us backup. 44. Section 8.2 - Selecting Cells with Keyboard Shortcuts: In this lesson, we'll focus on methods for highlighting or selecting cells. Most people use the mouse to select or highlight, which is fine for small spreadsheets, but not for large ones. Here are some useful keyboard shortcuts to make highlighting cells easier when you're dealing with a large spreadsheet. We're starting out in cell A1. Let's hold down the Shift key on the keyboard. And as we hold that down, we'll tap the right arrow key. And the down arrow key. One thing that's nice about using the keyboard is it's a little easier to control than the mouse. If we want to highlight fewer items, we can continue to hold down the Shift key, hit the up arrow or the left arrow. If we'd like to select everything in column a, we can hold down Control Shift and tap the down arrow key one time. This will highlight everything in that column down to the last cell containing data. We can do something similar to highlight an entire row. This time we'll hold down Control Shift and tap the right arrow key one time. If we wanted to highlight the entire spreadsheet, we could now hit control shift down arrow. And everything's been selected. If we have no blank columns or rows in our spreadsheet, we should be able to press control a. You can remember that as a for all. And that will automatically select everything in the spreadsheet. To return to cell A1, we can press control home. Let's say we have a few blank columns. Move select column D and press the delete key. And let's select row ten and press Delete. If we're in this section and we press control a. Notice that the blank column and blank row prevent us from highlighting anything else. If we press control a again, that ends up highlighting all cells including blank columns and rows outside or spreadsheet. To overcome this, we will start out in cell A1. Let's press control shift right arrow. And we see it stops us at the first blank column. If we press control shift right arrow again, we jump past that blank column and then press control shift right arrow one more time. That will get us over to the very last column of data. The same thing applies to blank rows. If we press control shift down arrow, it stops when it sees the first blank row. To jump past that, we'll press control shift down arrow again. And one more time. To highlight down to the last row. 45. Section 8.3 - Using Freeze Panes to Lock Headings in Place: In this lesson, we'll learn how to use the Freeze Panes feature to keep row and column headings visible as we scroll through our spreadsheet. Large spreadsheets present unique challenges. One common issue involves column and row headings disappearing when we scroll. Here's an example. When we scroll down the page, the column headings disappear and it's hard to remember which columns are which. When we scroll to the right, customer names disappear. And it's difficult to know which record belongs to which customer. The answer in both cases is simple. Use Freeze Panes. We'll click the View tab. Select the Freeze Panes button. And to make sure our column headings are always visible, will select Freeze Top Row because that's where the column headings are located. Now when we scroll down the page, the headings remain visible. To unfreeze them, go back to the Freeze Panes button and select Freeze Panes. Now let's say we'd like to make sure that column a with a customer names remains visible. We'll go back to the Freeze Panes button, and this time we'll select Freeze First Column. Now when we scroll to the right, the customer name is always visible. So we stay oriented. To remove the Freeze Panes, Freeze Panes button, and select unfreeze. Let's say we'd like to freeze both column a and row one. We select cell B2 because we want everything above cell B2 and everything to the left frozen in place. This time when we go up to freeze panes, we'll select the first option. Freeze Panes. In the description it says keep rows and columns visible while the rest of the worksheet scrolls based on current selection. In our case, the current selection is cell B2. So now when we scroll down the page, our column headings are visible. And when we scroll to the right, customer names are still visible. So now we know how to use prese pains to keep column and row headings always visible. 46. Section 8.4 - Using Split Window Feature to See Different Sections of Spreadsheet: In this lesson, we'll learn how to split a large spreadsheet so we can view different parts of it at the same time. A window can be split into two or four sections. If we split it into two sections, the split can be either horizontal or vertical. Once a window is split, we can drag the split bars to reposition them. To split a window and to vertically. We first select a row. Then go to the View tab and select the Split button. The split bar will appear directly above this row. And we can use the vertical scroll bars on the right-hand side of the screen to scroll in each window. Now we can see rows that are quite distant from one another. Something we couldn't do. Normally. We can reposition this split mar my left dragging it. To split the window. We go back to the View tab and click the Split button again. To split a window into horizontally. We first select the column, go to the View tab, and click the Split button. The split bar will appear directly to the left of this column. And we can use the horizontal scroll bars at the bottom of the screen to scroll back and forth. Now we can view columns that are quite distant from one another. Something we normally couldn't do. To split the window will go back to the View tab and click the Split button. We can also split the spreadsheet into four sections. Based on the cell we select. The split bars will appear above and to the left of that cell. Let's say we click in cell F6 and click the Split button. Upper and lower sections can scroll bars off to the right and the bottom sections have scroll bars at the bottom of the screen. In general, I don't recommend splitting a window into four sections because it can be very confusing. I think you're better off splitting a window into to remove the split will go back to the Split button. So now we know how to split a window. 47. Section 8.5 - Hiding and Unhiding Columns and Rows: In this lesson, we'll learn how to temporarily hide and unhide columns and rows. Our spreadsheet may contain columns and rows that we don't wish to view or print. To hide columns or rows, we simply right-click a column letter and select Hide. The same thing goes for rows. Will roll directly over a row number. Right-click and select Hide. To hide multiple columns or rows, we can click and drag with the mouse. By highlighting those headings, we can now right-click any one of them and select Hide. And the same thing applies to road. Will use the left mouse button to select several rows. Right-click any row heading, and select hide. To hide non-continuous columns or rows will use the Control key. Lets say we want to hide column K and column m. Will select the first column, hold down the Control key, and left-click to select the second one. Then we'll right-click and select Hide. Unhide in columns and rows can be a bit challenging. If we roll over the vertical line that separates a visible column from a hidden column and row our mouse slightly to the left. We actually see two different cursors. The one we're viewing right now is designed to change the width of a column. But if we roll slightly to the right, this cursor is designed to unhide a column. When we see the cursor with the two vertical lines included, we can right-click and select unhide. Let's try that with some rows. In-between row 1926, there are some hidden rows. If we roll our mouse over rho 26 and move it up slightly, we see the cursor that allows us to unhide rows. If we roll the mouse slightly higher, it changes into the cursor that changes the height of a row. So we'll roll down slightly so that we see the correct cursor. Right-click and select unhide. So now we know how to hide and unhide columns and rows. 48. Section 8.6 - Project 5 - Working with a Large Spreadsheet: In this project, we'll use techniques covered in this section to manage a large spreadsheet. First, we'll learn how to navigate using the keyboard. To move to the very last row of data will take quite some time if we scroll with the mouse. But if we use the keyboard shortcut Control down arrow, we can instantly move to the very last row of data. And Control up arrow will take us to the very first row of data. To move to the last column MOOC press Control right arrow. To move back to the first column, will press control left arrow. Also, selecting cells in a large spreadsheet can be very difficult. So we'll cover a couple of quick keyboard shortcuts that make it faster to select data in a large spreadsheet. If there are no blank rows or columns in the spreadsheet, we can press control a, that's a for all. And we should be able to select every cell in the spreadsheet. Let's press Control Home to return the cell A1. We can also hold down the Shift key and tap the down arrow and right arrow to select smaller sections. To de-select will hold down the Shift key and press the left and up arrows. To select an entire column of data. Will press control, shift down arrow. Let's press control home to return to cell A1. And if we need to select the entire row, we can press control shift right arrow. And again, we'll return to cell A1 by pressing control home. Another issue we encounter when working with large spreadsheets is column headings that disappear. As we scroll down the page. Earlier in this section, we learned about the Freeze Panes feature. Let's go up to the View tab. Will select Freeze Panes. And we'd like to freeze the top row in place. Now when we scroll down the page with the mouse, her column headings are always visible. To remove Freeze Panes, we'll click the Freeze Panes button again and select unfreeze pains. There are times when we need a column or two to remain visible at all times. In this example, when we scroll over to the right, we lose sight of the customer column. So we're unsure which row relates to which customer. To freeze column a in place. We'll click the Freeze Panes button and select Freeze first column. Now when we scroll to the right, we never lose sight of our customer names. And to remove Freeze Panes, we'll click the button and unfreeze the pains. And of course, there are times when we'd like to have columns and rows remain visible at all times. In this example, we'd like to keep column a and rho one visible at all times. So we'll click in cell B2 and everything above and to the left of that cell will be frozen in place. We'll click Freeze Panes and this time we'll select the first option. Keep rows and columns visible while the rest of the worksheet scrolls based on current selection. And our current selection is so b2. So everything above and to the left of that will remain visible. Let's go back to freeze panes and we'll unfreeze those. Many times. We need to view different parts of a large spreadsheet that are quite distant from one another. For example, let's say I need to compare the first 20 rows with the last 20 rows. Will, of course, I can't normally see those on the screen at the same time. So what we'll do here is we'll split the window. This is similar in many ways to freeze panes. How select row 21. Click the Split button. And now the split bar appears directly above that row. And off to the right. I can use the scroll bars. So now I can view two sections of my spreadsheet that normally are too distant from one another. This can be very handy when working with large spreadsheets. I can adjust the split par by dragging it to a new position. And I can remove it by clicking the Split button. There are also times when we might want to split the window vertically. Let's select Column F. And when we click the Split button, it will place the split part to the left of column F. At the bottom of the screen, we have two sets of scroll bars. And we'll AND-split that. Sometimes when we're working with large spreadsheets, we'd like to hide columns or rows. We may not need to see them as we work with a spreadsheet, or we may not want to print them. To hide an individual column. We simply right-click the column letter and select Hide. To unhide a calm. We roll up to the vertical lines between the two columns. And we're looking for the cursor with two vertical lines in it. When we see that, we can right-click unhide the column. To hide multiple columns. We will use the left mouse button to drag across the column headings. Release the left mouse button, Right-click any one of the column headings, and select hide and unhide those columns. Again, we'll roll up to the vertical line between the columns. Look for the cursor with the two vertical lines on it, a right-click and unhide. We can also hide non consecutive or non adjacent columns. Let's say we need to hide column C and G. After left clicking column C will hold down the Control key. And left-click column G. We can now right-click either one and hide them both. To unhide them. We will use the same procedure. Right-click and unhide. And you see that we unhide both columns at the same time. Those techniques also apply equally to rows. If we right-click and select Hide, we'll look for the cursor with two horizontal lines in it. Right-click and unhide. So those techniques can be useful when we need to temporarily hide columns or rows or viewing or printing purposes. So in this project, we had a chance to practice some of the techniques that we learned in this section for working with large spreadsheets. 49. Section 9.0 - Conclusion: Congratulations, you've completed beginning Excel. Mastering the basics. You should now have the skills and confidence to create a wide variety of different Excel projects. I hope you enjoyed this course and found it useful. Please take a moment and let me know what you thought of it. And thanks for watching.