excel 2007 tutorials
TRANSCRIPT
Excel 2007 Tutorials - Getting Started
Inserting, deleting and renaming worksheets Automatically move the cell pointer in any
direction after pressing enter Change the Number of Recent Documents See headings as
you scroll around a report Freeze First Column Freeze Panes Based on
Selection Eliminating Typing Errors with Spell Check Customizing the Spell Check
Settings Adding words to a custom dictionary Using Find and Replace Worksheet
Basics Ribbons Customizing Quick Access Toolbar Create a Worksheet Navigating Formula
Bar Status Bar Options What's New in Excel 2007? New Worksheet Functions Worksheet
Tables Larger Worksheets Styles and Themes SmartArt Other Features Customizing the
Excel Menus and Toolbars Adding Macros and Links to Toolbars and Menus Rearrange
Toolbars and Menus Create your own toolbars and menus
In this tutorial you'll learn the following techniques:
Techniques of inserting, deleting and renaming worksheets
Automatically move the cell pointer in any direction after pressing enter, change
the number of Recent Documents, see headings as you scroll around a report,
freeze top row, first column or freeze panes based on selection.
Spell checking a worksheet can go a long way towards eliminating typos that get
by the program's AutoCorrect feature. Learn how to check spells, update
dictionary, use custom dictionary and more.
Find and Replace feature to quickly locate every occurrence of a specific string (a
series of characters) in a worksheet and then have Excel actually update the cells
that it finds with new text or numbers.
Parts of Excel's window, Ribbon user interface, shortcut menus, dialog boxes and
how to navigate Excel worksheets.
Overview of what's new and what's changed in Excel 2007.
Customizing the Excel toolbars and pulldown menus. Taking the time to get the
Excel toolbars and the pull-down menus the way you want them saves your
valuable time when creating and editing your spreadsheets.
Inserting, deleting and renaming worksheets
Renaming a worksheet
1. On the Sheet tab bar, right-click the sheet tab that you want to rename, and then
click Rename (as shown in figure below).
2. Select the current name, and then type the new name.
OR
1. Double click on the tab you wish to rename (as shown in figure below). The
worksheet name is highlighted, indicating that it is selected.
2. Enter a new name for the worksheet.
Press enter key. The new worksheet name will be displayed on the worksheet tab at the
bottom the screen.
Inserting a worksheet
By default, MS Excel provides three worksheets in a workbook, but you can insert
additional worksheets or delete them as needed. You can also change the number of
worksheets that appear by default in a new workbook.
To insert a new worksheet, do one of the following:
To insert a new worksheet in front of an existing (active) worksheet, select that
worksheet and then, on the Home tab, in the Cells group, click Insert, and then
click Insert Sheet as shown in figure below.
OR
If you want to insert a new sheet that is based on a custom template then
Right-click the sheet tab of a worksheet, and then click Insert.
Double-click the template for the type of sheet that you want.
OR
To quickly insert a new worksheet at the end of the existing worksheets, click the Insert
Worksheet tab at the bottom of the screen (see figure).
Deleting a worksheet
Right-click the sheet tab of a worksheet, and then click Delete OR
On the Home tab, in the Cells group, click Delete and then Delete Sheet
In confirmation message click Delete to permanently delete the worksheet and its data.
Automatically move the cell pointer in any direction after pressing enter
If you type a number and then press a direction arrow key, Excel will enter
the number and move the cell pointer in the direction of the arrow key. However, if you
are using the numeric keypad, it is much more convenient to use the Enter key on
the numeric keypad rather than the arrow keys. By default, Excel will move the cell
pointer down one cell when you type Enter. But you can change this behavior to
Excel automatically move the cell pointer to the next cell to the right after each entry.
You can have the cell pointer move in any direction after pressing Enter by changing the
Editing options from the Excel Options. To change the default move selection click
Office button and then Excel Options as shown in figure below.
In Excel Options click on Advanced, it will open the Advanced options for working with
Excel, go to the Editing Options and in After pressing Enter, move selection Direction
dropdown, choose Right, as shown in figure below.
Now the cursor will automatically move one cell to the right every time you press the
Enter key.
Change the Number of Recent Documents
The goal of this tutorial is: change how many documents are shown in the Recent
Document List.
You can increase the Recent Documents List (located at Office button menu, see figure
below) to your desired number of workbooks, you can select 0 to 50 numbers.
Go to Office button > Excel Options > Advanced, scroll down to the Display options and
change the value of Change this number of Recent Documents drop down as shown in
figure below.
Note: Fewer documents will be visible if the list does not fit on the screen.
See headings as you scroll around a report
Freeze Top Row
Goal: You have a spreadsheet with headings at the top, as shown in figure below. You
want to be able to scroll through the data and always see the headings.
You can use the Freeze Panes command on the View tab, in the Window group and then
click on Freeze Top Row.
A solid horizontal line will be drawn after rows 1. As you scroll down you will always be
able to see theheading rows, as shown in figure below.
Freeze First Column
Think, you have a spreadsheet with headings at the First left column, as shown in figure
below. You want to be able to scroll through the data and always see the headings.
You can use the Freeze Panes command on the View tab, in the Window group and then
click on Freeze First Column.
A solid vertical line will be drawn after first column. As you scroll right you will always be
able to see theheading column, as shown in figure below.
Freeze Panes Based on Selection
You have a spreadsheet with headings at the top and at first column, as shown in figure
below. You want to be able to scroll through the data and always see the left and top
headings.
You can use the Freeze Panes command on the View tab, in the Window group and then
click on Freeze Panes.
Place the cell pointer in cell B2, as shown in figure below, before you select the Freeze
Panes command. The Freeze Panes command will freeze all visible rows above the cell
pointer and all visible columns to the left of the cell pointer. By placing the cell pointer
in column B and row 2 (B2), you will freeze column A, and row 1.
A solid vertical and horizontal line will be drawn after column A and Row 1 . As you scroll
right or down you will always be able to see the heading column and rows, as shown in
figure above.
To turn off this feature go to View tab, in the Window group and then click on Unfreeze
Panes and select Unfreeze Panes.
Eliminating Typing Errors with Spell Check
Excel's Spell Check feature affords you a quick-andeasy way to check for any typos in
yourspreadsheets before sending them out for any kind of review. To spell
check a worksheet, click the Spelling button on the Standard toolbar, press F7, or
choose Tools > Spelling.
Excel then looks up each word in the Excel dictionary. If it does not find a word (as is
often the case with less-common last names, abbreviations, acronyms, and technical
terms), Excel selects the cell with the unknown spelling and then displays a
Spelling dialog box showing the unknown word in the Not inDictionary text box, along
with suggested correct spellings shown in a Suggestions list box.
You can then take any of the following actions to take care of the unknown word:
Select one of the words in the Suggestions list box and then click
the Change button to have Excel replace the unknown word with the selected
suggestion and continue spell checking the rest of theworksheet.
Select one of the words in the Suggestions list box and then click the Change All
button to have Excel replace all occurrences of the unknown word with the
selected suggestion throughout the entire worksheet and then continue spell
checking.
Click the Ignore Once button to let the misspelling slide just this once and
continue spell checkingthe rest of the worksheet.
Click the Ignore All button to ignore all occurrences of the unknown word in
the worksheet and continue spell checking.
Click the Add to Dictionary button to add the unknown word to a custom
dictionary so that Excel knows the word the next time you spell check that or any
other worksheet.
Click the AutoCorrect button to have Excel add the unknown word to
the AutoCorrect list with the selected suggestion as its automatic replacement.
Excel checks the spelling of the cells only in the current worksheet (not in all the sheets
in the workbook). If you want Excel to spell check another worksheet, you need to select
its sheet tab to make it active before you start the spell check. To spell check just a
portion of the worksheet, select the range or make a nonadjacent cell selection before
you start the spell check.
When Excel finishes checking the current worksheet or cell selection, the program
displays an alertdialog box that indicates that the spell checking is complete.
Customizing the Spell Check Settings
When you use the Spell Check feature, you can change certain spelling options to better
suit thespreadsheet that you are checking. To change the spelling options, click
the Options button at the bottom of the Spelling dialog box to open the Options dialog
box, shown in Figure below.
You can use the controls in the Options dialog box to change the following settings:
Dictionary Language:
Select a new dictionary language. (This option is especially useful if
your spreadsheetcontains British English spellings or French or Spanish terms.)
Add Words To:
Select another custom dictionary to which to add new terms.
Suggest from Main Dictionary Only:
Select this check box to have Excel use only the main dictionary when doing a spell
check(thus, ignoring all words that you add to the custom dictionary).
Ignore Words in UPPERCASE:
Select this check box to have Excel ignore acronyms in your spreadsheet that use all
capital letters.
Ignore Words with Numbers:
Clear this check box to have Excel flag unknown words that contain numbers.
Ignore Internet and File Addresses:
Clear this check box to have Excel let unknown words that contain URL and Mailto
addresses and file pathnames slide. (You know, stuff such as www. google.com and c:\
mydocuments\finance.)
AutoCorrect Options:
Click this button to open the AutoCorrect dialog box where you change the settings that
determine when certain words are automatically corrected as well as add new
automaticreplacements.
Note that Language Specific check boxes and dropdown lists on the right side of the
Options dialog boxremain grayed out until you select a dictionary in
the Dictionary Language drop-down list for German, Korean, Hebrew, or Arabic. Then
you can use them (depending on the language you select) to determine how their words
are treated during spell checking.
Adding words to a custom dictionary
Click the Add to Dictionary button in the Spelling dialog box to add unknown words to
a customdictionary. By default, Excel (as well as your other Microsoft applications, such
as Word) adds words to a custom dictionary file named CUSTOM.DIC. If you want, you can
create other specialized custom dictionaries just to use when spell checking particular
types of spreadsheets. To create a new customdictionary, follow these steps:
1. Begin spell checking your worksheet ( press F7 or click the Spelling button on the
Standard toolbar).
You can't start adding words to a new custom dictionary until you spell check
a worksheet and Excel starts flagging some unknown words.
2. As soon as Excel locates an unknown word in the Spelling dialog box that you
want to add to a new custom dictionary, click the Options button.
3. Click in the Add Word To drop-down list, replace the custom part of
the dictionary filename with a name of your own, and then click OK
or press Enter.
When editing the custom.dic filename to create a name for your new
custom dictionary, be sure not to delete the .dic filename extension. As soon as
you click OK or press Enter, Excel adds the unknown word to your new
custom dictionary.
4. Continue spell checking your worksheet, clicking the Add to Dictionary button
to add all unknown words that you want to be part of your new custom
dictionary.
After creating a custom dictionary, Excel automatically uses the words in
this dictionary as well as in theCUSTOM.DIC when spell checking your worksheets.
You can directly edit the words that you add to your custom dictionary with the
Windows Notepad text editor. Open the custom dictionary file (located in
the Proof folder on your hard drive) and then make any changes to the entries in this
file by saving your changes with Notepad's File > Save command.
Using Find and Replace
Excel's Find and Replace feature enables you to easily update the contents of a
single worksheet or all the worksheets in a workbook on either a case-by-case basis or
globally. To make quick and easy editing changes with this feature, follow these steps:
1. To perform a search and replace through the entire worksheet, select a single
cell. To restrict the search-and-replace operation to a specific cell range or non-
adjacent selection, select all the cells to be edited.
2. Choose Edit > Replace or press Ctrl+H to open the Find and Replace dialog
box.
3. Click the Options button to expand the Replace tab shown below.
4. Type the search string that you want to locate in the Find What drop-down list
box and specify any formatting to be searched by clicking its Format button.
When entering the search string, you can use the question mark (?) or asterisk
(*) wildcards to stand for any characters that you are unsure of. Use the question
mark to stand for a single character as in Jos?ph, which matches either Joseph.
Use the asterisk to stand for multiple characters as in 9*1, which locates 91,
94901, or even 9553 1st Street. To search for a wildcard character, precede the
character with a tilde (~), as in ~*2.5, to locate formulas that are multiplied by
the number 2.5. (The asterisk is the multiplication operator in Excel.)
If the cell holding the search string that you are looking for is formatted in a
particular way, you can narrow the search by specifying what formatting to
search for. When you click the Formatbutton, Excel opens a Find Format dialog
box with the same tabs and options as the standardFormat Cells dialog box.
Select the formatting that you want to search for in this dialog box and then
click OK.
5. Type the replacement string in the Replace With drop-down list box and specify
any formatting to be added to the replacement string by clicking
its Format button.
6. Select any additional options you want:
Within:
Select the Workbook setting to search all the worksheets within a workbook.
Search:
Change this setting from By Rows to By Columns to search down the columns
and across the rows rather that across the rows and then down the columns.
Look In:
By default, Excel selects Formulas for this option to look for the search string in
the contents of each cell as it's displayed on the Formula bar. To have Excel
search for the string in among the values displayed in the cells themselves,
select Values on this dropdown list. To have the program look for the search
string only in the commentsadded to the cells, select Comments on this drop-
down list.
Match Case:
Find occurrences of the search string only when it matches the case that you
entered.
Match Entire Cell Contents:
Find occurrences of the search string only when it matches the entire cell entry.
By default, Excel considers any occurrence of the search string to be a match -
even when it occurs as part of another part of the cell entry. This means that
when you search for 25, Excel considers cells containing 25, 15.25, 25 Main
Street, and 250,000 as matches. Select the Match Entire Cell Contents check
box to match only complete occurrences of your search string.
7. Click the Find Next button to locate the first occurrence of the search string.
When Excel finds an occurrence, click the <code.replace< code="">button to
replace the first occurrence with the replacement string or the Find Next button
again to skip this occurrence.
Using the Find Next and Replace buttons to search and replace on a case-by-
case basis is by far the safest way to use the Find and Replace feature. If you
are certain (really certain) that you won't mess anything up by replacing all
occurrences throughout the spreadsheet, click the Replace All button to have
Excel make the replacements globally without stopping to show you which cells
are updated.
</code.replace<>
8. When you finish replacing entries on a case-bycase basis, click the Close button
to close the Find and Replace dialog box.
9. When you finish replacing entries on a case-bycase basis, click the Close button
to close the Find and Replace dialog box.
Note that if you globally replace the search string in the worksheet, Excel automatically
closes the Find and Replace dialog box when it finishes replacing the last match.
Be clear about the difference between the Formulas and Values Look In options in the
expanded Find and Replace dialog box. When, for example, the
default Formulas option is selected and you enter 15 the search string, Excel looks for
these two digits only in text entries and within the contents of formulas as they appear
on the Formula bar (as in =15+A4). To have the program find the digits 15 when directly
entered in a cell or returned as the result of a formula calculation as actually displayed
in the cells of the worksheet (as when the formula =A2-A3 returns 15 to a cell), you must
select Values as the Look In option before you conduct the search.
Worksheet Basics
Excel is the world's most widely-used spreadsheet program, and is part of the Microsoft
Office suite. Other spreadsheet programs are available, but Excel is by far the most
popular and has become the world standard.
Much of the appeal of Excel is due to the fact that it's so versatile. Excel's forte, of
course, is performing numerical calculations, but Excel is also very useful for non-
numerical applications. Here are just a few of the uses for Excel:
Number crunching: Create budgets, analyze survey results, and perform just
about any type of financial analysis you can think of.
Creating charts: Create a wide variety of highly customizable charts.
Organizing lists: Use the row-and-column layout to store lists efficiently.
Accessing other data: Import data from a wide variety of sources.
Creating graphics and diagrams: Use Shapes and the new SmartArt to create
professional-looking diagrams.
Automating complex tasks: Perform a tedious task with a single mouse click with
Excel's macro capabilities.
This tutorial covers the very basic task like: Create, Navigate, Add Information, Move
Data, ManageWorksheets and Workbooks. And also cover the basic and smart
formatting tricks and printingtechniques.
Ribbons
The most dramatic change in Office 2007 is the new user interface. Traditional menus
and toolbars are gone, and they’ve been replaced with the Ribbon. Office 2007 is the
first software in history to use this new interface, and it remains to be seen whether it
will catch on and replace traditional menus andtoolbars.
The Tabs of the RibbonQuick overview of what each tab provides:
Home
Includes some of the most commonly used buttons, like those for cutting and pasting
information, formatting your data, and hunting down important bits of information
with search tools.
Insert
Lets you add special ingredients like tables, graphics, charts, and hyperlinks.
Page Layout
Is all about getting your worksheet ready for the printer. You can tweak margins, paper
orientation, and other page settings.
Formulas
Are mathematical instructions that you use to perform calculations. This tab helps you
build super-smart formulas and resolve mind-bending errors.
Data
Lets you get information from an outside data source (database) so you can analyze it
in Excel. It also includes tools for dealing with large amounts of information, like sorting,
filtering, and subgrouping.
Review
Includes the familiar Office proofing tools (like the spell checker). It also has buttons
that let you add comments to a worksheet and manage revisions.
View
lets you switch on and off a variety of viewing options. It also lets you pull off a few
fancy tricks if you want to view several separate Excel spreadsheet files at the same
time.
Add-Ins
This tab is visible only if you’ve loaded a workbook or add-in that customizes the menu
or toolbars. Because menus and toolbars are no longer available in Excel 2007, these
customizations appear in the Add-Ins tab.
Ribbon Collapse
Do you want to use every square inch of screen space for your cells? You can collapse
the ribbon by double-clicking any tab. Click a tab to pop it open temporarily, or by right
mouse click on a tab then Minimize the Ribbon or double-click a tab to bring the ribbon
back for good. And if you want to perform the same trick without raising your fingers
from the keyboard, you can use the shortcut key Ctrl+F1.
Contextual Tabs
Excel 2007 also includes contextual tabs. Whenever an object such as a
chart, picture or a table is selected, specific tools for working with that object are
available in the Ribbon. Following figure shows the contextual tab that appears when a
chart is selected.
Customizing Quick Access Toolbar
In previous versions of Excel, end users can customize their menus and toolbars. Things
have changed in Excel 2007. Now, the only end user customization option is the Quick
Access Toolbar.
Normally, the Quick Access Toolbar appears on the left side of the title bar.
Alternatively, you can display the Quick Access Toolbar below the ribbon. Right-click the
Quick Access Toolbar and select Show Quick Access Toolbar Below Ribbon. By default,
the Quick Access Toolbar contains three tools: Save, Undo, and Repeat. You can
customize the Quick Access Toolbar by adding other commands that you use often. To
add a command from the Ribbon to your Quick Access Toolbar, right-click the command
which you want to add and choose Add To Quick Access Toolbar. If you click the
downward-pointing arrow to the right of the Quick Access Toolbar, you see a drop-down
menu with some additional commands that you might want to place in your Quick
Access Toolbar.
Figure below shows the Customization section of the Excel Options dialog box. To
display this dialog box right-click the Quick Access Toolbar and choose Customize Quick
Access Toolbar.
The Taskbar
Taskbar appears automatically in response to several commands. For example, if you
want to insert aclip art image, choose Insert > Illustrations > Clip Art. Excel responds by
displaying the Clip Art Task bar, shown in Figure below. When you’re finished using
a Task bar, click the Close button in the upper right corner. By default, the taskbar is
docked on the right side of the Excel window, but you can drag it anywhere you like.
Create a Worksheet
Create Excel Worksheet When you fire up Excel, it opens a fresh workbook file. If you've
already got Excel open and you want to create another workbook, just select Office
button > New. This step pops up the New Workbook window as showing in image below.
The New Workbook window lets you create a new, blank workbook or a ready-made
workbook from a template.
Adding the Column Headings
The most straightforward way to create a worksheet is to design it as a table with
headings for eachcolumn. For example, in a worksheet that stores a mailing list, you
could have two columns: one for names and another for addresses. But if you create
more than two columns, your life will probably be easier since you can separate first
names from street addresses from Zip codes, and so on.
If you enter the first and last names together in one column, Excel can sort only by the
first names. And if you clump the addresses and Zip codes together, you give Excel no
way to count how many people live in a certain town or neighborhood because Excel
can't extract the Zip codes.
The benefits of a six-column table are: it lets you sort your list according to people's last
names or where they live. It also allows you to filter out individual information when you
start using functions.
The first step in creating your worksheet is to add your headings in the row of cells at
the top of theworksheet. Adding information is easy just click the cell you want and start
typing. When you're finished, hit Tab to complete your entry and move to the next cell
to the right or Enter to head to the cell just underneath.
For a simple expense worksheet designed to keep a record of your most prudent and
extravagant purchases, try these headings: 1. Date Purchased stores the date when you
spent the money, 2. item stores the name of the product that you bought and 3. price
records how much it cost. As showing in figure below:
The standard width of an Excel column is 8.43 characters, which hardly allows you to
get a word in edge wise. To solve this problem, position your mouse on the
right border of the column header you want to expand so that the mouse pointer
changes to the resize icon. Now drag the column border to the right, as you drag a
tooltip appears, telling you the character size and pixel width of the column.
Adding Data
Now begin adding your data, simply fill in the rows under the column titles. Each row in
the worksheetrepresents a separate purchase (or record ). As the first column is for
dates, the second column is for text, and the third column holds numbers (price). Excel
doesn't impose any rules on what you type, so you're free to put text in the
Price column. But if you don't keep a consistent kind of data in eachcolumn, you won't
be able to easily analyze and understand your information later.
Data Editing
Every time you start typing in a cell, Excel erases any existing content in that cell. If you
want to edit cell data instead of replacing it, you need to put the cell in edit mode.
Move to the cell you want to edit, you can use the mouse or the arrow keys to get to the
correct cell. Put the cell in edit mode by pressing F2 or you can also get a cell into edit
mode by double-clicking it. Edit mode looks almost the same as ordinary text entry
mode. The only difference is that you can use the arrow keys to move through the text
you're typing and make changes. When you aren't in edit mode, pressing these keys
just moves you t
Navigating
Learn how to move around the Excel grid quickly. To move from cell to cell, you have
two basic choices:
1- Use the arrow keys on the keyboard, keystrokes move you one cell at a time in any
direction.
2- Click the cell with the mouse. A mouse click jumps you directly to the cell you've
clicked.
As you move from cell to cell, you see the black focus box move to highlight the
currently active cell. You can also use some useful short keys to quickly jump cell to
cell.
Shortcut Keys for Moving Around a Worksheet
Left arrow or Tab key
Moves one cell to the right.
Right arrow or Shift Tab key
Moves one cell to the left.
Up arrow
Moves one cell up.
Down arrow
Moves one cell down.
Page Up
Moves up one screen. Thus, if the grid shows 10 cells at a time, this key moves to a cell
in the same column, 10 rows up.
Page Down
Moves down one screen. Thus, if the grid shows 10 cells at a time, this key moves to a
cell in the same column, 10 rows down.
Home
Moves to the first cell (column A) of the current row.
CTRL + Home
Moves to the first cell in the top row, which is A1.
CTRL + End or End, Home
Moves to the last column of the last occupied row. This cell is at the bottom-right edge
of your data.
Excel also lets you cross great distances in a single bound using a Ctrl+arrow key
combination. These key combinations jump to the edges of your data. Edge cells include
cells that are next to other blank cells. For example, if you press Ctrl+ Right Arrow while
you're inside a group of cells with information in them, you'll skip to the right, over all
filled cells, and stop just before the next blank cell. If you press Ctrl+Right Arrow again,
you'll skip over all the nearby blank cells and land in the next cell to the right that has
information in it. If there aren't any more cells with data on the right, you'll wind up on
the very edge of your worksheet. The Ctrl+arrow key combinations are useful if you
have more than one table of data in the same worksheet. For example, imagine you
have two tables of data, one at the top of a worksheet and one at the bottom. If you are
at the top of the first table, you can use Ctrl+Down Arrow to jump to the bottom of the
first table, skipping all the rows in between. Press Ctrl+Down Arrow again, and you leap
over all the blank rows, winding up at the beginning of the second table.
Use Go To Command
You can use the Go To feature to make the jump, Go To moves to the cell address you
specify. It comes in useful in extremely large spreadsheets, where just scrolling through
the worksheet is aheadache. The Go To window maintains a list of the most recent cell
addresses that you've entered so the Go To feature becomes more useful the more you
use it. This feature makes it easy to jump to a far-off cell and quickly return to your
starting location by selecting the last entry in the list.
The Go To window isn't your only option for leaping through a worksheet in
a single bound. If you look at the Home Tab then Editing > Find & Select menu you'll
find more specialized commands that let you jump straight to cells that contains
formulas, comments, conditional formatting, and other advanced Excel ingredients.
Formula Bar
You can use the formula bar to enter and edit data, instead of editing directly in
your worksheet. This approach is particularly useful when a cell contains a formula or a
large amount of information. That's because the formula bar gives you more work room
than a typical cell. Just as with in-cell edits, you press Enter to confirm your changes or
Esc to cancel them. Or you can use the mouse: When you start tying in the formula bar,
a checkmark and an "X" icon appear just to the left of the box where you're typing. Click
the checkmark to confirm your entry, or "X" to roll it back
Ordinarily, the formula bar's a single line. If you have a really long entry in a cell (like a
paragraph's worth of text), you need to scroll from one side to the other. However,
there's another option you can resize the formula bar so it fits more information, as
shown in figure below.
Status Bar
The status bar is a good way to keep on top of Excel's current state. For example, if you
save or print a document, the status bar shows the progress of the printing process. If
you're performing a time-consuming operationsay, printing out an 100 pages table you
can look to the status bar to see how things are coming along. You can hide or show the
status bar from View Show/Hide Status Bar.
The leftmost part of the status bar shows the Cell Mode, which displays one of three
indicators.
The word "Ready" means that Excel isn't doing anything much at the moment, other
than waiting for you to take some action.
The word "Enter" appears when you start typing a new value into a cell.
The word "Edit" means the cell is currently in edit mode, and pressing the left and right
arrow keys moves through the cell data, instead of moving from cell to cell.
The right on the status bar are the view buttons, which let you switch to Page Layout
View or Page Break Preview. These different views help you see what
your worksheet will look like when you print it.
The zoom slider is next to the view buttons, at the far right edge of the status bar. You
can slide it to the left to zoom out (which fits more information into your Excel window
at once) or slide it to the right to zoom in (and take a closer look at fewer cells).
In addition, the status bar displays other miscellaneous indicators. For example, if
you press the Scroll Lock key, a Scroll Lock indicator appears on the status bar. This
indicator tells you that you're in scroll mode. In scroll mode, the arrow keys don't move
you from one cell to another; instead, they scroll the entire worksheet up, down, or to
the side. Scroll mode is a great way to check out another part of
yourspreadsheet without leaving your current position.
You can control what indicators appear in the status bar by configuring it. To see a full
list of possibilities, right-click the status bar. A huge list of options appears, as shown in
figure below .
Status Bar Customization
Cell Mode
Shows Ready, Edit, or Enter depending on the state of the current cell.
Signatures, Information Management Policy, and Permissions
Displays information about the rights and restrictions of the current spreadsheet. These
features come into play only if you're using Office SharePoint Server to
share spreadsheets among groups of people (usually in a corporate environment).
Caps Lock
Indicates whether Caps Lock mode is on.
Num Lock
Indicates whether Num Lock mode is on.
Scroll Lock
Indicates whether Scroll Lock mode is on. When it's on, you can use the arrow keys to
scroll theworksheet without changing the active cell. This feature lets you look at all the
information you have in your worksheet without losing track of the cell you're currently
in.
Fixed Decimal
Indicates when Fixed Decimal mode is on. When this mode is on, Excel automatically
adds a set number of decimal places to the values you enter in any cell. For example, if
you set Excel to use two fixed decimal places and you type the number 5 into a cell,
Excel actually enters 0.05. This seldom-used featured is handy for speed typists who
need to enter reams of data in a fixed format. You can turn this feature on or off by
selecting Office button Excel Options, choosing the Advanced section, and then looking
under "Editing options" to find the "Automatically insert a decimal point" setting. Once
you turn this checkbox on, you can choose the number of decimal places (the standard
option is two).
Overtype Mode
Indicates when Overwrite mode is turned on. Overwrite mode changes how cell edits
work. When you edit a cell and Overwrite mode is on, the new characters that you type
overwrite existing characters (rather than displacing them). You can turn Overwrite
mode on or off by pressing Insert.
End Mode
Indicates that you've pressed End, which is the first key in many two-key combinations;
the next key determines what happens.
Macro Recording
Macros are automated routines that perform some task in an Excel spreadsheet.
The Macro Recording indicator shows a record button (which looks like a red circle
superimposed on a worksheet) that lets you start recording a new macro.
Selection Mode
Indicates the current Selection mode. You have two options: normal mode and extended
selection. When you press the arrows keys and extended selection is on, Excel
automatically selects all the rows and columns you cross. Extended selection is a useful
keyboard alternative to dragging your mouse to select swaths of the grid. To turn
extended selection on or off, press F8.
Page Number
Shows the current page and the total number of pages. This indicator appears only in
Page Layout view.
Average, Count, Numerical Count, Minimum, Maximum, Sum
Show the result of a calculation on the selected cells. For example, the Sum indicator
shows the total of all the numeric cells that are currently selected.
View Shortcuts
Shows the three view buttons that let you switch between Normal view, Page Layout
View and Page Break Preview.
Zoom
Shows the current zoom percentage.
Zoom Slider
Shows a slider that lets you zoom in closer (by sliding it to the right) or out to see more
information at once (by sliding it to the left).
Options
Excel Options are a central location where you can adjust how Excel looks, behaves, and calculates. To get to this window, click the Office button, and then choose Excel Options on the bottom-right edge.
The top five sections in the Excel Options window let you tweak a wide variety of
different details. Some of these details are truly handy, like the options for opening
and saving files. Others are seldom-used holdovers from the past, like the option that
lets Excel act like Lotus an ancient piece of spreadsheetsoftware when you hit the "/"
key.
Beneath the top five sections are four more specialized sections:
Customize
Lets you put your favorite commands on the Quick Access toolbar, a maneuver you can
learn more about in the Appendix.
Add-Ins
Lets you configure other utilities that work with Excel and enhance its powers.
Trust Center
Lets you tweak Excel's security settings that safeguard against dangerous actions. You
need to learn more about these settings before you can use Excel to interact with
a database or run macro code.
Resources
Provides a few buttons that let you get extra diagnostic information, activate your copy
of Office, and get freebies and updates on the Web.
What's New in Excel 2007?
New User Interface
Microsoft Excel continues to grow in power, sophistication, and capability, but one thing
that has changed very little since the early '90s is its user interface. The once-simple
toolbar has been packed with so many features over the years that few users know
where to find them all. Microsoft has addressed this problem in Excel 2007 by radically
redesigning the user interface. The time-honored menu-and-toolbar user interface has
been scrapped and replaced with a new "e;tab-and-ribbon"e; interface that makes
every feature easy to locate and use.
Other elements that comprise the new look include:
Six new modern-looking fonts:
The default workbook font is now 11-point Calibri, which is much more readable
than the old 10-point Arial, especially in smaller sizes.
Quick Access Toolbar:
A personal toolbar, to which you can add commands that you use regularly. This toolbar
is the only part of the Excel 2007 interface that the user can customize.
The Mini toolbar:
A new addition to the right-click menu. This toolbar contains commonly-used formatting
icons, displayed near your mouse pointer for quick access.
New File Formats
Over the years, Excel's XLS file format has become an industry standard. Excel 2007
still supports that format, but it now uses new default "open" file formats that are based
on XML (Extensible Markup Language).
For compatibility, Excel 2007 still supports the old file formats so that you can continue
to share your work with those who haven't upgraded to Excel 2007.
New Worksheet Functions
IFERROR
Returns a value you specify if a formula evaluates to an error; otherwise, returns the
result of the formula.
AVERAGEIF
Calculates a conditional average (similar to SUMIF and COUNTIF).
AVERAGEIFS
Calculates a conditional average using multiple criteria.
SUMIFS
Calculates a conditional sum using multiple criteria.
COUNTIFS
Calculates a conditional COUNT using multiple criteria.
In addition, 39 worksheet functions that used to require the Analysis Toolpak add-in are
now built-in. Excel 2007 also includes seven new CUBE functions that retrieve data
from SQL Server Analysis Services.
Enhanced Conditional Formatting
Conditional formatting refers to the ability to format a cell based on its
value. Conditional formatting makes it easy to highlight certain values so that they
stand out visually. For example, you may set upconditional formatting so that if a
formula returns a negative value, the cell background displays green.
In the past, a cell could have at most three conditions applied. With Excel 2007, a you
can format a cell based on an unlimited number of conditions. But that's the least of
the improvements. Excel 2007 provides a number of new data visualizations: data bars,
color scales, and icon sets.
Excel 2007 includes quite a few other improvements to conditional formatting. In
general, conditionalformatting is much more flexible, easier to set up, and relies less on
creating custom formulas to define the formatting rules.
Formula AutoComplete
Entering formulas in Excel 2007 can be a bit less cumbersome, thanks to the new
Formula AutoComplete feature. When you begin typing a formula, Excel displays a
continually updated drop-down list of matching items, including a description of each
item. When you see the item you want, press Tab to enter it into your formula. The
items in this list consist of functions, defined names, and table references.
Worksheet Tables
Working with tables is easier than ever. A table is just a rectangular range of cells that
usually containscolumn headers. The designers of Excel 2007 realized that
such tables are widely used in Excel, and they've taken the concept to a new level.
Once you designate a particular range to be a table using the Insert > Tables > Table
command, Excel provides you with some very efficient tools that work with the table:
You can apply attractive formatting with a single click.
You can easily insert summary formulas in the table's total row.
If each cell in a column contains the same formula, you can edit one of the
formulas, and the others change automatically.
You can easily toggle the display of the table's the header row and totals row.
Removing duplicate entries is easy.
Auto filtering and sorting options have been expanded.
If you create a chart from a table, the chart will always reflect the data in the
table-even if you add new rows.
If you scroll a table downwards so that the header row is no longer visible,
the column headers now display where the worksheet column letters would be.
Improved Pivot Tables
Excel’s pivot table feature is probably one of its most underutilized features. A pivot
table can turn a large range of raw data into a useful interactive summary table with
only a few mouse clicks. Microsofthopes to make this feature more accessible by
improving just about every aspect of pivot tables in Excel 2007.
Charts created from pivot tables (pivot charts) now retain their formatting when they’re
updated. This loss of formatting had been a frustration for hundreds of thousands of
users, and Microsoft finally did something about it.
Larger Worksheets
Over the years, perhaps the most common complaint about Excel was the size of
a worksheet. Aworksheet now has 1,048,576 rows and 16,384 columns, which works out
to more than 17 billion cells, an Excel 2007 worksheet has more than 1,000 times as
many cells as an Excel 2003 worksheet.
Excel 2003 versus Excel 2007
Excel
2003
Excel 2007
Number of rows 65,536 1,048,576
Number of columns 256 16,384
Amount of memory used 1 Gbytes Maximum allowed
by Windows
Number of colors 56 4.3 billion
Number of conditional formats per cell 3 Unlimited
Number of levels of sorting 3 64
Number of levels of undo 16 100
Number of items shown in the Auto-Filter
dropdown
1,000 10,000
The total number of characters that can display
in a cell
1,000 32,000
Number of unique styles in a workbook 4,000 64,000
Maximum number of characters in a formula 1,000 8,000
Number of levels of nesting in a formula 7 64
Maximum number of function arguments 30 255
Maximum number of function arguments 30 255
Styles and Themes
Mostly people aren't very good with graphic design. Worksheets with gaudy colors and
unreadable blue-on-green text are common.
Excel has always supported named styles, which can be applied to cells and ranges.
Excel 2007 brings this feature to the forefront by providing a good assortment of
predefined styles, easily accessible by choosing Home > Styles > Cell Styles.
With the introduction of document themes, Excel 2007 makes it easy to create good-
lookingworksheets. A theme consists of a color palette, font set, and effects. You now
have one-click access to a gallery of professionally-designed themes that can
dramatically change the look of your entirespreadsheet-almost always for the better.
Access the theme gallery by choosing Page Layout > Themes > Themes.
Better Looking Charts
Excel 2007 offers no new chart types, and many of the long-time chart-
related feature requests have been ignored by Microsoft.Excel charts now look better
than ever. For the first time, you can honestly use the term "boardroom quality" to
describe Excel charts.
Page Layout View
As an option, you can display your worksheet as a series of pages. This new
Page Layout view ensures no surprises when it's time to print your work. Even better,
the Page Layout view includes "click and type" page headers and footers-which is much
more intuitive than the old method. Unlike the standard print preview, Page Layout view
is fully functional in terms of spreadsheet editing.
Excel's new Page Layout view makes it easy to see how your printed work will appear.
SmartArt
Excel 2007 still includes a wide assortment of Shapes that you can use to create visual
diagrams, such as flow charts, org charts, or diagrams that depict relationships. But the
new SmartArt feature is a much better tool for such tasks. You can quickly add
shadows, reflection, glow, and other special effects.
Consolidated Options
In the past, Excel provided far too many dialog boxes to set various options. In Excel
2007, mostdialog boxes have been consolidated into a massive Excel Options dialog
box. To display this dialog box, choose File > Excel Options.
The options are grouped into tabs, which you select on the left. Locating some of the
options still isn’t easy, but the new implementation is much better than it used to be.
The Excel Options dialog box is also resizable—just click and drag the lower right corner
to change the size.
Collaboration Features
Excel Services is new server technology that ships with Office 12. It’s part of
the Microsoft Office Share Point Server product. Excel Services supports loading,
calculating, and rendering Excel spreadsheets on servers.
If your company is set up with Excel Services, you can use Excel 2007 to collaborate
with your coworkers efficiently and present data to those who don’t use Excel.
Compatibility Checker
Given all the new features in Excel 2007, you may be hesitant to share a workbook with
others who use an earlier version of Excel. To find out how your workbook will function
with previous versions, use the compatibility checker. Choose Office > Prepare > Run
Compatibility Checker.
Other Features
Excel 2007 features
Trust Center: Protecting yourself from malicious macros is a bit easier with
Excel 2007. For example, you can disable all macros, except those in workbooks
that are stored in trusted locations on your computer.
PDF add-in: You can create an industry-standard Adobe PDF file directly from
Excel using an add-in available from Microsoft.
Improved zooming: Use the zoomer control on the right side of the status bar
to quickly zoom in or zoom out on your worksheet.
More control over the status bar: You can now control the type of
information that appears in the status bar.
Color Schemes: Change the appearance of Excel by applying one of three color
schemes that ship with Excel (Blue, Silver, or Black).
Resizable formula bar: When editing lengthy formulas, you can increase the
height of the formula bar so that it doesn’t obscure your worksheet. Just click and
drag on the bottom borderof the formula bar.
Lots of new templates: Why reinvent the wheel? Choose Office > New, and
you can choose from a variety of templates. One of them may be exactly (or at
least close to) what you need.
When you first launch Excel, it starts you off with a new blank worksheet.A worksheet is
the grid of cells where you type your information and formulas.This grid is the most
important part of the Excel window. It's where you'll perform all your work, such as
entering data, writing formulas, and reviewing the results.
Each workbook is comprised of one or more worksheets, and each worksheet is made
up of individualcells.Each cell contains a value, a formula, or text. A worksheet also has
an invisible draw layer, which holdscharts, images, and diagrams. Each worksheet in a
workbook is accessible by clicking the tab at the bottomof the workbook window. In
addition, workbooks can store chart sheets. A chart sheet displays a single chartand is
also accessible by clicking a tab.
The grid divides your worksheet into rows and columns. Columns are identified with
letters (A, B, C…), while rows are identified with numbers (1, 2, 3…).The smallest unit in
your worksheet is the cell. Cells are identified by column and row. For example, B4 is
the address of a cell in column B (the third column), and row 4 (the sixth row). An Excel
cell can hold up to 32,000 characters.
A worksheet can span an eye-popping 16,000 columns and 1 million rows. When you
enter information, you enter it one cell at a time. However, you don't have to follow any
set order. For example, you can start by typing information into cell B40 without
worrying about filling any data in the cells that appear in the earlier rows.
Active cell indicator
This dark outline indicates the currently active cell.
Column letters
Letters range from A to IXFD. To select an entire column click a column heading.
Office button
This button have a lots of options for working with Excel.
Formula bar
Cell information or formulas appear in this line.
Name box
Displays the active cell address or the name of the selected cell, range, or object.
Page view buttons
Change the way the worksheet is displayed by clicking one of these buttons.
Quick Access Toolbar
A customizeable toolbar that holds commonly-used commands
Ribbon
The main location to find Excel's commands.
Row numbers
Numbers range from 1 to 1,048,576, to select an row click a row number.
Sheet tabs
Each of these tabs represents a different sheet in the workbook.
Sheet tab scroll buttons
These buttons let you scroll the sheet tabs to display tabs that aren't visible.
Status bar
This bar displays various messages like status of the Num Lock, Caps Lock, and
ScrollLock keys on your keyboard.
Tab list
Similar to a menu, display a different ribbon.
Zoom control
Worksheet in and out zoom controller.
The current cell in above figure is B7. You can recognize the current (or active) cell
based on its heavy black border. You'll also notice that the corresponding column letter
(B) and row number (7) are highlighted at the edges of the worksheet. Just above
the worksheet, on the left side of the window, the formula bar tells you the active cell
address.
Customizing the Excel Menus and Toolbars
Customizing the Excel toolbars and pull down menus. Taking the time to get the
Excel toolbars(especially the Standard and Formatting toolbars that
Excel automatically displays whenever you launch the program) and the pull-down
menus the way you want them saves your valuable time when creating and editing
your spreadsheets. (Just think about how many times you access their buttons
and menu items in a single work session, and you will understand why.)
Excel makes it easy to customize the position, behavior, and even content of its
many toolbars and pull-down menus. This customization includes placing
the Standard and Formatting toolbars on two separate rows, always displaying full pull-
down menus, and customizing the buttons and command options found on the built-in
bars and menus as well as those found on the bars and menus you build on your own.
Showing the Toolbars and Menus
Excel tries to save valuable screen space by automatically placing
the Standard and Formatting toolbarson the same row at the top of the screen. This is
fine except for when you are using one a smaller monitor in a relatively low screen
resolution (such as 800 x 600 or 1024 x 768) where it is impossible to have all the
buttons on either toolbar display at the same time and where you are often forced to
waste time in clicking the Toolbar Options button to have access to options that are
currently hidden.
To ensure that all the buttons on both the Standard and Formatting toolbars are always
displayed, follow these steps:
1. Choose View > Toolbars > Customize to open the Customize dialog box and then
Select the Options tab in the Customize dialog box, as shown below.
Forcing Excel to display the Standard and Formatting toolbars on two rows.
2. Select the Show Standard and Formatting Toolbars on Two Rows check box and
then click the Close button.
To get Excel to display all of a menu's options the moment you open it, select the
Always Show Full Menus check box on the Options tab of the Customize dialog box. This
stops the program from short changing you on how many options you see when you
first open a particular menu. (Menus don't show all their options until the menus are
open for several moments - moments that often seem like alifetime.)
Adding Macros and Links to Toolbars and Menus
Built-in commands are not the only things you can add to the toolbars and pull-down
menus you customize or create from scratch. You can also add macros you
have recorded and hyperlinks to particular documents, folders, and even favorite Web
sites. To do this, you attach the macro or link to acustom button that you add to a
toolbar or to a custom menu item that you add to a pull-down menu.
To attach a macro or hyperlink to a custom button that you add to a toolbar, follow
these steps:
1. Choose View > Toolbars > Customize to open the Customize dialog box.
2. If the toolbar to which you want to add the custom button is not already open,
select theToolbars tab and then select the toolbar's check box to display it in the
Excel screen.
3. Select the Commands tab and then select Macros near the bottom of the
Categories list box, as shown below.
When you select the Macros category, the Commands list box gives you a choice
between a Custom Menu Item and a Custom Button.
4. Drag the Custom Button (with the Happy Face icon) and drop it into position on
the toolbar from which you want to be able to run the macro or select the
hyperlink.
5. Right-click the Custom Button you just added to the toolbar and then click either
the Assign Hyperlink > Open item (to attach a hyperlink to it) or the Assign Macro
item (to attach a macro to it) on the shortcut menu, shown below.
If you choose Assign Hyperlink > Open, Excel opens the standard Assign
Hyperlink dialog boxwhere you can select an existing file or Web page or e-mail
address to link to. If you choose Assign Macro, the program opens the Assign
Macro dialog box where you can select the macro to run when the button is
clicked.
6. Select the link to the hyperlink's destination in the Assign Hyperlink dialog box or
the macro to run in the Assign Macro dialog box; then click OK.
7. To select a new icon for the button, right-click the custom button and choose
Change Button Image. From the icon pop-up palette that appears, choose the
new icon you want to use.
You can use the Button Editor - which you can open by selecting Edit Button
Image on the button's shortcut menu - to modify the icon that you choose on the
Change Button Image's icon palette. This editor lets you make pixel-by-pixel
changes to the image, either by erasing pixels or by painting with new colors.
8. Click the Close button in the Customize dialog box.
The process for assigning a hyperlink or macro to a menu item is almost the same,
except that you select Custom Menu Item in the Macros category on the Commands tab
of the Customize dialog box. Then you drag this Custom Menu Item to the place on the
pull-down menu where you want it to appear. Next, you assign either a hyperlink or
macro to it by rightclicking the Custom Menu Item on its pull-down menu and then
choosing either Assign Hyperlink > Open or Assign Macro on its shortcut menu.
After assigning the link or macro, you will want to rename the menu item. To do this,
right-click CustomMenu Item on its pull-down menu and then click the Name option and
replace Custom Menu Item with a name of your own. If you want to assign a hotkey to
one of the characters in the new name, be sure totype an ampersand (&) immediately
in front of the character in the custom menu name that you want to so designate.
Rearrange Toolbars and Menus
You can easily make the Excel more efficient by customizing any of the
existing toolbars and pulldown menus so that they consist of just the options you use in
the order in which you'd like to see them. To customize a built-in Excel toolbar, you
follow these steps:
1. Choose View > Toolbars > Customize to open the Customize dialog box.
2. If the toolbar you want to customize is not already displayed on the screen,
select the Toolbarstab in the Customize dialog box and then select that toolbar's
check box.
3. To add a new button to the toolbar now displayed, select the Commands tab in
the Customizedialog box and then select the desired command category in the
Categories list box. Scroll to the command's icon in the Commands list box and
then drag the icon from the Customize dialog boxto its new place on the toolbar.
You can release the mouse button as soon as the box with the + (plus) appears
on the mousepointer. Excel indicates where the new button will be inserted on
the toolbar by displaying the bold I-beam-type indicator on the toolbar.
To delete a button from the toolbar, drag its button from the toolbar, and then
when the box with the X in it appears at the mouse pointer, release the
mouse button.
4. To rearrange the buttons on the toolbar, drag the button icon to its new position
on the bar (indicated by the bold I-beam-type indicator).
5. To add a vertical separator bar that groups the buttons, locate the button in front
of which the separator will be inserted and then drag that button slightly to the
right. As soon as you releasethe mouse button, the separator bar appears.
To remove a vertical separator, drag the button that the separator is directly in
front of to the left until the bar disappears.
6. After you finish customizing the toolbar, click the Close button to close the dialog
box and returnthe functionality to the toolbars and menus.
The steps for customizing one of Excel's pull-down menus are the same as those
for customizing a toolbar, except that after opening the Customize dialog box and
selecting the Commands tab, you must choose the name of the menu you want to
customize. The steps for adding commands, deletingmenu items, and rearranging them
on a particular menu are the same as those for doing these things to a toolbar: just
keep in mind that the bold, I-beam-type indicator shows you where the command you
add will be inserted on the pull-down menu.
You can use the controls in the Rearrange Commands dialog box to quickly add, delete,
or modify the order of the buttons on any Excel toolbar or items on any of its pull-down
menus. To open this dialog box, click the Rearrange Commands button on the
Commands tab of the Customize dialog box.
To customize a menu or toolbar in the Rearrange Commands dialog box, follow these
steps:
1. If you are customizing a pull-down menu, click the Menu Bar option button and
then select the pull-down menu name in its drop-down list box. If you
are customizing a toolbar, click the Toolbar option button and then select the
toolbar name in its drop-down list box.
Excel displays all the menu items or toolbar buttons for the menu or toolbar you
selected in their current order.
2. To add an item or button, click the menu item or button in front of which you
want to insert the new button and then click the Add button. In the Add
Command dialog box that appears, select the option you want added and click
OK.
3. To delete an item or button, select it in the Rearrange Commands dialog box and
then click the Delete button.
4. To modify the position of an item or button, select it in the Rearrange
Commands dialog box and then click the Move Up or Move Down button until the
item or button appears in the desired position.
5. After you finish customizing your menu or toolbar, click the Close button in the
Rearrange Commands dialog box and then click the Close button in the
Customize dialog box.
Create your own toolbars and menus
In Excel 2007 you can create your own toolbars and menus. By building your own
custom toolbar or menu, you can bring together just the commands that you normally
rely on. For example, you can make a Calculation toolbar or pull-down menu that
contains only the options that you frequently use in building your spreadsheet formulas.
To create a custom toolbar, follow these steps:
1. Choose View > Toolbars > Customize to open the Customize dialog box and then
click theToolbars tab and then click the New button to open the New
Toolbar dialog box, shown in below.
2. Enter your name for the custom toolbar in the Toolbar Name text box and then
click OK.
Excel adds the custom toolbar's name to the Toolbars list box in the
Customize dialog box and displays a blank floating toolbar that shows only the
first few letters of its name.
3. To add a tool to the new toolbar, select the Commands tab in the
Customize dialog box, click the desired command category in the Categories list
box, scroll to the command's icon in the Commands list box, and then drag the
command's icon from the Customize dialog box to the new toolbar.
Excel automatically expands the toolbar to accommodate the buttons you add.
4. Repeat Step 3 until you have added all the tools you want to appear on your new
toolbar.
5. If you want to group the buttons on the new toolbar, locate the buttons in front of
which you want the separators to appear and then drag the buttons slightly to
the right.
You can also reposition the buttons you add to a new toolbar simply by dragging
them to their places.
6. When you have the new toolbar exactly the way you want it, click the Close
button to close the Customize dialog box.
After creating a custom toolbar, be sure to test out its buttons by clicking them at the
appropriate moments in building or editing your spreadsheet (not all Excel commands
can be executed on a blankworksheet).
The process for creating a custom menu is the same as for a toolbar except that to start
the custom menu, you select the New Menu item at the bottom of the Commands tab of
the Customize dialog box. Then you drag this New Menu item from the Customize dialog
box to the place where you want it to appear on the Excel menu bar. To rename the
menu to something besides New Menu, right-click New Menu on the Excel menu bar and
choose Name from the shortcut menu. Then replace New Menu with the new name you
have chosen.
If you want to assign a hot key to the name you give your new custom menu, type an
ampersand (&) immediately in front of the letter in the menu name. (Just make sure
that it is not already assigned to one of the other items on the same menu.)
To add an item to your new menu, drag the command from the Customize dialog box to
the tab under the menu. (Don't release the mouse button until the bold, I-beam-
type indicator appears on the tab and the box with the plus sign (+) appears next to the
mouse pointer.) Removing and repositioning items on your custom menu is no different
from making these types of modifications to a standard pulldown menu.