Microsoft Excel Tutorial: 3-Hour MS Excel 2019 Course for Beginners!

Simon Sez IT・2 minutes read

Cindy, an experienced Excel 2019 instructor, covers basics to advanced features in the course with tips, tricks, and new features like the CONCAT function. The course includes navigation, formulas, ranges, functions, data management, sharing, and customizing options for efficient Excel usage.

Insights

Cindy, an Excel 2019 instructor with 15+ years of experience, covers basics to advanced Excel features in the course.
New users benefit from video tutorials for improved understanding, while experienced users learn tips and tricks for easier Excel navigation.
Section 2 highlights Excel 2019 updates, such as enhanced selection features and new functions like CONCAT.
Excel's work area includes columns labeled with letters and rows numbered, with each sheet having the same column and row count.
Formulas in Excel are crucial for calculations, start with an equal sign, and utilize relative references, parentheses, and functions like SUM for efficient data processing.

Get key ideas from YouTube videos. It’s free

Recent questions

What is Excel 2019?
A software for spreadsheet creation and data analysis.

Related videos

Summary

00:00

Excel 2019 Course: Basics to Advanced Features

Cindy is the Excel 2019 instructor with over 15 years of teaching experience.
The course covers Excel basics to advanced features.
New users should watch videos in order for better understanding.
Experienced users can learn tips and tricks for easier Excel use.
Section 2 discusses what's new in Excel 2019, including improved selection features.
Accessibility checker helps visually impaired users identify and fix spreadsheet issues.
Translate feature allows text translation into different languages.
The new CONCAT function merges cell contents with added features.
The course overview includes Excel window navigation and terminology.
The Quick Access Toolbar provides essential functions like undo, redo, save, and print.

14:46

Excel Basics: Columns, Rows, and Cells

The work area in Excel is where text, numbers, and formulas are input, divided into columns and rows.
Columns are labeled with letters, with 16,384 available columns, while rows are numbered, with 1,048,576 rows.
Each sheet in Excel has the same number of columns and rows.
A cell is formed by the intersection of a column and a row, with a green line indicating the active cell.
Cells are named based on their column and row, like "C3."
The fill handle in the bottom right corner of the active cell allows for copying and predefined lists.
The name box displays the name of the selected cell and can be used to name ranges of cells.
The formula bar shows the content of the active cell, which can hold text, numbers, or formulas.
A scroll bar on the right and bottom allows for scrolling in different directions.
Different views like normal, page layout, and page break preview can be accessed at the bottom, with zoom options available.

29:09

Essential Spreadsheet Formulas and Operations

Formulas in spreadsheets are essential for mathematical calculations.
Formulas can be on the same sheet as data or on a separate sheet or file.
The mouse click determines where the formula will show up.
Key buttons on the keyboard for formulas include plus, minus, multiplication, and division signs.
All formulas must start with an equal sign.
Formulas can use cell addresses instead of typing numbers for flexibility.
Using the fill handle can copy formulas across columns or rows quickly.
Relative references in formulas change columns or rows to ensure correct answers.
The order of operations in math dictates the sequence in which calculations are done.
Parentheses in formulas are crucial for controlling the order of operations and getting accurate results.

43:54

Excel Ranges: Selection, Formulas, and Functions

Ranges in Excel are groups of adjacent cells that can be manipulated in various ways, such as changing colors, formatting as dollars, or using them in formulas.
To select a range, click in the starting cell, hold the mouse down, and drag to the ending cell, ensuring you release the mouse at the correct spot.
Avoid using arrow keys to navigate within a range, as it can deselect the range; instead, use the Enter key to stay within the range or the Tab key to move right.
When working with formulas, specify the range by indicating the starting and ending cells, such as B2:C4, to perform calculations accurately.
Functions like SUM can be used with ranges to quickly calculate totals, with the result displayed at the bottom of the screen.
Utilize the Shift key to select a range precisely by clicking in the starting cell, holding Shift, and clicking in the ending cell.
Saving your Excel workbook is crucial to retain changes; use the Save button or go to the File tab to save your work, ensuring you name the file and choose the appropriate location.
File extensions like .xlsx indicate Excel files, while formats like CSV and PDF serve specific purposes like data export or secure document sharing.
Excel offers options to share files with others, export them in different formats, and even publish interactive reports using tools like Power BI for collaborative work.
Understanding these saving and sharing features is essential for efficient file management in Excel, ensuring data integrity and accessibility for collaboration or personal use.

58:46

Excel File Management and Formulas Mastery

To save your exercise, go to the file tab and select "save as," creating a folder on your desktop named "my sales report" with the file extension ".xlsx."
Familiarize yourself with sharing, export, and publish options in Excel for future use.
In module four, learn to navigate workbooks by opening files and managing multiple open files efficiently.
Use the file tab to open a new file, where you can access recent files or browse to locate specific files for opening.
Understand how to work with formulas in Excel, such as changing values to affect calculations like monthly payments in a loan workbook.
Utilize different sheets within a workbook to view detailed breakdowns of data, like loan terms and monthly balances.
Link cells across sheets by creating formulas that reference specific cells in other sheets, ensuring data consistency.
Learn to navigate large files by using scroll bars, keyboard shortcuts like Ctrl+End to reach the bottom of data, and the name box to jump to specific cells.
Use the find and select option under the Home tab to search for specific words or numbers within your workbook, with options to refine the search criteria.
Explore the freeze panes feature in Excel to keep specific rows or columns visible while scrolling through large datasets, enhancing data visibility and navigation.

01:12:39

Excel Features: Freeze Panes, Split Screen, Headers

Freeze panes in Excel allows you to lock specific rows and columns for easier navigation.
To freeze panes, click on the cell where you want the split to occur, then select "Freeze Panes" and choose the option that suits your needs.
Split screen in Excel enables viewing two different parts of a file simultaneously without scrolling.
To split the screen, click on a cell where you want the split, go to the View tab, and select "Split."
The split screen creates a gray line that can be moved up or down to adjust the split position.
With split screen, you get two sets of scroll bars for independent navigation in each split section.
To turn off the split screen, click "Split" again or drag the splitter to the top of the screen.
You can split the screen both vertically and horizontally for versatile viewing options.
Saving a file with freeze panes or split screen active will retain these settings for the next opening.
Headers and footers in Excel can be customized with text like dates, file names, or personal information, visible in the top and bottom margins when printing.

01:26:25

Header and Footer Customization in Microsoft Word

Access the header and footer options by going to the insert tab and selecting header and footer.
Note the contextual tab that appears when in the header and footer view, offering various options like page numbers, date, time, and file name.
Utilize the "current date" option to automatically update the date to the current day each time the file is opened.
Include the current time in the header by using the appropriate coding.
Customize the header by adding your name, ensuring it appears consistently on all pages.
Explore footer options, such as displaying the page number and total number of pages.
Utilize pre-designed headers for quick customization or manually input desired information.
Consider different settings like "different first page" to exclude headers or footers on specific pages.
Set up print titles to ensure column headings appear on every page when printing a multi-page spreadsheet.
Use the comment feature to add notes to specific cells, which can be edited, resized, and deleted as needed.

01:40:42

Customize Margins, Orientation, and Printing in Excel

To access custom margins, go to the bottom and click on "custom margins" or use the arrow next to "page setup."
In the custom margins section, you can adjust top, bottom, left, and right margins and center content horizontally on the page.
Orientation options include portrait and landscape for printing, useful for fitting columns on a page.
Paper size can be changed from the standard 8.5 by 11 inches to legal, envelopes, or other sizes.
Set a print area by selecting the desired area to print, ensuring only that section is printed.
Page breaks can be manually inserted or removed to control where Excel breaks pages for printing.
Backgrounds can be added by inserting a picture from Bing or personal files, but they are only visible on-screen, not in print.
Adjust width, height, and scaling to fit content on one page, ensuring everything prints neatly.
In the page setup options, you can access portrait, landscape, scaling, and paper size adjustments.
Printing options include collated or uncollated copies, one-sided or double-sided printing, specific page selection, and choosing the printer and number of copies.

01:55:12

Excel Workbook Loan Detail Sheet Formatting

Open the loan workbook and go to the detail sheet for the five-year loan.
Check for headers or footers by using the page layout view in Excel.
Remove the generic headers and footers, add the date on the top right, and put your name in the footer.
Switch back to normal view after making these changes.
Add a comment on cell C9 stating it's the beginning balance of the loan.
Change margins to one inch for left and right, set rows 4-7 as print titles.
Print the entire workbook, save, and close the file.
Learn about working with rows, columns, and cells in module six.
Insert rows by selecting and using the insert option in Excel.
Change column and row widths by dragging or double-clicking the line between columns.

02:10:03

"Clipboard basics and Excel formula tips"

To turn on the clipboard, go to the Home tab and click on the clipboard grouping arrow.
The clipboard stores items cut or copied, allowing you to paste them elsewhere.
There is one clipboard for the entire computer, not per program.
The clipboard is cleared when all of Microsoft Office is closed.
The four steps for cut, copy, and paste involve selecting, choosing cut or copy, clicking where to paste, and pasting.
Example: Select a range, cut it, click where to paste, and then paste.
The fill handle in Excel copies formulas across cells automatically.
Excel uses relative reference to adjust formulas when copied across rows or columns.
Various paste options include values, formulas, transpose, formatting, and links.
Formulas in Excel start with an equal sign and can be written manually or using functions for efficiency.

02:25:05

Excel Functions: Simplifying Formulas and Calculations

Functions in Excel are words used in formulas to dictate actions, with over 200 functions available.
The most common function is "sum," used for addition.
The "autosum" button under the Home or Formulas tab simplifies adding functions.
Autosum assumes adding above or to the left of the clicked cell, allowing for easy adjustments.
Formulas can be manually adjusted by dragging through desired ranges before hitting enter.
The formula indicates the range being added, streamlining the process for adding multiple numbers.
Errors in formulas are flagged with a green mark, offering options to correct them.
Averages are calculated using the "average" function, while the highest number is found using "Max."
Excel offers a range of functions categorized for easy access, with financial, math, and trigonometry functions available.
Copying and pasting formulas is efficient due to relative referencing, ensuring correct answers.

02:40:05

Mastering Excel: Absolute Values and Sheet Tabs

Excel feature: Absolute values discussed in relation to automatic column or row changes for correct answers.
Explanation of when to use absolute values in formulas for specific instances.
Example provided using a company's divisions and commission calculations.
Review of relative references in formulas and the need for absolute values in certain cases.
Demonstration of creating an absolute value in a formula by fixing a cell reference.
Importance of understanding absolute values for accurate formula results.
Introduction to working with sheet tabs in Excel.
Instructions on adding, deleting, renaming, and moving sheet tabs.
Detailed steps on renaming sheet tabs for clarity and organization.
Additional options available when right-clicking on sheet tabs, including inserting, deleting, renaming, moving, copying, viewing code, protecting, and changing tab colors.

02:54:45

Excel: Unhiding, Selecting, and Writing Formulas

To unhide a sheet in Excel, right-click and select the option to unhide, then choose the desired sheet and click OK. Reasons for hiding sheets include protecting sensitive information, like drop-down lists, from unwanted changes.
You can select all sheets by clicking on one and holding shift, allowing changes made on one sheet to reflect on all selected sheets. To deselect, simply click on any sheet tab.
Three-dimensional formulas in Excel involve writing a formula on one sheet that calculates data from other sheets and displays the result on the current sheet. This is useful for consolidating information from multiple sheets.
When writing a three-dimensional formula, you select a range of sheets similar to selecting a range of cells. The formula starts with an equals sign, followed by the function (e.g., sum), then selecting the sheets and cells to add up.
Practice writing three-dimensional formulas by adding cells from different sheets, ensuring correct selection and calculation. Use the fill handle to copy the formula across multiple cells accurately.
In Excel, renaming sheet tabs to represent specific divisions, adding and deleting sheets, changing tab colors for clarity, and writing three-dimensional formulas are essential tasks for organizing and calculating data effectively.

Try it yourself — It’s free.