UNIT V STUDY GUIDE
Working With Microsoft Excel 2016–Part II
Course Learning Outcomes for Unit V
Upon completion of this unit, students should be able to:
4. Perform fundamental data-manipulation techniques.
4.1 Illustrate advanced Microsoft Excel 2016 techniques in PC Pal.
4.2 Describe uses of Microsoft Excel in daily life.
Course/Unit
Learning Outcomes
4.1
4.2
Learning Activity
Unit Lesson
PC Pal Unit 3
Unit V Quiz 1
Unit Lesson
PC Pal Unit 3
Unit V Essay
Reading Assignment
Complete the following lessons for PC Pal Unit 3. Each lesson consists of videos, interactive exercises, and
multiple-choice quizzes. (These are nongraded, but they are great practice for the interactive assessment at
the end of each unit.)
PC Pal Unit 3
Lesson 3.1 Goal Seek, VLOOKUP, and TREND
Presentations
3.1.1 Formulas, References and Data Entry
3.1.2 IF Statements and Tracing Errors
3.1.3 Using Goal Seek and VLOOKUP
3.1.4 The TREND Function and Quick Calculations
Interactive Exercises
3.1.1 Formulas, References and Data Entry
3.1.2 IF Statements and Tracing Errors
3.1.3 Using Goal Seek and VLOOKUP
Multiple Choice Quiz
Lesson 3.2 Outlining Data
Presentations
3.2.1 All About Outlines
3.2.2 AutoSum and Comments
3.2.3 Working With Multiple Sheets
3.2.4 Hyperlinks and ScreenTips
Interactive Exercises
3.2.1 All About Outlines
3.2.2 AutoSum and Comments
3.2.3 Working With Multiple Sheets
3.2.4 Hyperlinks and ScreenTips
ITC 3001, Personal Computer Fundamentals
1
Multiple Choice Quiz
Lesson 3.3 Creating Charts
Presentations
3.3.1 Charting Data
3.3.2 Layouts, Styles and Labels
3.3.3 Data Tables, Axes and Gridlines
3.3.4 Sizing and Positioning
UNIT x STUDY GUIDE
Title
Interactive Exercises
3.3.1 Charting Data
3.3.2 Layouts, Styles and Labels
3.3.3 Data Tables, Axes and Gridlines
3.3.4 Sizing and Positioning
Multiple Choice Quiz
After completing PC Pal Unit 3, complete the Unit V Quiz 1.
Unit Lesson
Unit V provides a practical application to advanced features of Microsoft Excel. Now that you know how
to enter data into a spreadsheet and how to format it so that it displays in a professional manner, it is
time to work with some of the more advanced features of Excel. As promised, we will begin working with
formulas in this unit because they are the strength of Excel. Without them, you might as well be using a
word processor to organize your data. First, we will cover the topic of managing worksheets and verifying
the integrity of the data.
When you open a blank spreadsheet, it contains one blank worksheet. Additional worksheets can be added
by clicking on the + next to the first blank worksheet. These worksheets can be named, copied, moved, or
deleted. More worksheets can be also added as needed. Worksheets can also be grouped, allowing you to
enter data or apply formatting to several worksheets at the same time. However, be careful when using the
grouping feature since you could accidently change the data in multiple worksheets when you only meant to
change it in one. Just as rows and columns can be hidden, worksheets can be hidden as well. The Hide
feature works a little differently with worksheets than with columns and rows. The Find and Replace
commands can also be useful, especially when working with larger worksheets.
As mentioned in the previous unit, if the data in the spreadsheet is not accurate and reliable, then the results
or totals will not be either. Excel provides features for you to validate the data entered into the worksheet. The
contents of the cell can be limited to a specific data type. You can also instruct Excel to identify and remove
duplicate rows or columns in your spreadsheet. Another feature that allows you to analyze and clean up the
data in a spreadsheet is the sorting feature. You can sort one or more criteria in ascending or descending
order. You can even sort the data based on conditional formatting. Sorting is a very useful feature, so be sure
to get some practice with it while you are reading for this unit.
Another very useful tool for analyzing data is filtering. A filter restricts the data set on your screen and allows
you to work with a subset of the data. For instance, you might filter by state and only show the rows for a
single state or several states from one region. You can also filter based on comparisons of numerical values
such as all rows where the balance is greater than 1,000. Data can be filtered on conditional formatting or cell
attributes as well. These topics are addressed in more detail within the interactive exercises.
Of course, one of the most important features of Excel is the ability to perform calculations. Formulas are the
mechanisms used to calculate totals. A formula is just an equation. All formulas start with the equal symbol: =.
A simple formula would be =2+2. Of course, the answer to that would be 4, so if you entered =2+2 into a cell,
then it would display 4. In most cases, you do not want to add two specific numbers together. It is much more
useful to refer to specific cells within a formula rather than specific values. For instance, to create a total of the
values in column B, you would place a formula in cell B3. That formula would state =B1+B2. The result would
be that the sum of B1 and B2 would be displayed in this field B3. It is as simple as that.
ITC 3001, Personal Computer Fundamentals
2
UNIT x STUDY GUIDE
Title
The formula for cell B3.
Students who are new to Excel formulas can become overwhelmed when constructing formulas. Just
remember to break it down and think through what makes up the total or calculation that you are trying to
create. The formula is always placed in the cell where you want the answer to appear. The contents of the
formula will depend on what you want the total to be. Do you want several cells added together? Then, simply
list the cell references with + between each one, and place an = at the beginning. There are other ways to
construct this formula, but the simplest way is best until you understand how formulas work.
Formulas consist of operators and operands. In our previous examples, the + was the operator. Other
operators include * for multiplication, - for subtraction, and / for division. Operands are the values used to
calculate the total. They can be constants, such as the 2 in the first example above, or they can be cell
references, such as B1 and B2 used in the second example above. Be sure to practice entering all of the
formulas in the hands-on practice in the exercises. The more experience you have with formulas, the better
prepared you will be to use them outside of this class when the need arises.
As you already know, a cell reference is an identifier for a specific cell. By using cell references, you are more
easily able to update your spreadsheet. Instead of having to change the value in a formula wherever it is
used, you just change the value in the cell that is referenced in formulas. When the change is made, all of the
formulas using that cell reference will be aware of the new value and will update the totals. This is an
extremely powerful feature of Excel. What happens when you copy a formula that uses cell references and
paste it in another location? Suppose you have the situation below, where column A is set up just how you
like it, and you want the same types of totals for columns B–E.
Typically, this is how you want Excel to behave, so the default of relative cell references works out well.
Occasionally, you would want to make sure that a cell reference does not change when a formula is copied.
Suppose you had the following spreadsheet with estimates for expenses that are based on a percentage of
sales. The formulas for each expense reference the cell where the multiplier is stored, so in order to calculate
the total expense for advertising for January, you would multiply the sales for January by the % multiplier for
advertising. The same multipliers should be used for every month.
If you were to copy the formula in cell B3 to cell C3, using the relative cell references as they are above, then
the resulting formula in cell C3 would be =C2*C7. This formula would result in an error, since there is no value
in cell C7. Instead, you would use the $ to let Excel know that you want a particular cell reference to remain
ITC 3001, Personal Computer Fundamentals
3
constant, even when the formula is copied. The formulas in column B should be
set xup
as follows.
The
UNIT
STUDY
GUIDE
resulting formulas in columns C–E are also shown.
Title
Cell references such as $B$7 are referred to as absolute cell references because they remain constant
regardless of their location. You can also use a mixed-cell reference where either the row or the column is
constant such as B$7 or $B7.
You can also use cell ranges and functions in formulas to simplify them. For instance, the formula
=A1+A2+A3+A4+A5 is the same as saying =SUM(A1,A2,A3,A4,A5) or =SUM(A1:A5). All three of these
formulas perform the same task and result in the same answer. Excel offers many different built-in functions
for common calculations, including basic functions, such as SUM, AVG, and COUNT, and more complicated
functions, such as finding the net present value, subtracting dates to find duration, or calculating the cosign or
tangent. Using these formulas can be daunting, but just keep in mind what you want to accomplish. It is also a
good idea to set up the formula and test it with very simple data before you apply it to real data.
Usually, displaying the numbers and totals is not enough to fully convey the information. Often, it is necessary
to include some sort of chart with the data to help explain the overall trends and message. Excel offers many
options for creating charts and graphs. For beginners, it is a good idea to use the chart wizard and the Excel
defaults for labels and legends. As you become more familiar with the charting feature, you can customize
them to a greater extent. You are going to be working with VLOOKUP and Pivot Charts that are very useful
for finding certain information needed in a spreadsheet. You will be using many more advanced features in
this unit.
Additional information is available from Microsoft at https://support.office.com/en-US/Excel, including Excel
training for current and older versions of the software (Microsoft, n.d.).
Reference
Microsoft. (n.d.). Welcome to Office help & training. Retrieved from https://support.office.com/en-US/Excel
Suggested Reading
In order to access the following resource, click the link below.
The following article provides some additional tips to help you work in Excel.
Paul, I. (2015, December). These 15 Excel keyboard shortcuts will help you become a spreadsheet pro. PC
World, 152. Retrieved from
http://go.galegroup.com.libraryresources.columbiasouthern.edu/ps/i.do?p=CDB&sw=w&u=oran95108
&v=2.1&it=r&id=GALE%7CA438207098&asid=91de05ab95cced62c2248e53fe16fcfe
ITC 3001, Personal Computer Fundamentals
4
Purchase answer to see full
attachment