This course explores Excel as a tool for solving business problems. In this course you will learn the basic functions of excel through guided demonstration. Each week you will build on your excel skills and be provided an opportunity to practice what you’ve learned. Finally, you will have a chance to put your knowledge to work in a final project. Please note, the content in this course was developed using a Windows version of Excel 2013. This course was created by PricewaterhouseCoopers LLP with an address at 300 Madison Avenue, New York, New York, 10017.
Excel Basics and Navigation Part 2
Loading...
來自 PwC 的課程
Problem Solving with Excel
2315 個評分
從本節課中
Overview of Excel
In this module you will learn the basics of Excel navigation and Excel basic functionality. You will learn how to navigate the basic Excel screen including using formulas, subtotals and text formatting. We will provide you an opportunity to perform a problem solving exercise using basic Excel skills.
與講師見面
Alex MannellaAlumni / Former Principal
[MUSIC]
In order to view and or edit a formula, you can go to the relevant cell and
double click or you can press F2 to open up the formula editing field.
Any referenced cells within a formula are highlighted using different colors.
These colors are also used to show the corresponding cell locations in
the sheets.
Sometimes, you may want to gain a better understanding of how a formula is being
calculated or if a complex formula is not returning the expected results.
This can be done by clicking on the "Evaluate Formula" button that is located
on the formula tab.
Clicking this button will open up a prompt that helps you walk through the formula
step-by-step.
Evaluate formula can be used to analyze Excel workbooks that are send to
you by someone else or it can be used well in developing and/or
troubleshooting complex formulas that have multiple components.
For the second exercise, we are going to use the "Evaluate Formula" option to
help us evaluate the function that we just typed.
We start by clicking on the formula in cell B21, and
then we select the "Evaluate Formula" option from the Formula ribbon.
As you can see, this opens up a pop-up window that will let us evaluate
the formula that is in the cell that we just selected.
We can step through the evaluation of that
formula by clicking the Evaluate button at the bottom.
If so, we'll underline the part of the formula that it is currently calculating.
As we keep clicking through, we will see how it's cell computes
the different values and end up with the final result of 25.
Once we are done evaluating the formula,
we click on the Close button to close this pop up Window.
To write a formula, we first need to select the proper formula for
this situation.
If we do not know the exact formula that is needed,
we can find a series of options on the formula bar.
If we open the Lookup and Reference bar,
we get a series of formulas that help us reference data.
When we hold our mouse over a formula,
Excel will give us a short description of that formula.
Excel has hundreds of functions that can be combined to do countless operations.
Even the most advanced Excel user is constantly learning new formulas and
new tricks.
Excel will assist you in writing formulas, but providing hints regarding
the structure of the formula that you selected, such as showing required inputs.
Excel formulas have both mandatory and optional fields.
The optional fields will be shown encompassed by brackets.
If you are unsure about the formula you are using,
or how to construct it, you can use the fx button which will walk you through
the construction of that specific formula.
Each formula has a unique logic that has to be followed in order for
the formula to calculate correctly.
Excel will prompt you if all fields are not provided,
or if fields are provided incorrectly.
For the third exercise, we will be looking into how Excel can help you
construct a formula, by providing various prompts.
Let's start by clicking on cell B27 and
pressing the equal sign to start a formula.
We're going to try out the average function which is an
Excel formula calculating the average value of a set of data points.
As you can see, Excel recognizes the formula and
tells us to enter at least one value since this is mandatory.
The additional values are optional since they are encompassed by brackets.
As you work within different sections of the formula, the active section is bolded,
I used this frequently when trying to troubleshoot a formula.
If I believe I am working on one component of the formula but it's not
the bolded section, then I know I'm missing a comma or parenthesis somewhere.
Here, let's try to calculate the average of one and three.
As you can see, Excel tells us that this is equal to 2.
If you want to see where the contents of a cell are coming from you can navigate to
the formula ribbon and click on the Trace Precedents button.
This will highlight all of the cells that are being used in order to calculate the value
in the cell that you are looking at.
The Trace Dependence button, also under the formula tab, will allow you to show any
cells that are referencing the cell that is currently selected.
Both the Trace Precedence and Trace Dependence buttons will show
arrows on the sheets, showing you how the data flows to one cell to the other.
You can remove these arrows by clicking on the remove arrows button that is also
located under the formula tab.
These functions become very helpful when auditing someone else's spreadsheet or
when error checking your own spreadsheet.
For the fourth and final exercise,
we will be looking into Trace Precedence and Trace Dependence.
Let's start by clicking cell B35.
Now, we go up to the formula ribbon and we select Trace Dependence.
As you can see, there's now an arrow pointing from cell B35 to cell
C35 showing us that cell C35 is currently referencing cells B35 in its calculations.
Excel also allows us to Trace Precedence,
showing us where a cell is pulling it's value from.
This is done by selecting cell C35 and
then going up to the Formulas ribbon and selecting Trace Precedents.
As you can see, there are now two arrows pointing from cell
B35 as well as cell B36 to cell C35,
Showing us that cell C35 is currently referencing both number two and
the number five and cell C35 and C36.
Note that once you are done Tracing Precedents and Dependents,
there are arrows across the screen.
You can remove these arrows by clicking on the remove arrows
button in the formula ribbon.
[SOUND]
