Microsoft Office Tutorials and References
In Depth Information
Working with Large
and What-If Analysis
This chapter introduces you to techniques that will enhance your ability to create
worksheets and draw charts. This chapter also covers other methods for entering
values in cells, such as allowing Excel to enter values for you based on a pattern of
values that you create, and formatting these values. In addition, you will learn how to
use absolute cell references and how to use the IF function to assign a value to a cell
based on a logical test.
When you set up a worksheet, you should use cell references in formulas
whenever possible, rather than constant values. The use of a cell reference allows you
to change a value in multiple formulas by changing the value in a single cell. The cell
references in a formula are called assumptions. Assumptions are values in cells that
you can change to determine new values for formulas. This chapter emphasizes the use
of assumptions and shows how to use assumptions to answer what-if questions, such
as what happens to the six-month operating income if you decrease the Equipment
Repair and Maintenance expenses assumption by 1%. Being able to analyze quickly
the effect of changing values in a worksheet is an important skill in making business
Wo r k s h e e t s n o r m a l l y a r e m u c h l a r g e r t h a n t h o s e c r e a t e d i n t h e p r e v i o u s c h a p t e r s ,
often extending beyond the size of the Excel window. When you cannot see the entire
worksheet on the screen at one time, working with a large worksheet can be frustrating.
This chapter introduces several Excel commands that allow you to control what is
displayed on the screen so that you can view critical parts of a large worksheet at
one time. One command allows you to freeze rows and columns so that Excel always
displays them on the screen. Another command splits the worksheet into separate panes
so that you can view different parts of a worksheet on the screen at one time. Another
changes the magnication to allow you to see more content at a smaller magnication.
This is useful for reviewing the general layout of content on the worksheet.
From your work in Chapter 1, you are aware of how easily you can create charts.
This chapter covers additional charting techniques that allow you to convey a message
in a pictorial fashion, such as by using sparkline charts and a clustered column chart.
For an introduction to
office and instruction
about how to perform
basic tasks in office
programs, read the office
and Windows chapter
at the beginning of
this topic, where you
can learn how to run a
program, use the
ribbon, save a le, open a
le, exit a program, use
Help, and much more.
Project — Financial Projection Worksheet
with What-If Analysis and Chart
The project in this chapter uses Excel to create the worksheet and clustered column
chart shown in Figures 3 – 1a and 3–1b. FroYoToGo is a seasonal business that operates
beach kiosks serving frozen yogurt during tourist season. Each spring, the owner