Transcript Slide 1

Lynn Mann
July 17, 2008
For audio call Toll Free 1-888-886-3951
and use PIN/code 907705
•
•
•
•
•
•
•
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.
 Sequencing
using AutoFill, Custom Lists
 Creating templates
 Managing Formatting: Conditional Formatting:
Highlight Cells, Icon Sets, Data Bars
 Managing Viewing: Freeze Panes, Split Screen
 Managing Printing: Page Breaks, Set Print Area,
Repeating Rows/Columns
 Managing Data: Sort and Filter, Importing and
Exporting
Using AutoFill saves valuable time
 Starting value(s)


Type
Default Increment
Time
Hour
Date
Day
Days of the Week
Day
Months of the Year
Month
Formula
Cell
Numerical (two values needed)
Difference between the starting values
Highlight cell(s)
Move mouse to bottom right corner,
click and hold
pulling over cells you want to fill with the sequence



Click on the Office Button
Click on Excel Options
Select Popular on the menu list
◦ Click on Edit Custom Lists
 Type your custom list separated by commas or enter, click
Add
 Select cells to create list, click Add
If you work with a design that you reuse then create a template to be able
to use without modifying or possibly corrupting an existing file.


Beginning with an existing file, click on the Office Button
, either
place your cursor over Save As (then click on Other Formats) or click
on Save As. Select either Excel Template (.xltx) or Excel MacroEnabled Template (.xlmx) depending on if your template includes
macros – do not change the default template location. After you have
renamed/saved the file, make modifications to remove any specific
information. Leave all formatting, formulas, labels, and macros. Save
the completed template.
Beginning with a blank spreadsheet, create all formatting,
labels/headings, formulas, and macros. Save the file using same
directions as above.
Conditional formatting is useful to analyze numerical or textual data
visually

On the Home tab, in the Styles group, click Conditional
Formatting icon

You’ll have several options to choose depending on how you
want to display your conditional formatting: Highlight Cells
Rules, Top/Bottom Rules, Data Bars, Color Scheme, and Icon
Sets
Let’s run through a few of these choices.
Highlighting cells with specific
data allows analysis of data
visually and quickly.



On the Conditional Formatting
menu, place the cursor on
Highlight Cells Rules, a
submenu will appear.
Select the rule which you want
to apply or select More Rules...
In the dialog box, select the
test value and the formatting.
Icon Sets are fun visual aids to interpolate
data.
 On the Conditional Formatting menu,
place your cursor on Icon Set, a
submenu will appear.
 If the default settings for the icon
division (when the icon changes) is
acceptable then click on the Icon Set
you want to use.
 If the icon divisions need to be
customized, on the submenu move your
cursor to More Rules…
◦ Select the Rule Type
◦ Edit the Rule Description

Note: Feature not available in previous
versions of Excel
To use Data Bars, the steps are
the same as Icon Sets accept
select Data Bars on the
Conditional Formatting menu.
 Select Data Bars
 Select color
 Modify rules if necessary

Note: Feature not available in
previous versions of Excel
Many times data flows off the viewing area but when
scrolling to see the extended data we can’t see data
that helps the visual data have meaning such as
column and row headings.
Freezing Panes and Splitting the screen can assist with
viewing data.
Note: Be sure Workbook View (on the View tab) in set
to Normal otherwise you will not have these features.
Freezing Panes allows the user the scroll through the data yet hold
some of the data frozen in the viewing area.

To freeze either the first row or column then on the View tab, in
the Window group, click on Freeze Panes icon


Select either Freeze First Row or Freeze First Column

To freeze a different area then activate the cell where you want
the above rows and the columns to the left to freeze, then select
Freeze Panes on the Freeze Panes menu.
Sometimes you’ll need to leave some data on your screen and
view other data in another portion of your workbook. Splitting the
screen will allow you to do this.

On the View tab, in the Windows group click on Split icon

You’ll see your viewing screen split into four parts.
Move the split bar to the desired placement by clicking and
dragging.
To remove the split either click on the Split button again to click
and hold the split bar and drag it to the edge of the screen.


We have many options when printing our workbook:
Orientation, Margins, Page Breaks, Print Area,
Print Titles, etc.
Let’s look at a couple of these options.


To insert page break, click on the column to insert
the page break to the left or click on the row to
insert the break above or click on a cell to insert
multiple page breaks both above the cell and to
the left.
To change/view current page breaks, click on View
tab, in the Workbook View group, click Page
Break Preview icon
. Drag the breaks to the
preferred position.

Highlight the area to print
On the Page Layout tab, in the Page Setup group click
on Print Area
.

Click on Set Print Area

Click on the Office Button

.
, click on Print
choose printing options (i.e. number of copies, printer)

On the Page Layout tab, in the Page Setup group,
click on Print Titles
.

Select which rows and/or column you want to
repeat, either type or use the select button



Highlight information to be sorted
On the Data tab, in the Sort & Filter group click
either
for ascending or for descending
default sort. If you need to sort using more than
one criteria click
In the Sort dialog box, select your options
including if you need to sort by a custom list
previously created
Filtering allows us to view specific data without deleting data we
want to keep but don’t want to view at the moment



Highlight the range of data, on the Data tab, in the
Sort & Filter group, click on Filter icon
. Arrows appear next
to each of the column headings. (Or convert your data to a
table)
For each of the headings which you want to filter, click on the
arrow and select only the data you want to view
To turn off filtering, either click on the arrows and then select the
box next to (Select all)
or click Filter icon to deselect
filtering option.
There are several ways Excel can import data from
another type of format into Excel’s spreadsheet
format: from Microsoft Access, text file, the
Internet (Web), SQL Server, Analysis Service,
XML, Data Connection (OLEDB - Object Linking
and Embedding, Database), Microsoft Query
(ODBC - Open Database Connectivity).
Let’s look at the most popular, text file and web.






On the Data tab, in the Get External Data group, click From Text icon
.
Select the file you want to import, click Import. The Import Wizard will walk you
through the steps to import the data correctly.
Wizard Step 1: Delimited or Fixed data type. The wizard will try to determine
the type of your data but if it chooses incorrectly you can manually change the
type.
Wizard Step 2: Move, Create, Delete column breaks. The wizard will try to
determine the column breaks of your data but if it chooses incorrectly you can
change the column breaks either moving, creating or deleting the breaks.
Wizard Step 3: Formatting of each column. You can change the formatting of
each of the columns being imported here at the wizard phase or after the data
has been imported. You can also decide to import the column or skip importing
certain columns.
Final step: Location of the imported data either in the existing sheet (starting
with a specific cell) or in a new sheet

On the Data tab, in the Get External Data group, click
on From Web icon




.
Type in the URL of the website of the information you want
to import.
Select the areas by clicking on the yellow arrows when
selected there will be a green checkmark .
When all areas to be imported are selected click Import
button.
To change the options of refresh rate, click on Connection
then Properties.

To export data is actually saving the data as an
importable format usually either text or comma
delimited.

Click on the Office Button , click on Save As,
choose either Text (Tab delimited) .txt or CSV
(Comma delimited) .csv format using the dropdown list.
Lynn Mann
[email protected]
951-487-3524
http://tinyurl.com/62t3x4
For upcoming desktop seminars and links to recently
archived seminars, check the @ONE Web site at:
http://www.cccone.org/seminars/index.php