Excel 2002Lab 9 Customizing Excel
Objectives • Create custom worksheets. • Create custom styles. • Merge styles. • Apply conditional formatting. • Create custom toolbars.
Objectives • Create custom menus. • Create custom charts. • Create trendlines. • Share workbooks. • Create a workspace.
Creating a Custom Worksheet • Logo • Custom toolbars • Custom menus • Reusable templates
Concepts Overview • Custom Style Custom styles are created by modifying Excel's standard styles, by basing a style on an existing cell, or by creating a new style to combine a number of cell characteristics in one group.
Concepts Overview • Conditional Formatting Conditional formatting is applied to numbers that exceed or do not meet specified value criteria. • Custom Toolbar Custom toolbars collect frequently used buttons and commands into one easily accessible floating or docked toolbar.
Concepts Overview • Custom Menu Custom menus are created to place a group of commands in one functional area for easy access either to add to the functionality of Excel or to restrict users from certain commands by eliminating them from menus.
Concepts Overview • Custom Chart Custom charts are created from an existing chart by adding custom colors, borders, patterns, pictures, and more. • Trendline Trendliners show the general tendency of data by historically showing where the data series of a chart has been and where it could potentially go.
Concepts Overview • Shared Workbook Shared workbooks enable multiple users to work on the same workbook at the same time from different computers on a network.
Outline • Creating a Custom Worksheet • Adapting a Logo • Adapting a Worksheet • Creating Custom Styles • Entering Multiple Formulas in a Column • Merging Styles from One Worksheet to Another
Outline cont. • Creating a Custom Toolbar • Adding Buttons to an Existing Toolbar • Deleting Buttons from a Toolbar • Creating New Buttons for a Toolbar • Attaching a Macro to a New Button • Creating a New Toolbar • Adding Buttons to a New Toolbar • Attaching a Toolbar to a Worksheet
Outline cont. • Creating a Custom Menu • Adding a Menu to a Toolbar • Adding a Button to a Menu • Adding a New Menu to the Menu Bar • Verifying Menu and Toolbar Changes • Resetting Toolbars and Menus
Outline cont. • Creating Custom Charts • Customizing an Existing Chart • Adjusting Titles • Removing a Data Series • Formatting Data Items • Formatting the Y-Axis Units • Adding a Picture to Chart Walls • Adding a Custom Chart to the Chart Gallery
Outline cont. • Creating Custom Charts (continued) • Changing Axis Scaling • Filling a Column with a Picture • Setting the Gap between Data Points • Adjusting a Series • Adding a Data Table to a Chart • Creating a Trendline • Creating a Trendline with the TREND Function
Outline cont. • Sharing Workbooks • Adding Password Protection • Creating a Shared Workbook • Resolving Conflicting Changes • Creating a Workspace
Outline cont. • Concept Summary • Lab Review • Lab Exercises
New clip art Tab color Adapting a Worksheet
EXCEL Concept 1Custom Styles • Created by modifying Excel's standard style • Base a style on an existing one • Create a new style • Merged styles are styles taken from another open worksheet and then added to the current worksheet
Items That Can Be Adjusted to Create Custom Styles • Number • Alignment • Font • Border • Pattern • Protection
Formula added as a group to the column Entering Multiple Formulas in a Column
Merging Styles • Can add a style from another worksheet to the current worksheet • Make sure each style has a unique name
Applied to numbers that exceed or do not meet specified value criteria If condition is met, the format of the number changes to call attention to it Conditions: Between Not Between Equal to Not Equal to Greater Than or Equal to Less Than or Equal to Concept 2Conditional Formatting
Condition to test Applying Conditional Formatting
Concept 3Custom Toolbar • Can collect frequently used buttons & commands • Floating toolbar • Docked toolbar • Increases the ease of accessing commands in Excel
Buttons added to Format Toolbar Customize commands Adding Buttons to an Existing Toolbar
Creating New Buttons for a Toolbar • Select a command and drag to the toolbar • Right-click on the command to access the pop-up dialog box • Select Change Button Image to change the image on the button
Macros available to the worksheet Attaching a Macro
Creating a New Toolbar • A new toolbar can contain frequently used commands • Makes access to common functions easier
Attach Attaching a Toolbar to a Worksheet
Concept 4Custom Menu • Custom menus are created to place a group of commands in one functional area • Provides easy access • Can restrict users by eliminating certain commands from the menu
Adding a Menu to a Toolbar • Choose Tools/Customize • Open the Commands tab • Select New Menu • Drag new menu to the toolbar • Rename the menu
Other Custom Options • Can add a new button to a custom toolbar • Can add a new menu to the standard menu bar • Can save custom toolbar and menu settings to a special file called Excel.xlb • Can use with different workbooks • Can copy file to your laptop as a backup • Can reset back to default options
Creating Custom Charts In today's image-minded society, a good graph is priceless!
Concept 5Custom Charts • Created from existing charts • Add custom colors, borders, patterns and pictures • Can be saved in the Chart Gallery
Charting Terms • Tick Marks • Major or subdivision gridlines • These start at the axis and move across the chart • Data Table Values • Values from which the chart is created • Placed immediately below a chart • Values may be from a range of values in the worksheet
Extra legend labels No data Alignment Total listed as 5th qtr. Customizing an Existing ChartChanges Needed
Customizing Changes • Adjust title size, font, and placement • Remove a data series – eliminate incorrect data in the chart • Delete legend to streamline chart • Apply different colors & patterns to the data items • Format the axis units • Add a picture to chart walls
Name of custom chart Adding a Custom Chart to the Chart Gallery
Scale options Maximum value of Y Axis Changing Axis Scaling
Filling a Column with a Picture • Can place similar but different pictures on a column • Calls attention to the differences between the columns
Customizing a Chart • Set the gap between data points • Adjust a series • Make changes in the worksheet • Add a data table to a chart
Concept 6Trendlines • Trendlines show the general historical tendency of data • Shows where the data series of a chart has been and where it could go • Original representation and trendline appear on the same chart
Some Trendlines Types • Linear regression • Line generated by non-linear curve fitting techniques • Moving average
More on Trendlines • Trendlines attempt to show the overall future picture from the existing data • Can be created from 2-D charts • Column • Bar line • Area • Scatter