advance excel workshop notes
TRANSCRIPT
-
7/29/2019 Advance Excel Workshop Notes
1/12
Ragaas
Education & Training Services
-
7/29/2019 Advance Excel Workshop Notes
2/12
ADVANCE EXCEL 2007
RAGGAS EDUCATION & TRAINING SERVICES
2
Introduction of MS Excel
Microsoft Excel is program that helps you analyze
and organize data. The most common use for Excel is creating Spreadsheets.
Spreadsheets can organize data such as monthly expenses or sales data and
perform various calculations such as totaling your expenses or indicating the
highest producing sales person. Calculations in Excel occur automatically so if
data is changed, totals will recalculate.
Advantages of MS Excel
Import, organize, and explore massive data sets within significantly
expanded spreadsheets.
Use the completely redesigned charting engine in Office Excel 2007 to
communicate your analysis in professional-looking charts.
Enjoy improved and powerful support for working with tables.
Create and work with interactive PivotTable views
Office Excel 2007 features the Microsoft Office Fluent user interface to help
you find powerful tools when you need them.
Steps to open MS Excel
Go to Start Button
All Programs
Ms Office
Ms office 2007
Ms Excel 2007
-
7/29/2019 Advance Excel Workshop Notes
3/12
ADVANCE EXCEL 2007
RAGGAS EDUCATION & TRAINING SERVICES
3
Look of MS Excel
The Ribbon
The Ribbon is designed to help you quickly find the commands that you need to
complete a task.
Microsoft Office Button
Here you will find the same basic commands available in earlier releases of
Microsoft Office to open, save, and print your file.
Sheet Management in Excel 2007
Total Rows in a Sheet 2007
Total column in a Sheet 2007
-
7/29/2019 Advance Excel Workshop Notes
4/12
ADVANCE EXCEL 2007
RAGGAS EDUCATION & TRAINING SERVICES
4
Quick Access Toolbar
The place to keep the items that you not only need to access quickly but want to
be immediately available regardless of which of the Ribbon's tabs you're working
on.
Formula Bar
A place where you can enter or view formulas or text.
Expand Formula Bar Button
This button allows you to expand the formula bar.
Worksheet Navigation Tabs
By default, every workbook has 3 sheets. You are able to navigate the sheets by
clicking on the sheet tab.
Insert Worksheet Button
Click the Insert New Worksheet button to insert a new worksheet in your
workbook.
Normal View
This is the normal view for working on a spreadsheet in Excel.
Page Layout View
View the document as it will appear on the printed page.
Page Break Preview
View a preview of where pages will break when the document is printed.
Zoom Level
Allows you to quickly zoom in or zoom out of the worksheet.
-
7/29/2019 Advance Excel Workshop Notes
5/12
ADVANCE EXCEL 2007
RAGGAS EDUCATION & TRAINING SERVICES
5
Horizontal/Vertical Scroll
Allows you to scroll vertically/horizontally in the worksheet.
Active Cell
In an Excel worksheet, the active cell is the cell with the black border. Data can only be entered
into the active cell.
Row
Rows run horizontally in an Excel worksheet. They are identified by a number in the row header.
Row
Columns are a fundamental part of any spreadsheet program such as Excel. Columns runvertically in a spreadsheet and help to identify the location of data.
Worksheets / Spreadsheets
Workbook
-
7/29/2019 Advance Excel Workshop Notes
6/12
ADVANCE EXCEL 2007
RAGGAS EDUCATION & TRAINING SERVICES
6
Name Box
The Name Box displays the cell reference of the active cell. It will also show the name assigned
to a cell or range of cells.
Mark sheet in MS Excel
Conditional Formatting in MS Excel
UseExcel conditional formattingto set rules for cell
formatting based on cell value or another cell.
Procedure to used Conditional Formatting
Name Formula Description of Formula
Total =SUM(B5:G5) B5:G5 indicates marks of
each subject
Average =AVERAGE(B5:G5)B5:G5 indicates marks of
each subject
Grade
=IF(I5>=75,"DISTINCTION",
IF(I5>=60,"A",IF(I5>=45,"B",IF(I5>=45,"B",
IF(I5>=35,"C","FAIL")))))
I5 indicates Average
Class
=IF(I5>=75,"DISTINCTION",
IF(I5>=60,"FIRST",
IF(I5>=45,"SECOND",
IF(I5>=35,"PASS","FAIL"))))
I5 indicates Average
Result =IF(I5>=35,"PASS","FAIL") I5 indicates Average
-
7/29/2019 Advance Excel Workshop Notes
7/12
ADVANCE EXCEL 2007
RAGGAS EDUCATION & TRAINING SERVICES
7
1. In the Home tab, Styles group, click on Conditional Formatting.
2. Select New Rule.
3. The New Formatting Rule window will open.
4. Click Use a formula to determine which cells to format.
5. The New Formatting Rule window will now look like this:
6. In the edit box labeled Format values where this formulais true, enter = followed by a formula referencing the
other cell.
In this example the formula: =B1
-
7/29/2019 Advance Excel Workshop Notes
8/12
ADVANCE EXCEL 2007
RAGGAS EDUCATION & TRAINING SERVICES
8
2. Select Clear Rules & Select open which you want.
Pivot Table in MS ExcelA Pivot Table is way to present information in a report format.
Procedure to convert data in Pivot Table
Select all the Table>Click on Insert Tab >Click on
Pivot Table>
In the pivot table field list.
Choose field and dragfield
in between areas
Close pivot table field list.
-
7/29/2019 Advance Excel Workshop Notes
9/12
ADVANCE EXCEL 2007
RAGGAS EDUCATION & TRAINING SERVICES
9
Chart in MS ExcelUsea chart whenever you want to visualize your data, making
it clear and simple
First Select those data which you want to
convert in the Chart. Then to Open the Chart
Click on Insert>In the Group
Illustrations>Click on Chart option.
& Select Chart Type.
After Creating Charts Select the Chart &
By Using the Design, Layout & Format Tab
Enhance chart look.
Functions in MS Excel
Lookup FunctionIn Excel, the Lookup function returns a value from a range (one row or one
column) or from an array.
-
7/29/2019 Advance Excel Workshop Notes
10/12
ADVANCE EXCEL 2007
RAGGAS EDUCATION & TRAINING SERVICES
10
Example
=lookup(F-005,A1:A20, C1:C20)= Office Assistance.
To open above window first open those table in which you want to apply Lookup
Function. The select those cells where you want the Result. Then type =lookup
and click on Insert Function Button. Then Fill up Above Details.
Financial Function
PMT Function:- In Excel, the Pmt function returns the payment amount for a loan basedon an interest rate and a constant payment schedule.
Example
PV.. 8,00,000
NPER.. 1
RATE.. 6%
PMT.. ?
Insert Function Button
-
7/29/2019 Advance Excel Workshop Notes
11/12
ADVANCE EXCEL 2007
RAGGAS EDUCATION & TRAINING SERVICES
11
PMT( interest_rate, number_payments, PV, FV, Type )
=PMT(B3/12,B2*12,-B1)
Some Shortcut Keys in MS Excel
F1 Displays the Microsoft Office Excel Help task pane.
F2 Edits the active cell and positions the insertion point at the end of
the cell contents.
F6 Switches between the worksheet, Ribbon, task pane, and Zoom
CTRL+1 Displays the Format Cells dialog box.
CTRL+2 Applies or removes bold formatting.
CTRL+3 Applies or removes italic formatting.
CTRL+SHFT+( Unhides any hidden rows within the selection.
CTRL+SHFT+) Unhides any hidden columns within the selection.
CTRL+SHFT+& Applies the outline border to the selected cells.
CTRL+SHFT_ Removes the outline border from the selected cells.
CTRL+A Selects the entire worksheet
CTRL+U Applies or removes underlining.
CTRL+P Displays the Print dialog box.
CTRL+N Creates a new, blank workbook.
-
7/29/2019 Advance Excel Workshop Notes
12/12
ADVANCE EXCEL 2007
RAGGAS EDUCATION & TRAINING SERVICES
controls.
PAGE DOWN Moves one screen down in a worksheet
PAGE UP Moves one screen up in a worksheet
Topics
Introduction of MS Excel
Basic MS Excel
Conditional Formatting Pivot Table
Chart
Linking
Cell References
formulas
Financial Related Formulas
Logical Related Formulas
Lookup Related Formulas
Text Related Formulas Date & Time Related Formulas
IF & Nested IF Functions
Other Formulas (Depreciation, G/P, N/P.)
Advance Filter
Data Validation
Scenario
Goal Seek
Data Table
Macro
Shortcut Keys