_Website_Business Info.

Click the Links below for the Excel Video Assignment
ABC Spreadsheet	ABC 2 Charts
Follow along with the video in order to create a spreadsheet in Excel that uses an Auto SUM formula (fx) to calculate the totals of doughnuts sold for each month.	Create 2 very informative, creative and visually pleasing charts.
Drop	Drop

G.P.A. Excel Doc
Part 1		Part 3
Step # 1a Column Width & Row Height	Step # 6 If Error Formula in Cells B3:I3
Step # 1b Format All Cells	Step # 7 If Error Formula in Cells B3:B11

Step # 1c Center Alignment &Text Wrap	Step # 8 Input QUARTER grades	Step # 11 Make 4 copies of the Plain Sheet. Use the Move or Copy command.
Step # 2 Type in your Class Schedule	Drop Part 1 and wait for an A+ grade before going on to Part 2.
	Part 2
Step # 3 Set the Number Value Round to the nearest tenths.	Step # 9 Conditionally Formatting F = 0.0-0.9 D = 1.0-1.9 C = 2.0-2.9 B = 3.0-3.9 A = 4.0	Step # 12 Insert quarter grades & new elective titles.
Step # 4 Average Formula in column J		Step # 13 Insert a Row and adjust the formulas in Cells B13 and J12 for the 4th quarter sheet.
Step # 5 Average Formula in row 12	Step # 10 Is Blank Rule
	Drop Part 1&2 and wait for an A+ grade before going on to Part 3	Drop

	School Email	F.O.L. Link
	firstnamedotlastname@gdotkpbsddotorg mickey.mouse@g.kpbsd.org

G.P.A. Excel Doc
Part 1		Part 3
Step # 1a Column Width & Row Height	Step # 6 If Error Formula in Cells B3:I3
Step # 1b Format All Cells	Step # 7 If Error Formula in Cells B3:B11

Step # 1c Center Alignment &Text Wrap	Step # 8 Input QUARTER grades	Step # 11 Make 4 copies of the Plain Sheet. Use the Move or Copy command.
Step # 2 Type in your Class Schedule	Drop Part 1 and wait for an A+ grade before going on to Part 2.
	Part 2
Step # 3 Set the Number Value Round to the nearest tenths.	Step # 9 Conditionally Formatting F = 0.0-0.9 D = 1.0-1.9 C = 2.0-2.9 B = 3.0-3.9 A = 4.0	Step # 12 Insert quarter grades & new elective titles.
Step # 4 Average Formula in column J		Step # 13 Insert a Row and adjust the formulas in Cells B13 and J12 for the 4th quarter sheet.
Step # 5 Average Formula in row 12	Step # 10 Is Blank Rule
	Drop Part 1&2 and wait for an A+ grade before going on to Part 3	Drop

CW - Intro to Excel
ABC Video	ABC Doughnuts Create 1 Excel Document Save as LastName_FirstName_excel_ Rename the SHEET tab at the bottom of your spreadsheet. Name it ABC.
	#1 Follow along with the video in order to create a spreadsheet in Excel that uses an Auto SUM formula (fx) to calculate the totals of doughnuts sold for each month.	10:00 min.	ABC Spreadsheet
	#2 Create 2 very informative, creative and visually pleasing charts.Include 2 Bar Charts. Don't forget the titles.	9:00 min.	ABC Charts

Frequently Asked Questions
.Where is the Formula button (FX)?
Where is the Merge Button?
Where is the AUTO SUM button?
Where is the Chart Button?

Frequently Asked Question
Where is the Formula button (FX)?

Frequently Asked Question
Where is the Chart Button?

Frequently Asked Questions
Where is the Text Wrap Button?
Where is the Merge and Center Button?

Frequently Asked Questions
Where is the Merge Button?
Where is the Conditional Formatting Button?
Ms. Church show me how to Conditional Format cells.

Frequently Asked Questions
Where is the Chart Button?

GPA Part 1
Start Here........ Part 1 ---- Make a NEW GPA_Chart in Excel or Google Docs.. Save it as GPA_LastName_FirstName Rename the SHEET and call it Quarter GPA Chart
If you are creating the Chart in Google Sheets go to Step 2.
If you are creating the Chart in Excel go to Step 1.

Step by Step Instructions

Step 1

Format Cell Size

Step 1a.   Select Cells A1 - J12
          Format Cells > Column Width = 15
          Format Cells > Row height = 35-40

Step 1b. Select the Whole chart

	Format > All Borders
	Format > Bold Font
	Format > Font Calibri
	Format > Font Size 16

Step 1c. Alignment
Select the Whole chart > Wrap Text >

Step 2.

Type the G.P.A. Chart

Google Sheets Tool Bar

Google Sheets Tool Bar

Google Sheets Tool Bar

Step 2. TYPE your Class Schedule.
Click to see an example.

Select cells A1 - J12 and choose all borders

Step 2a) In cell A1 Type the title "Grade Check Chart"
Merge cells > A1-J1.

Center cells > A1-J1.

Center cells > B2-J2.

Step 2b) In cells B2 - J2 Type in your Class Schedule.

Click the Text Wrap Tool so that Language Arts fits in the cell.

Step 2c) In cells A3-A11 Type the week number.

Step 2d) In cells A12 type the title "G.P.A. by Subject."

Click the Text Wrap Tool so that G.P.A. by Subject fits in the cell.

Step 2e)

Type the number four in Cell G6.

Type the number three in Cell E4.

Type the number two in Cell B7.

Type the number one in Cell H9.

Type the number zero in Cell E5.

Step 3

Number Format

Step 3a) If you did not complete Step 2e) then type in a few G.P.A. numbers into the chart. 4's, 3's, 2's, 1's or 0's.

Step 3b) Now select cells B3 - J12

Step 3c) Set the Number Value from General to Number.

Step 3d) Set the decimal value
                                   to round to the nearest tenths position.
                             From a 3 to a 3.0
                             From a 2 to a 2.0
                             From a 1 to a 1.0 etc.

Google Sheets Number Tool Bar

Change to Number

Step 4

Imput Formulas

Google Sheets Formula Tool Bar

Step 4. In Cell J3 type in the = Average Formula:

=AVERAGE(B3:I3) Press Enter

Use the "skinny green +" to extend the Average Formula all the way down the column.

When you have an AVERAGE Formula in a cell but there are
no numbers in the cells you'll get the Error Message .

We'll get rid of the Error Message in Step 8.

Step 5

Step 5. In Cell B12 type in the = Average Formula:

=AVERAGE(B3:B11) Press Enter

Use the "skinny green +" to extend the Average Formula to the right for the entire row.

Step 6

Error Messages

Watch the video

or
follow the
Step by Step
Instructions.

When you have an AVERAGE Formula in a cell but there are
no numbers in the cells you'll get the Error Message

Step 6. Let's Hide the Error Message

How to get rid of the Error Messages

3:05 min

Step 6 - Let's Hide the Error Message

Step by Step Instructions

Step 6a Select Cell J3 that has the

Error Message.

Step 6b Edit the Average Formula =AVERAGE(B3:I3)


Step 6c	Put your cursor between the = sign and the A in average.
Step 6d	Type iferror(
Step 6e	Put your cursor behind the last )
Step 6f	Type a comma , 2 quotes "" and a )

Step 6g) Your "If Error Formula" should look like this:

Step 6h) Use the "skinny green +" to extend the NEW Formula all the way down the column.

Step 7

Step 7 Repeat the Steps from 6 in Row 12 but use (B3:B11) instead of (B3:I3)

Step 7a Select Cell B12 that has the Error Message.

Step 7b Edit the Average Formula so that it looks like the "If Error Formula" below.

Step 7c Use the "skinny green +" to extend the NEW Formula all the way down the row.

Step 8 Get your Grade Report from Ms. Church and input your Quarter Grades.

Drop and wait until Ms. Church
Grades your Excel Document.

GPA Chart
Part 2


. .
GPA Part 2 Step 9 - 10 Conditional Formatting Watch the video or follow the Step by Step Instructions.	Step 9) Select the whole chart and input the Conditional Formating for the GPA Ranges.
	Ms. Church show me how to Conditionally Format cells.
Step by Step Instructions		One more step in Part 2 to complete. Scroll down to Step 10.

Step 10.

Is Blank Rule

Watch the video
or
follow the
Step by Step
Instructions.

Change the cell color of Blank Cells to White.

2:24 mi

After Conditional Formating your chart the
0.0-0.9 range and a Blank Cell will be the same color.

Excel gives Blanks cells a zero value, therefore,
you need to add an Is Blank formula so that the cell that
Is Blank will be white.

Ms. Church show me how to change the chart from pink to white.

Click for the Step by Step Instructions.


G.P.A. Part 3 Quarters Steps 11-13	. Step 11) Once the Formatted Sheet called Part 2 is completely finished Move or Copy fours New Sheets and change the titles to 1st Quarter, 2nd Quarter, 3rd Quarter & 4th Quarter. .
.
	Step 12) Change the title and the background colors for the title of each Quarter SHEET. Go to Power School > to the Standards Grades TAB to find & edit your Electives. .
.

4th
Quarter

has

10 weeks

Step 13) Insert a row above
"GPA by Subject" row A12 so you may include the 10th week.

INSERT ROW

1. Select Row 12

2. Right Click the Row > Insert > Entire Row

3. Title the new row "Week 10 grades."

4. Edit ALL Formulas to include Row 12.

Step by Step to....

Edit the formula to include the new row.

1. Put a Number in Cell B12 then Click on cell B13.

2. See below for the example.
Put your cursor in the B3:B11 area of the formula.
Once you click the Column will be highlighted.

3. Now drag down the "Skinny Blue +" to include the cell in your formula.

4. Press Enter

5. Click Hold Drag the "Skinny Green +"in Cell J13 to the left
to copy the formula into the other cells in the row.

6. Press Enter

7. Repeat Steps 1-5 for Cell J3 and the rest of Column J

5. Edit ALL Conditional Formatting to include Row 12.

	Frequently Asked Questions
	UP

	Where is the AUTO SUM button? a.k.a. Where is the FX button?
	UP
	Where is the Chart Button?

	UP

	Frequently Asked Questions
	UP
	Where is the FX button?

	UP

	Frequently Asked Questions
	UP
	Where is the Chart Button?

	UP

	Frequently Asked Questions
	UP
	Where is the Text Wrap Button?	Where is the Merge and CenterButton?

	UP

	Frequently Asked Questions
	UP
	Where is the Settings and Print Preview? Special Note - This version there is no Print Preview Settings.
	UP
	Where is the Merge Button?
	UP
	Where is the Conditional Formatting Button?
	Ms. Church show me how to Conditional Format cells.
UP

	Frequently Asked Questions
	UP
	Where is the Chart Button?
UP

G.P.A.	Frequently Asked Questions
	Back to Video GPA
	Where is the Merge and Conditional Formatting Button?
Back to Video GPA

Just for fun Hour of Code
Sign Up	Email first name dot last name at g dot kpbsd dot org shell.church@g.kpbsd.org

G.P.A. Video

Conditional Formating Videos

Fri.	G.P.A. Video
	Create a new GPA Excel Document with drop down windows, Value Look up formulas and conditional formatting. for each G.P.A. range. Save it as LastName_FirstName_gpa
	G.P.A. Video	Conditional Formating Videos

1- Excel Video Doc
ABC	Fred's Part 1				Fred's Part 2
01	02	03	04	05	06	07	08
Drop	Drop 02-05				Drop 06-08

Part 1

Part 1