close

Вход

Забыли?

вход по аккаунту

код для вставкиСкачать
EXCEL 101
Level 1 on a PC
CORE (Centre for Organizational Resilience), For Youth Initiative
Basic Functions of Excel
CORE (Centre for Organizational Resilience), For Youth Initiative
Formatting Cells

Can change property and aesthetics of cells:
orientation, size, colour ...

Implications of formatting:


Only certain text can be entered into cell without
receiving error message
Can affect formulas (ex. Percentage)
CORE (Centre for Organizational Resilience), For Youth Initiative
Formatting Cells

Right Click on cell or go to top right menu bar
Formatting Cells

A menu will appear that will allow you to change the
format of your cell
Formatting Cells

Generally speaking, finance spreadsheets would use
accounting or currency formats, each with additional
formatting options
Formulas: The Basics

Add: +

Subtract: -

Multiply: *

Divide: /
CORE (Centre for Organizational Resilience), For Youth Initiative
Formulas: The Basics

To create a formula: In the cell that you want the
answer to be entered, put an equals sign
Formulas: The Basics

Then click the cell that holds the information that you
want in the formula—a letter:number combination will
show up in the formula cell and the top bar
Formulas: The Basics

Enter the sign representing the action you want to
complete [+, -, *, /] then click on the cell that represents
the second number in the formula or type in the values
required for your formula
This formula
means cell B2
will always
equal what is
in cell A2
multiplied by 5
Formulas: The Basics

To complete the formula, hit ‘Enter’
 When you click on the cell, the answer will be in the cell
and the formula will be visible in the top content bar
Formulas: The Basics

To have same formula in other cells, right click to copy the
cell and paste it where you want the formula to show up or
simply click on the cell and drag from the box in the bottom
right of the highlighted cell
When you click on the
cell, a small box will
appear at the bottom
right—drag that box to
copy the formula
Formulas: The Basics

A simple formula in action....
Formulas: The Basics

An Example involving more complex calculations

Shows the calculation of a salary including MERC
Auto Features

The Auto Sum button is the upper right hand corner
of your Excel Menu bar—it has a drop down menu to
select other functions
Auto Features

Auto functions can calculate different pieces of
information related to your data: ex. Sum, average,
min...
Once you click on
an auto function,
it will highlight
the data to be
included. Use
your cursor to
highlight the
correct data.
Auto Features

An Example of Auto sum
For Financial Management
How can you build a table that captures the
information you need for your finances?
Building a Table

Base headings and columns on information you
need to know

Insert formulas


Test them out!
Build a table that is easy for you to read and use!
CORE (Centre for Organizational Resilience), For Youth Initiative
Protecting a Table
PROS


CONS
Formulas are safe
If you know the
password, you can
still revise as
necessary

If you don’t know the
password, you cannot
revise the formulas
 Cannot account for
changes or mistakes
in formulas
CORE (Centre for Organizational Resilience), For Youth Initiative
Protecting a Table

Purpose: the cells with formulas are protected (ie. you
can’t revise them, and the cells that require data are unprotected)

All cells are locked by default, meaning if you
protect a sheet ALL of the cells are protected

Unlock cells that require data input first then protect
the worksheet
CORE (Centre for Organizational Resilience), For Youth Initiative
Unlocking Cells

Click on cells you want to remain open, go to
‘format cells’ and click on ‘Protection’

Uncheck
the
‘Locked’
box
Protecting a Table

Go to the ‘Review’ tab and select Protect sheet or
Protect workbook
Protecting a Table

A menu box will pop up—uncheck ‘select locked
cells’
Protecting a Table

Enter and then re-enter your password

Now the formulas are protected!
Revising a Table

WHY?

Finances:
 Tax rates have changed
 Budget items or details have changed

Statistics Tracking
 New funders or changes in funding practices mean
they want new/different information
 Your needs/wants have changed
CORE (Centre for Organizational Resilience), For Youth Initiative
Revising a Table

In terms of finance, has much to do with revising
formulas

Your sheet must be unprotected to change formulas

Go to the Review tab, click on unprotect sheet, and input
your password
 Unprotect will only be an option if you’ve protected
your sheet or workbook!
CORE (Centre for Organizational Resilience), For Youth Initiative
Revising a Table

Edit your formulas by clicking on the box with the
formula
The
formula
appears in
the
content
bar and
the cell.
Revising a Table

Revise the formula in the content bar at the top or in the
cell itself; drag or copy and paste to revise the formula in
the whole table
Was multiplied
by 5—now
multiplied by 10
Revising a Table

Once all the formulas are changed, the cell contents,
including the auto sum, immediately change to reflect
the new sum
Summary Sheet

Summary sheets total your data from previous
sheets


Useful when completing multi-term/multi-year
budgets
NB. Tables can be copied and pasted from one
sheet to the next
CORE (Centre for Organizational Resilience), For Youth Initiative
Summary Sheet

Create your summary sheet table in the format and
style you deem appropriate to capture the information
you need
Complete a
separate
table for
Year 1, 2, 3
of budget
Complete a
summary
sheet to
capture the
total budget
Summary Sheet

To add total data, enter a = in your cell, then go back
to the Year 1 sheet and click on the cell data that you
require
Note that the content
bar shows that you are
adding data from a
different sheet
Summary Sheet


Before you move to the next sheet, click +
Then go to Year 2 Sheet, click on data, hit +, go to Year 3
Sheet, click on data and press enter before going to the
summary sheet.
The content bar
shows your
formula.
Summary Sheet

When you click on the data from the last sheet and hit
enter, you will immediately be sent to the summary
sheet  the total is in the cell
If you click on the cell
with the total, your
formula will show in
the content bar. If you
change a value on one
of your sheets, your
total on your summary
sheet will change
automatically as well.
Hiding Panes

If you would like to see only specific information,
you can hide sections of your chart.

Make sure your sheet is unprotected to work on this
Hiding Panes

Highlight the columns or rows you’d like hidden by
clicking on the lettered or numbered bars and right
click
When you right
click, make sure
you do so on the
letter bar at the
top. A menu will
pop up and you
can click “hide”.
Hiding Panes

You now have the same information, but only certain
panes are visible.
Hiding Panes

To unhide the panes, highlight the columns on either
side of the hidden panes. Right click on the letter bar
and click unhide.
Remember to use
the letter bar to
highlight the panes.
Freezing Panes

This action keeps panes in place while others move
when you scroll
If we freeze the ‘Position Title’ pane, it will stay in place
as we scroll across the sheet.
Freezing Panes

Highlight the pane you want frozen by clicking on
the letter or number bar; go to the view menu
and click on ‘Freeze Panes’

It will provide you with three options: select the
one most appropriate for your needs
CORE (Centre for Organizational Resilience), For Youth Initiative
Freezing Panes
The first column has
been frozen so as
you scroll across the
‘Position Title’ pane
remains in place.
Header/Footer

Go to the Insert menu

Click the Header & Footer tab A number of menu
options are provided

Insert titles, page numbers, dates.....
CORE (Centre for Organizational Resilience), For Youth Initiative
Other Applications





Statistics tracking
Creating graphs and charts
Databases of donors, funders, participants....
Sorting and filtering information
Project Management
CORE (Centre for Organizational Resilience), For Youth Initiative
The Help Menu

The

Browse function
Search function

?
at the top right corner of the page
CORE (Centre for Organizational Resilience), For Youth Initiative
Quiz: What did you learn?
1.
2.
3.
4.
What is the first thing you enter into a cell to create
a formula?
How do you protect your formulas?
Why would you create a summary page? How do
you do it?
What else would you use Microsoft Excel?
CORE (Centre for Organizational Resilience), For Youth Initiative
Questions or Concerns?
Contact us at [email protected]
Follow us on twitter @fyiCORE
1/--страниц
Пожаловаться на содержимое документа