CEB201 Lecture1
CEB201 Lecture1
CEB201 Lecture1
“What Do I
Want To
Do?”
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!!
• 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
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
• 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
SIGN OPERATION
+ (Plus Sign) Addition
* (Asterisk) Multiplication
/ (Slash) Division
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
• 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.
• 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.
• 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
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
• 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!.
• 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:
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.
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
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 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.
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.
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.
• 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.
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.
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
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.
• 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.