CEB201 Lecture1

Download as pdf or txt
Download as pdf or txt
You are on page 1of 49

Telue Engineering Institute

TOPIC 1: MS EXCEL 2010 –


REVIEWING THE BASICS AND A
LOOK AT SOME SPECIAL
FEATURES
Objectives:
 Review the Excel Windows Interface
 Be Able to Perform Basic Operations In Excel
 Saving a Spreadsheet
 Printing a Spreadsheet
 Be Able To Develop to Formulas in Excel
 Simple Formulas
 Built-In Formulas
 Be Able To Create Charts in Excel
 Explore Some Special Features in Excel
 Personalising Your Formulas
 Protecting Your Work
 Creating and Running Macros
 Activating the Add-Ins
INTRODUCTION TO MS EXCEL 2010

Microsoft Excel 2010 is a spreadsheet program that is used to


manage, analyse and present data. It includes many powerful
tools that can be used to organize and manipulate large
amounts of data, perform complex calculations, create
professional-looking charts, enhance the appearance of
worksheets and more.

Spreadsheets have the following important features:

• Designed to make repetitive and/or complicated


calculations very easy to carry out
• Have advanced graphing capabilities that make producing
graphs based on data given relatively simple
OVERVIEW OF THE USER INTERFACE

MS Excel 2010 Program Window


Name Description
Title Bar Appears at the top of the program window and displays the
name of the workbook and the program.
Quick Access Toolbar Provides one-click access to commonly used commands
and options
Ribbon Consists of a set of tabs, each of which contains groups of
related commands
Formula Bar Displays the data or formula stored in the active cell and
can also be used to enter or edit a formula, a function or
data in a cell.
Name Box Displays the active cell address or the name of the selected
cell, range or object
Workbook Window Displays a portion of the active worksheet
Sheet tabs Represents a different worksheet in the workbook
Scroll bars Enables you to scroll through the worksheet
Status Bar Used to display the worksheet in a variety of views and to
change the magnification of the worksheet
GOING “BACKSTAGE” IN EXCEL
• The Backstage View contains all the commands related to managing files and
customizing the program. It provides an easy way to create, save, open, share,
print and close files. In the Backstage View, you can find recently used files; view
and update file properties; set permissions; set program options; get help and
finally, exit the program.

To Display the Backstage View:


1. Click the File tab on the
Ribbon.

To Exit the Backstage View:


1. Click any tab on the Ribbon or
press the Esc key.
ABOUT THE RIBBON

The Ribbon contains the following components:


• Tabs for each of Excel’s main tasks that bring together and display all the
commands commonly need to perform that core task
• Groups that organize related command buttons into subtasks normally
performed as part of the tab’s larger core task.
• Command buttons within each group that you select to perform a particular
action to open a gallery from which you can click a particular thumbnail ( icon)
• Dialog Box Launcher in the lower-right corner of certain groups that opens a
dialog box containing a bunch of additional option you can select.
KEEPING TABS ON THE EXCEL RIBBON

“What Do I
Want To
Do?”

The Excel Ribbon contains the following Tabs:


 Home
 Groups: Clipboard, Font, Alignment, Number, Styles, Cells, Editing
 Insert
 Groups: Tables, Illustrations, Charts, Sparklines, Filter, Links, Text, Sympols
 Page Layout
 Groups: Themes, Page Setup, Scale to Fit, Sheet, Arrange
 Formulas
 Groups: Function Library, Defined Names, Formula Auditing, Calculation
 Data
 Get External Data, Connections, Sort & Filter, Data Tools, Outline
 Review
 Proofing, Language, Comments, Changes
 View
 Workbook Views, Show, Zoom, Window, Macros
KEEPING TABS ON THE EXCEL RIBBON

Tab Purpose
Home Contains command buttons for creating, formatting and editing a
spreadsheet
Insert Contains command buttons used to add particular items to the
spreadsheet like graphics, charts, headers and footers
Page Contains command buttons used when preparing a spreadsheet for
Layout printing
Formulas Contains command buttons used when adding formulas and functions to
a spreadsheet
Data Contains command buttons used when importing, querying, outlining,
and subtotalling the data placed into a worksheet’s data list
Review Contains command buttons used when proofing, protecting and marking
up a spreadsheet for review by others
View Contains command buttons used when changing the Windows display
SAVING IN MS EXCEL 2010

• To avoid having to re-create an entire workbook, adopt this motto: SAVE YOUR
WORK ANYTIME YOU ENTER MORE INFORMATION YOU COULD POSSIBLY BEAR TO
LOSE!!

• To Save Your Work Quickly and Efficiently:


• Use the Save button on the Quick Access Toolbar
• Press Ctrl+S

• When your work is saved for the first time, Excel displays the Save As dialog box.
The appearance of this dialog box means that you can perform the following tasks:
1. Replace the temporary document name - Given an appropriate and
meaningful name to your file
2. Change the default location – Select a new drive and folder
3. Change the file type – select a new file format to save the document as
THE SAVE AS DIALOG BOX

1
3
THE SAVE AS DIALOG BOX

 To Save A Spreadsheet as a
PDF
 To Save a Spreadsheet as a
Template

• A PDF document
cannot be edited ; A
Template is set up to
be edited later on

When working with macro documents save the document


as an Excel Macro-Enabled Template (*.xltm)
PRINTING IN MS EXCEL 2010

• Everything you do in Excel is in preparation for printing!


• In MS Excel, the biggest difficulty is getting used to the paging scheme and
learning how to control it.
• Spreadsheet programs like Excel usually break up pages both vertically and
horizontally to print a worksheet document.

• There are 2 ways to Handle Paging Problems in Excel:

• SWITCH TO THE PAGE LAYOUT VIEW


• USE THE PRINTING PANEL
PRINTING IN MS EXCEL 2010
SWITCH TO THE PAGE LAYOUT VIEW
• Excel’s Page Layout view gives you instant access to the paging of the current
worksheet.
• To Activate the Page Layout View:
• Go to the Status Bar at the bottom of the Excel Window, click the Page Layout
View button (the center one)

Viewing a spreadsheet in
Page Layout View
PRINTING IN MS EXCEL 2010
USING THE PRINT PANEL

• The best way to print your worksheet is directly from the Print Panel.
• To Print from the Print Panel:
1. Go to the File tab
2. Click the Print Option to open the Print Panel
OR
Use the Shortcut Key: Ctrl+P

• The print preview area in the Print Panel will show you exactly how the worksheet
data will look like when it is printed.
• If you need to, you can easily go back and make changes to the document, come
back to the Print Panel to view these changes and Print.
THE EXCEL WORKSHEET WINDOW
• An Excel Worksheet is made up of rows and columns. Rows are labelled using
numbers along the left side; Columns are labelled using Letters along the top of the
window.
• The box formed by the intersection of a row and a column is called a cell. Cells are
used to store data.
• Each cell is identified by its address which is made up of a column letter and a row
number.
• An active cell has a thick black boarder around it and its address appears in the
Formula Bar.

Cell
Address:
A1
THE EXCEL WORKSHEET WINDOW

WORKBOOK

ROWS
WORKSHEETS AND
COLUMNS

One of the benefits of spread sheets is that once a datum, label or


formula is entered into a unique cell in the spread sheet, the
contents of the cell can then be used elsewhere in the program
simply by referencing the cell address.
THE FORMULA BAR

Name Box Formula Bar Buttons Cell Contents

• The Formula Bar displays the Cell Address.

• The Formula Bar has 3 Sections:


✓ Name box: that displays the address of the current cell address.
✓ Formula bar buttons: a nondescript button in which when you start making
or editing a cell entry, Cancel (an X) and Enter (a check mark) buttons appear
between them
✓ Cell contents: the white area that takes up the rest of the bar and
expands as necessary to display really long cell entries that won’t fit in the
normal area.
FABRICATING FABULOUS FORMULAS
• Formulas are calculation instructions entered into worksheet cells

• Formulas are one of the three basic types of data that MS Excel handles (
text or numbers being the other two)

• A correctly constructed formula will give you the correct answer and it will
stay up to date by recalculating the results whenever you change the
values that the formula uses.

• To Let Excel Know that You are about to enter a Formula, in the active cell
enter a equal sign ( = ) followed by your Formula

• Formulas can be:


• Simple Formulas
A FORMULA OR
• Built-In Function FUNCTION ALWAYS
BEGINSWITH A
EQUAL SIGN ( = )
FABRICATING FABULOUS FORMULAS
SIMPLE FORMULAS
The most simplest formulas are made up of values or cell references and use the
following mathematic operators:

Cell References: A1, C13,


Values: 1, 2, 3, 4 …
JX56

SIGN OPERATION
+ (Plus Sign) Addition

- (Minus Sign or Hyphen) Subtraction

* (Asterisk) Multiplication

/ (Slash) Division

^ (Caret) Raising a Number to an


Exponential Power
FABRICATING FABULOUS FORMULAS
SIMPLE FORMULAS

For Example, to Create a Formula in C2 that multiplies a value entered in cell A2 by


a value in cell B2, enter the following formula in cell C2 = A2 * B2

To Enter this Formula in cell C2:

1. Select cell C2
2. Type the entire formula = A2 * B2 in the cell
3. Press Enter

OR

1. Select cell C2
2. Type the equal sign ( = )
3. Select cell A2 in the worksheet by using the mouse or the keyboard (arrows)
4. Type * (Shirt + 8)
5. Select cell B2 in the worksheet by using the mouse or the keyboard (arrows)
6. Click the Enter button to complete the formula entry while keeping the cell
pointer in cell C2.
FABRICATING FABULOUS FORMULAS
SIMPLE FORMULAS

There are Two Ways to Enter Simple Formulas:


 Type out Cell References
 Point/Select the Cell References

• The advantage of using the 2nd method is that you have less
chance of entering the wrong cell reference.

• After creating a formula that refers to the cells rather than the
values, you can now change the values in these cells and Excel will
automatically recalculate the formula using the new values and
the updated answer will be displayed on the worksheet.

CHANGE the value in B2 from 100 to 50!


FABRICATING FABULOUS FORMULAS
BUILT IN - FUNCTIONS
• Function: A pre-built formula that you can use to make calculations

• Excel provides a whole range of Built-In Functions starting from Basic


Functions, to Financial Functions, Logical Functions, Mathematical and
even Engineering Functions, all of which are located in its Function Library.

• There are several ways that you can insert a Built in Function into a
worksheet either by itself or as part of a formula:
1. Go to The Formula Tab
• Select the Formula Tab
• Under the group Function Library select Insert Function to open
the Dialog Box
2. Use the Formula Bar
• Select the Insert Function label located in the middle section to
open the Dialog Box
3. Type the Built-In Function
• Select a cell and type the equal sign ( = ) then type the name of
the Built-In Function
FABRICATING FABULOUS FORMULAS
BUILT IN - FUNCTIONS

• Method 1 and 2 are similar as they open up the Insert Function dialog box.

1. Search For a Function –


1 type a description of the
function’s purpose
2
2. Select a Category – select
3
a function based on its
appropriate category (
financial, logical or
engineering)
3. Select a Function –
provides a brief
description of the
function
FABRICATING FABULOUS FORMULAS
BUILT IN - FUNCTIONS

• Once your function is selected, the Function Arguments dialog box opens up.
• You can now enter your arguments which can be values or cell references.
• For simple everyday functions like SUM and AVERAGE the arguments selection is
easy but when faced with complex and new functions use the Help link on the lower
left hand corner.
FABRICATING FABULOUS FORMULAS
BUILT IN - FUNCTIONS
• The last method for inserting built-in functions into a worksheet depends on knowing
the correct function name.
• This can be done for commonly used functions like SUM or AVERAGE
• You start typing the equal sign ( = ) and the name of the function and Excel will
automatically display a list of possible functions to choose from.
• Once the function is selected, you can insert your arguments.
DON’T FORGET THE OPENING AND CLOSING BRACKETS!! 
FABRICATING FABULOUS FORMULAS
BUILT IN - FUNCTIONS

For Example, ADD the following values using the SUM FUNCTION in
Excel: 35, 44,24, 14, 10

To Enter this Formula in cell B3:

1. Select cell B3
2. Type the equal sign ( = )
3. Type “ SUM( “
4. Enter the values each separated by commas. Finish with the
closing bracket and press ENTER.
ALTERING THE NATURAL ORDER WITH BRACKETS

• Many of the formulas you will now be creating will contain more than one
mathematical operation; remember that Excel always performs each operation from
left to right and just like in maths, Excel follows the general rule:

B – Brackets First

E – Exponents Next To Alter (Change)


this Order of
M – Multiplication / Division Next Operations – use
Brackets ()
A - Addition / Subtraction Last

• In complicated formulas, you may need to add more than one set of brackets.
• When nesting brackets, Excel will always perform the calculation in the inner most
bracket FIRST!.

To ensure Excel performs the correct calculations,


ALWAYS use ( brackets)!!
FORMULA FLUB-UPS
• When a formula or function has been incorrectly setup you get an error value.
• Error Value: informs the user that some element of the formula is preventing Excel
from returning an anticipated value.
• When an error value shows up, you have to discover what caused the error and edit
the formula in the worksheet.
• The following are examples of error values and the common causes.
WHAT SHOWS UP IN WHAT’S GOING ON HERE??
THE CELL
#DIV/0! Appears when the formula cells for division by a cell that either
contains the value 0 or it empty
#NAME? Appears when the formula refers to a cell or range name that doesn’t
exist in the worksheet
#NULL! Appears most often when you insert a space to separate cell references
used as arguments for functions
#NUM! Appears when Excel encounters a problem with a number in the
formula
#REF! Appears when Excel encounters an invalid cell reference
#VALUE! Appears when you use the wrong type of argument or operator in a
function
CHARTS

• Although, Excel automates almost the entire process of charting worksheet data,
sometimes you tell Excel which data set you want graphed in the x-axis and which in
the y-axis.

Value Axis
Y-AXIS

X- AXIS
Category Axis
• Worksheet values represented graphically in the chart remain dynamically linked
to the chart so that, should you make a change to one or more of the charted
values in the worksheet, Excel automatically updates the affected part of the
chart to suit.
CHARTS
TO CREATE A CHART IN EXCEL:

Excel makes the process of creating a new chart in a worksheet as painless as


possible:
1. Click a cell in the table of data you want graphed, or, when you
only want to graph a part of the data in a table, select the cell range,
including headings and data.

2. On the Insert tab of the Ribbon, click the button in the Charts group
for the type of chart you want to create: Column, Line, Pie, Bar, Area,
Scatter, or Other Charts.

3. Click the thumbnail with the style of chart you want to create.
Style options within a chart type include 2-D, 3-D, Cylinder, Cone,
Pyramid, Surface, Doughnut, Bubble, or Radar style to name a few.

As soon as you click one of the styles in the drop-down gallery, Excel immediately
creates a free-floating chart (called an embedded chart) on the same
worksheet as the table of data the chart represents graphically.
THE CELL NAME GAME
PROTECTING YOUR WORK
CREATING AND USING MACROS
USING ADD-INS
THE CELL NAME GAME

• By assigning descriptive names to cells ,you can go a long way toward keeping on top of
the location of important information in a worksheet.

• When assigning names to a cell , you need to follow a few guidelines:


✓ Cell names must begin with a letter of the alphabet, not a number.
For example, instead of 01Profit, use Profit01.
✓ Cell names cannot contain spaces.
Instead of a space, use the underscore (Shift+hyphen) to tie the parts of the name
together. For example, instead of Profit 01, use Profit_01.
✓ Cell names cannot correspond to cell coordinates in the worksheet.
For example, you can’t name a cell Q1 because this is a valid cell coordinate. Instead,
use something like Q1_sales.

• The advantages of naming a cell are numerous:


• Can help identify a specific section you want to print or use in another program
• After naming a cell you can use this name with the Go To feature
THE CELL NAME GAME

To name a cell or cell range in a worksheet:

1. Select the cell or cell range that you want to name.

2. Click the cell address for the current cell that appears in the Name Box on the far
left of the Formula bar.
Excel selects the cell address in the Name Box.

3. Type the name for the selected cell or cell range in the Name Box. When typing
the range name, you must follow Excel’s naming conventions.

4. Press Enter.

Once you name a cell and you want to access that particular cell and its contents, you
can use Go To Feature:
By Pressing Ctrl+G OR F5
THE CELL NAME GAME

A construction worker completed 13 hours of work at a rate of


K3.50/hour. Calculate his weekly earnings using the formula
WAGES = HOURS * RATE.

Cell names are not only a great way to identify and find
cells and cell ranges in your spreadsheet, but they’re
also a great way to make out the purpose of your
formulas.
PROTECTING YOUR WORK

• After you have finalized a worksheet by checking out its formulas and proofing its
text, you often want to guard it against any unplanned changes by protecting the
document.

• To Protect Worksheet from any changes do the following:


1. Go to the Review tab and under the Changes group click the Protect Sheet
command button.
2. The Protect Sheet dialog box opens up; to ensure that your worksheet is
completely protected from all changes uncheck all the check boxes and add a
password.
3. Click the OK button to close the dialog box.

• To Protect the entire workbook, under the Changes group you select the Protect
Workbook command button and enter a password to ensure that the layout of the
worksheets in the workbook are further protected.
PROTECTING YOUR WORK
• Usually though, the idea is not to prevent all changes but to prevent changes
from happening in certain areas of the worksheet.

• For example, in a BOQ you will want to protect all the cells that contain
headings and formulas, but allow changes in all the cells where you need to
enter amounts (rates) and quantities.

• Each cell in a workbook can be specifically locked or unlocked.

• Before putting protection on the entire Worksheet or Workbook, do the


following:
1. Select the cell or cells that are to be unlocked for editing, and right click
2. From the drop down list, select Format Cells
3. Go to the last tab, the Protection tab
4. Click “Locked” check box to remove its check mark.

Now, go through the steps described in the previous slide to protect the
entire worksheet or workbook.
AUTOMATING COMMANDS WITH MACROS
• Macros are used to speed up the process of performing routines tasks within
Excel; a recording of complex command sequences that can be later
performed by specific key combinations.
• Before we can record new macros, we need to add an optional Develop tab to
the Ribbon. This tab contains its own Record Macro command button along
with other useful command buttons.

To Add the Developer Tab:


1. Select the File tab and select Options to open up the Excel Options dialog
box.
2. Click the Customize Ribbon tab and under the Main Tabs (right side of dialog
box) check the box beside Developer.
3. Click OK to exit the dialog box.

The Developer Tab should now be added to the Ribbon. It has 5 Groups – Code, Add-Ins,
Controls, XML, and Modify.
AUTOMATING COMMANDS WITH MACROS

• The macro recorder doesn’t record the keystrokes or mouse actions that you take to
accomplish an action – only the VBA code required to perform the action itself.

• When Recording Macros, you make the following 3 important decisions:

• Where you save the Macro


• The Macro Name
• The Macro’s Shortcut Key

• The macros that you create with the Macro Recorder can be stored in a variety of
ways:
• In the current workbook
• In a new workbook
• In a special, globally available Personal Macro Workbook (PERSONAL.XLSB)

• Macros recorded as part of your Personal Macro Workbook can run from ANY
workbook that you open; those macros recorded as part of the current workbook or a
new workbook can only run when the workbook in which they were recorded is open
in excel.
AUTOMATING COMMANDS WITH MACROS

• When naming macros, use the same naming guidelines which were discussed
earlier for naming cells.

• When assigning a shortcut keystroke to the macro, you can assign:

✓ The Ctrl key plus a letter from A to Z, as in Ctrl+Q


✓ Ctrl+Shift and a letter from A to Z, as in Ctrl+Shift+Q

You cannot, however, assign the Ctrl key plus a punctuation or


number key
(such as Ctrl+1 or Ctrl+/ ) to your macro.
AUTOMATING COMMANDS WITH MACROS
STEPS TO RECORD A MACRO:

1. Open the Excel workbook that contains the worksheet data or chart
you want your macro to work with.
2. Go to the Developer Tab and Click the Record Macro button. The Record Macro dialog
box will open.
3. Replace the temporary name by entering your name for the macro.
AUTOMATING COMMANDS WITH MACROS
STEPS TO RECORD A MACRO:

3. Replace the temporary name by entering your name for the macro.
4. Click the Shortcut key textbox and enter the letter of the alphabet that you want to
assign to the macro. (Use the Shift button as well)
5. Save your macro; click the Personal Macro Workbook, New Workbook or This
Workbook option on the Store Macro list.
6. Click the Description box and insert a brief description of the purpose of the macro.
7. Click OK to close the Macro dialog box.
8. Select the cells, enter the data, and choose the Excel commands required to perform
the tasks that you want recorded just as you normally would in creating or editing the
current worksheet, using the keyboard, the mouse, or a combination of the two.
9. Click the Stop Recording button on the Status bar or Developer tab on the Ribbon.

* Step 8 is where the actual recording of the macro takes place


AUTOMATING COMMANDS WITH MACROS

TO RUN A MACRO:

1. If you assigned a shortcut key, simply press the keystrokes you assigned to the macro .
2. If you did not assign a shortcut key:
• Go to the Developer tab and under the Code group click Macros to open the
Macro dialog box
• Select the Macro and click RUN
USE ADD-INS

• Add-in programs are small modules that extend the power of Excel by giving
you access to a wide array of features and calculating functions which are
not otherwise offed in the program.
• There are 3 types of Add-Ins:
• Built-In Add-Ins (available when Excel is installed)

• Add-ins that can be downloaded for Excel from the MS Office Website
(http://www.office.microsoft.com)
• Add-ins developed by third-party vendors for Excel 2010 (can be
purchased)

• When Excel is first installed, the built-in add-in programs included in Excel
are not loaded and cannot be used.
USE ADD-INS

• To Load a Built-In Add-In Program for Excel:

1. Go to the File tab and select Options to Open up the Excel Options dialog box.
2. Select the Add-Ins tab (on the left side of the panel)
3. In the Manage drop-down list, ensure that “Excel Add-Ins” is selected and
press the Go button to open the Add-Ins Dialog Box.
4. Click the check boxes for each add-inn program that you want loaded in the
Add-Ins Available list box and click t he OK button.
5. Click the OK button in each alert dialog box to install its add-in.
USE ADD-INS

The Analysis ToolPak is an Excel built-in add-in program which provides data
analysis for statistical and engineering analysis.

Activate this add-in.

• After activation, Excel places command buttons for the add-ins in the
Ribbon under a tab.
• If you never use a particular add-in you have loaded, you can unload it
(following the procedure above) in order to free up some computer
memory.
IN SUMMARY

QUESTIONS
TUTORIALS
 See me tomorrow and get Tutorial 1.
 Other Tutorial Exercises will be given
for your own learning.

You might also like