Transcript Slide 1
Lynn Mann
July 24, 2008 For audio call Toll Free 1 888-886-3951 and use PIN/code
114310
• • • • • • • Maximize your CCC Confer window.
Mute your phone (*6) if you have visitors or noise in your office.
Please note phone audio may be in presenter-only mode.
Ask questions over the phone when the presenter prompts. Ask questions throughout presentation via the chat window.
Turn on or off Closed Captioning by clicking on the icon.
Save the presentation or chat by clicking on the icon.
Manage your data with built-in functions, or create your own formulas
Use data from multiple sheets in your analyses
Summarize data with PivotTables
Create informative charts and embed them into reports and presentations
Reduce repetitive work using Macros
There are about 340 built-in functions categorized in ◦ ◦ ◦ ◦ ◦ the function library by type of function Financial Date & Time Math & Trig Database More Functions ▫ Logical ▫ Lookup & Reference ▫ Text ◦ Statistical ◦ Engineering ◦ Cube ◦ Information
Financial – FV, PMT Logical – AND, IF Date & Time – DATE, NOW Lookup & Reference – HYPERLINK, VLOOKUP Math & Trig – SUM, PRODUCT Text – CONCATENATE, PROPER Statistical – AVERAGE, STDEV Engineering – CONVERT
+ Addition - Subtraction * Multiplication / Division Combination of built-in and/or user formulas
Programming is required
◦ ◦ 3-D Referencing within a workbook Used in formula, syntax is =‘
WorksheetRange
’!
CellRange Ex:
‘Quarter1’!B7 or ‘Quarter1:Quarter4’!H2:H5 ◦ Grouping Sheets Click the first sheet to group, hold Shift (for adjacent) then click on the last sheet you want to group or Ctrl (for nonadjacent) click on each of the sheets you want to add to the group.
◦ ◦ ◦ ◦ Hyperlinking within a workbook On the
Insert
tab, in the
Links
group, click
Hyperlink
.
Select
Place in this Document
in the
Link to:
menu Type the cell and select the sheet of the linked location Type the
Text to Display
, click
OK
.
A PivotTable is an interactive table that will allow us to group and summarize data quickly and easily.
Click in the table or select the range of data On the
Insert
tab, click the
PivotTable
button Click the Select a table or range option button and verify the reference in the
Table/Range
box Choose either
New Worksheet
or
Existing Worksheet
select a starting cell and Click
OK
Select the fields you want to add to the PivotTable by clicking on the check boxes or drag fields to the box in the layout section
Charts allow us a visual representation of a set of data showing trends or relationships of data that are more difficult to see by looking at numbers.
Select the data source with the range of data you want to chart Click
Insert
tab, in the
Charts
group click a chart type, and select a chart subtype in the
Chart Gallery
.
On the
Chart Tools Design
tab, in the
Location
group, click
Move Chart
button to place the chart on it’s own worksheet
Each chart type will display data a specific way therefore it’s important to know which chart will display your information in a meaningful way.
Chart Type
Column Line Pie Bar Area XY (Scatter) Stock Surface Doughnut Bubble Radar
Description
Compares values from different categories. Values are indicated by the height of the columns.
Compares values from different categories. Values are indicated by the height of the line. Often used to show trends and changes over time.
Compare relative values of different categories to the whole. Values are indicated by the areas of the pie slices.
Compares values from different categories. Values are indicated by the length of the bars.
Compares values from different categories. Similar to the line chart except that areas under the line are filled with color.
Shows the patterns r relationship between two or more sets of values. Often used in scientific studies and statistical analyses.
Displays stock market data, including the high, low, opening, and closing prices of a stock.
Compares three sets of values in a three dimensional chart.
Compares relative values of different categories to the whole. Similar to pie chart except that it can display multiple sets of data.
Shows the patterns or relationship between two or more sets of values. Similar to XY (Scatter) chart except the size of the data marker is determined by a third value.
Compares a collection of values from several different data sets.
One of the many benefits of using Microsoft Office suite is that the applications work nicely with one another.
You can link an Excel chart from one workbook into PowerPoint presentation, Word document or another Excel workbook. Upon any changes in the source the chart will be updated.
A macro is a series of stored commands that can be run whenever you need to perform the recorded task.
If you have macros in your workbook you will need to save your workbook as Excel Macro-Enabled Workbook .xlmx
To add Developer tab to your Ribbon, click on Office button, Excel Options, on the Popular menu check Show Developer tab in the Ribbon, click OK.
Recording: Click the
Developer
button.
tab, in the
Code
group, (or on the
View
tab), click
Record Macro
Enter a name for the Macro, and specify the location to store the macro.
Specify a shortcut key, if you’d like.
Enter a description of the macro.
Click
OK
to begin recording.
Perform the tasks you want to automate.
Click
Stop Recording
button.
Running: Press the shortcut key you assigned the macro OR Click the
Developer
tab, in the
Code
group, click
Macro
Select the macro from the list of macros, and click
Run
button.
button.
Programming is required
Do one of the following: ◦ Open the workbook that contains the macro that you want to delete. ◦ If the macro that you want to delete is stored in the personal macro workbook (Personal.xlsb), and this workbook is hidden, do the following to unhide the workbook: On the
View
tab, in the
Window
group, click
Unhide
. Under
Unhide workbooks
, click PERSONAL, and then click
OK
.
On the
Developer Macros
. tab, in the
Code
group, (or on the
View
tab), click In the
Macros in
list, select the workbook that contains the macro that you want to delete. For example, click
This Workbook
. In the
Macro name
delete. box, click the name of the macro that you want to Click
Delete
.
Lynn Mann [email protected]
951-487-3524
http://tinyurl.com/5qrxg2
For upcoming desktop seminars and links to recently archived seminars, check the @ONE Web site at: