Microsoft Excel for Absolute Beginners - An Excel Tutorial

Understanding what has made others successful can help you become successful. These are principles which have helped me become successful. I learned these principles from others and from my own experience. I share these principles to help you succeed in this course and in life:

• Time on task

• Grit

  • Angela Duckworth: Grit: the power of passion and perseverance

• Focus

  • Bill Gates & Warren Buffett

  • Bill Gates, “Get in front of what’s coming and let it hit you.”

• Habits of effective people

  • https://drive.google.com/file/d/0B22KXlqHz6ZNQ1VwM21ZR1FiOGM/view?usp=sharing&resourcekey=0-o4aHZPlgaITJUSAI9XsEjw

• my teachers

  • drop by drop, the bucket gets filled

  • persistently, patiently, you are bound to succeed

You can increase the speed of videos when you watch them. Not everyone knows this. This is something you should include in the beginning of all of your courses. Watching videos quickly helps many students. It’s not for everybody, but it works for a lot of people. You need your students to know about this. You can also turn on the “tools / document outline” for our course outline.

What version of Microsoft Excel are you using? Is this the most current version?

Is your computer a 32-bit or 64-bit machine?

Tell a friend or family member about spreadsheets and Microsoft Excel. Explain to them your understanding of both “spreadsheets” and “Microsoft Excel.”

Grab a piece of paper and a pen. Write out the principles for student success. Write a commitment to yourself as to how frequently, and how much, you will dedicate yourself to learning Excel. For instance, you might write, “I will study Excel every day for at least 30 minutes until I have finished the course.” Post this paper where you, and everyone else in your household, can see it every day.

A workbook has worksheets. When you open a Microsoft Excel file you are opening a workbook. Inside that workbook you will find worksheets. You can add and delete worksheets as needed.

• terminology

• workbook

• worksheet

• skills

• Starting Excel

• Saving a file

• Closing Excel

• Opening a file

• Spreadsheets are made up of columns and rows.

• The intersection of a column and row is a cell.

• The active cell has a green or black box around it.

• Each cell has a cell address: column row, eg, B2.

• Every Excel file is known as a workbook.

• Each workbook has worksheets.

• to enter data into a cell, click on the cell and start typing

• you can edit data in a cell by

• double-clicking the cell

• or up in the formula bar

• cells overflow if there is no data in the adjacent cell

• you can make columns wider

• ribbon menus

• give you different choices

• file menu

• hit the arrow to go back

• you can collapse your ribbon, and pin back in place

• view ribbon

• change view

• print view

• gridlines

• page layout

• gridlines

• headings

• status bar

• count

• average

• sum

• Learning tip: jump to hands-on Exercises to reinforce this material

While using Excel, your mouse pointer will change depending upon the context. Paying attention to the way your mouse pointer looks, and knowing what the different looking mouse pointers mean, will help you use Excel more effectively. Also covered in this video:

• worksheets

• name, color, position

• You can name the tabs of these worksheets, change their colors, and change their order. Right-clicking is helpful for this.

• add

• You can add new worksheets to your workbook by clicking the plus sign.

Learn the basics of writing formulas in Excel including the point-and-click method for writing formulas.

• writing formulas

• =

• ‘=

• point-and-click method

• order of operations

• relative references

• formula ribbon

• show formulas

Learn how to use relative, absolute, and mixed references when writing formulas in Excel.

• relative

• ab$olute

• mixed

• range

• a selection of 2+ cells

• colon notation

• B9:F9

• names

• named cells & ranges

Create a new excel spreadsheet. Do the following:

• create a new worksheet

• name it “Happy items”

• give the worksheet tab a color

• move the worksheet tab to the front of the tabs

• starting in cell B2

• list five items that make you happy

• one item in each cell: B2, B3, B4, B5, B6

• Delete the other worksheets

• right-click the worksheets

• edit the entry in cell B3

• use the double-click method

• edit the entry in cell B4

• use the formula bar

Create a new workbook. Do the following:

• make sure you are using normal view

• remove the gridlines

• collapse the ribbon menu

pin the ribbon menu back into place

Open the “020-hands-on-03” workbook. Using the “sales data” worksheet:

• For items F4:F21, what is the

• count

• average

• sum

Open the “021-hands-on-04” workbook. Write a formula which adds up B4:B7. Use relative references in your formula. Use the autofill handle to copy that formula across B8:M8

Open the “021-hands-on-05” workbook. Write a formula which calculates the tax. Use an absolute reference. Use autofill to copy the formula.

• Calculate the grade for each student. Assume each graded item carries equal weight. To calculate the grade for each student, just calculate the average score of all scores for that student.

• Calculate the class average for each graded item.

• Calculate the grade for each student. Each graded item does not carry equal weight. To calculate the grade for each student, you will need to do a weight average calculation.

• Calculate the class average for each graded item.

• Calculate the grade for each student. This gradebook is using the points method. To calculate the grade for each student, just add up a student’s points then divide that by total points possible.

• Calculate the class average for each graded item.

Calculate the costs for a trip to Disneyland in Anaheim, California, for yourself and three others. Have your trip last 5 days at the Disneyland & California Adventure parks.

• ticket price for both Disneyland & Cal Adventure

• airfare

• hotel

• $500 per person for food & incidentals

Create a spreadsheet that has the recipe for oatmeal peanut butter chocolate chip cookies.

• include the quantities

include the ability for the quantities to multiply by the number of batches desired.

Formatting your worksheets is important. Join me on a quick tour of some of things we will learn about formatting worksheets.

It is not only what you say that matters (the content) but also how you say it (the form). When studied, the greatest impact upon others isn’t the content, but the form. The 7 38 55 study from UCLA says that what impacts people in public speaking is:

• 7% the content

• 38% how it’s said

• 55% body language

This is true in public speaking, this is true in art, this is true in job interviews, and this is true in your Excel spreadsheets. Take your content and give it good form (make it look good).

We can format the cells of a worksheet to display different types of data:

• General

• Number

• Currency

• Accounting

• Date

• Time

• Percentage

• Fraction

• Scientific

• Text

• Special

• Custom

The alignment tab in the format cells dialog box allows you to align your text:

• horizontal

• vertical

• orientation (angle)

• wrap

• merge

• shrink-to-fit

Using the alignment tab, we can quickly achieve some very nice visual looks

In graphic design, font determines feeling. There are two broad categories of fonts: serif and sans-serif. A serif font has feet; a sans-serif font does not. For text on computer screens, sans-serif is the most popular and, perhaps by consensus, best choice. You can find the most popular fonts in the world on Google Fonts.  Once the fonts are installed on your computer, you can use them in your spreadsheets. Take-aways:

• use a sans-serif font

• use Google Fonts to get the most popular fonts

The “format cells” dialog box “border” option allows us to set borders around a cell or range of cells.

The “format cells” dialog box “fill” option allows us to fill a cell, or range, with a color and pattern.

The “clear” option on the “home” ribbon allows you to clear different elements on your spreadsheet:

• clear all

• clear formats

• clear contents

• clear comments

• clear hyperlinks

You can quickly format tabular data as a table using the “format as table” option on the home ribbon. Two good things to know about:

• filter checkbox

• convert to range

Using the format cells dialog box, format various data as different numbers.

Use the "format cells" dialog box "alignment" tab to align cell contents.

Using the "format cells" dialog box "font" tab:

• Use "Roboto regular" from Google Fonts for all text.

• Also make sure to "superscript" the 42 in cell V27.

Using the "format cells" dialog box, work with the "fill" & "border" tabs for format your spreadsheet.

Use the “clear” button on the home ribbon to clear the formatting on a spreadsheet.

Use the “format as table” button on the home ribbon to format data as a table.

Format this spreadsheet and make it look pretty.

An overview of what we have learned so far, and what we will learn next.

In hands-on #7 in Ninja Level 3 you saw me use the “snipping tool” to capture a picture of something in Excel. You can also capture a picture in Excel with “copy as picture.” This workflow, however, is not quite as elegant as using the snipping tool.

Paste has a few options which are useful to know about. These paste options allow us to determine how items we have copied are pasted:

• paste

• paste formula

• paste value

• paste transpose

We can copy data from the web and then use “paste special” to determine how that data is pasted into Excel.

• https://finance.yahoo.com/quote/TSLA/history?p=TSLA

We can use the “format” option on the home ribbon to format options for column and rows:

• width

• height

• hide & unhide

Excel makes it easy to insert pictures and shapes (arrows, smiles, stars) into worksheets.

• pictures

• online pictures

• shapes

• screenshot

• Smart art

• list

• process

• cycle

• hierarchy

• relationship

• matrix

• pyramid

• picture

• word art

• symbol

• copyright

• trademark

• unicode characters

You can insert links in your Excel worksheet. You can also insert text boxes.

Increase the functionality of Excel with third-party “add-ins” such as maps.

Using spreadsheet “054-hands-on-01-disney-trip” use “copy as picture” to copy the table to a picture. Paste that picture back into the spreadsheet.

Take the data in “055-hands-on-02” and transpose it from a column to a row. Important: only select the data, do not select the entire column. If you select the entire column, you will not be able to paste transpose.

Copy data from Yahoo finance, or data from somewhere else online, and paste the data into Excel using paste special.

• https://finance.yahoo.com/quote/TSLA/history?p=TSLA

Using “057-hands-on-04” adjust the row and column height to look good. Hide columns B & C.

Create a new spreadsheet. Save the spreadsheet as “058-hands-on-05.” Create a tab for each of the following, and then complete the tasks for each tab:

• pictures

• use Google to find an image that is labeled for reuse

• save that image to your computer

• insert that image into your spreadsheet

• online pictures

• find an image using “online pictures” and insert it into your spreadsheet

• shapes

• insert a star onto your spreadsheet

• screenshot

• open a web page

• go to excel, then insert a screenshot and choose “screen clipping”

• the web page should come to the foreground and allow you to select a region of the web page

• the selection of the web page should appear in excel

Create a new spreadsheet. Save the spreadsheet as “059-hands-on-06.” Create a tab for each of the following, and then complete the tasks for each tab:

• smart art

• insert a smart art of your choice

• if applicable, add 5 options to it

• word art

• insert this word art: “I’m learning so much about Excel!”

• symbol

• insert the copyright © symbol

Create a new spreadsheet. Save the spreadsheet as “060-hands-on-07.” Create a tab for each of the following, and then complete the tasks for each tab:

• link

• create a link to this poem

• https://www.thesunmagazine.org/issues/418/in-my-good-death

• text box

• insert a text box with this phrase

• “My Excel skills are going up and up and up!”

Create a new spreadsheet. Save the spreadsheet as “061-hands-on-08.” Use an “add-in” on your spreadsheet.

Create a new spreadsheet. Save the spreadsheet as “062-hands-on-09.” Copy the Oscar winners from either of these urls …

• https://en.wikipedia.org/wiki/List_of_Academy_Award-winning_films

• https://www.flickfilosopher.com/oscar-best-picture-winners-alphabetical

… then use “paste special” to get it into Excel.

This is an overview of where we’ve been and where we’re going. Looking at what we’ve covered, and what we’re going to cover, helps you learn. This is also referred to as “preview, view, review” and is a well-documented technique to help students learn more effectively.

You can add a background image to your spreadsheet. Use the “background” option from the page layout ribbon.

The stacking order is the order in which overlapping graphics stack. This is also referred to as the vertical stacking order. You can change the vertical stacking order of graphics by using the “bring forward” and “send backward” buttons on the page layout ribbon.

• forward

• backward

You can use buttons on the page layout ribbon to align, group, and rotate graphics.

Use options on the view ribbon to zoom-in and zoom-out on your spreadsheet.

• also: formula bar checkbox

Templates are pre-built worksheets. Excel has a variety of templates which you can search for and use.