Excel 2010 (Quickstudy: Computer)

6 Pages·2010·1.982 MB·English

by  Inc. BarCharts

BarCharts, Inc.® WORLD’S #1 QUICK REFERENCE SOFTWARE GUIDE
eXcel 2010
Contents
Pg. 1 Excel 2010 Main Window, New Features Pg. 3 Working with Documents – Backstage View continued, Undo Pg. 5 Basic Formatting of Cells
Pg. 2 New Features continued, Document File Formats, Getting & Redo – Quick Access Toolbar, Introduction to Formulas – Home Tab continued
Help, Excel 2010 Terminology, Working with Documents – Pg. 4 Introduction to Formulas continued, Basic Formatting of Pg. 6 New User Interface (UI)
Backstage View Cells – Home Tab
Excel 2010 Main Window
1. Quick Access Toolbar. Puts commonly 1 2 3 4 5 6 7 8
Microsoft®
used commands on a permanently visible
toolbar. Fully customizable. 10
2. Insert Function. Clicking this button
displays Insert Function dialog box (see
11 9
pg. 3).
3. Formula Bar. Formula or contents of
the currently selected cell is shown here. 12
Edit the contents by using the mouse 13
to select text to be edited and entering 15
14
desired change.
4. Filename and Type of Document.
19
The Title Bar shows the name of the
5 18
document and its type.
5. Selected Row and Column. The
column and row of the selected cell is
displayed like this.
6. Minimize the Ribbon. Clicking this
button minimizes the Ribbon to just 20
the tabs; if minimized, the arrow is
highlighted and clicking it will
26
restore the buttons on the tabs.
7. Help. Provides quick and easy access
to the Help system.
8. Close Document Window. To close 20
the currently displayed document,
click this icon. If the document
needs to be saved, a window will be
25
displayed asking whether or not to
16
save the document before closing.
17
9. Expand Formula Bar. Allows more
21 22 23 24
space to display a long formula, instead
of it covering data cells below it. 17. Status Bar. This area displays various state indicators of the application New Features
10. File Tab. Replaces the File Menu in and statistics for the selected cells, if any (multiple cells must be selected
Excel 2010 offers many new features (those
previous versions (Excel 2003 and lower) for them to be displayed; includes sum, average, count [both total and
in red are described in greater detail in this
and the old round Office button in Excel cells with numbers only], minimum and maximum). It can be configured
guide) over Excel 2007. Some of the new and
2007. Provides options to work with the to display as many or as few details as desired about the current workbook.
improved features are:
document (EX: Save, Close, Print). To change what is displayed, simply right-click on the Status Bar and
• The most noticeable change is the updated
11. Ribbon. The new user interface (UI) check or uncheck the options desired. The normal display is “Ready,”
(relative to 2007) or completely redesigned
for some Office 2007 products and meaning it is waiting for you.
(relative to 2003) User Interface (UI). While
all Office 2010 products, replacing 18. Active Cell. The currently selected (active) cell. To select a cell, click it.
very different from the past, most people
traditional menus. Used to work in the 19. Graphic. Excel 2010 has the ability to place graphics anywhere on the
adapt quickly and like the new design much
document (EX: formatting text). spreadsheet, similar to Word and PowerPoint.
better, as it is simpler to find what you want
12. Name Box. Cell coordinates (intersecting 20. Sparklines. Excel 2010 introduces a new feature that allows for simple
and offers the ability to preview changes
column and row) of the currently selected graphs that fit in a single cell to show trends, wins/losses, etc.
before making them.
cell are displayed here. 21. Worksheet Tabs. A tab for each worksheet in a workbook is displayed
• Sparklines make it easy to create simple
13. Select All. Clicking this button selects here. Display a specific worksheet by locating the worksheet tab and graphs in a single cell that automatically
all the cells in the current sheet. clicking it. resize to the width and height of the cell.
14. Row. Excel 2010 organizes data into 22. Insert Worksheet. Inserts a new blank worksheet tab at the end of the • The Slicer feature for PivotTables makes it
rows and columns. The designation for list of tabs. easy to divide the PivotTable into pieces,
rows is numeric and is displayed in this 23. Views. These icons in the Status Bar provide the ability to quickly and then filter each piece so that only
area. To select an entire row, click the change the display view. The buttons represent (left to right): Normal, relevant data is displayed, making it much
row number. Page Layout and Page Break Preview. easier to analyze large PivotTables.
15. Column. Excel 2010 organizes data into 24. Zoom. The document can be zoomed in or out from 10 to 400% by • Excel Web App is a new feature of
rows and columns. The designation for dragging the arrow or clicking the + or – icons to change 10% per click. Windows Live (for home users) or
columns is alphabetic and is displayed 25. Scroll Bars. These bars provide the ability to scroll horizontally and SharePoint 2010 (for business users) that
in this area. To select an entire column, vertically through a document. To scroll one line at a time, click the single allows you to view and make simple edits to
click the column letter. arrows located at the left and right or top and bottom (respectively) of the your document right in a Web browser. The
16. Worksheet Scroll Icons. The icons scroll bars. To scroll through a full screen vertically, click in the shaded spreadsheet can be shared online if desired
displayed here allow scrolling through area above or below the location indicator. and accessed from anywhere. Edits can be
worksheet tabs, providing access to 26. Graphs. Excel has the ability to create many types of graphs, including made in real time and any changes will be
worksheets when there isn’t enough both stacked bar charts (bottom) and pie charts (top). Data point values, tracked and flagged to make it simple to see
space to display all sheet tabs. colors, styles, labels, legends, etc., may all be specified. changes as they happen.
1
New Features (continued)
• Excel Mobile 2010 is an updated version of Excel that • PivotCharts have been updated so they can display • It is much simpler to add equations (EX: A = πr2; not
is available with Windows 7-based mobile phones. different data than the associated PivotTable shows (in to be confused with formulas described below); from
• Conditional Formatting, which can be used to make previous versions, changing one changed the other). the simple (i.e., area of a circle) to the complex (i.e.,
data stand out without formatting the data itself (a • The Backstage view is a new feature in all Office Fourier series), as well as the ability to create any
visual effect, not a cell property), has been improved. apps that is an update of the old Office button, now custom equation.
Changes include the ability to display data bars for called the File tab, which controls how you work • Supports dual processors and multi-threaded
negative values and to highlight specific items, plus with a document. It brings features in various dialog processors and 2 GB of RAM (up from 1 GB in x86
additional options in styles and icons. Available on the boxes together into a singe cohesive view (EX: all the versions); the maximum file size of 2 GB has also
Home tab of the Ribbon. printing options and print preview together). been removed.
• Paste Preview shows you how the data will look when • Several statistical functions have been updated for • 64-bit Office 2010 does have some compatibility issues.
pasted, as well as paste options, including the standard better accuracy; the old functions are still available For instance, x86 and x64 editions can’t be installed
paste, pasting just the formulas or the actual current for compatibility; see the list of old functions and side-by-side (it’s an either/or choice). You’ll automatically
values the formulas calculate to, pasting a picture of their replacements by pressing the Insert Function get the x86 version by default if you run the standard
the selected cells, transposing rows into columns and button and selecting Compatibility from the Category Setup.exe program, unless you browse the CD and go into
columns into rows, etc. drop-down list. the x64 directory and run setup in that folder.
Document File Formats
Excel 2010 comes with support for OpenDocument, thus should take less disk space than previous formats. display this in the Title Bar next to the filename). It will
an industry standard supported by many products They are also stored internally in “segments,” so that if automatically disable any features that are not compatible
(such as Google Docs, OpenOffice, Star Office and one segment gets corrupted, the rest of the workbook will with that format, by graying them out in the Ribbon and
WordPerfect X4, as well as 2007 SP2 and later). It is still be useable (in previous versions of Excel, if there stating they are disabled in the Super Tooltip.
an XML (eXtensible Markup Language)-based format, was any corruption, the workbook could not usually be
similar to the new formats introduced in 2007; the binary opened at all). Excel can still read and write workbooks The table below summarizes the available formats in
format introduced with 2007 and designed to be used created in Excel 97–2003, and can create new documents Excel (formats new in Excel 2007 are in blue and those
with very large spreadsheets is also available. These new in the old format as well. If the old format is chosen, that are new in Excel 2010 are in bold small caps and
formats are stored in a compressed format on disk, and however, Excel will operate in compatibility mode (and purple). The default format is .xlsx.
Extension Purpose .xlam Excel add-in
.xlsx Normal workbook .xml Excel 2003 XML workbook or XML data
.xlsm Workbook with macros in it .xls Format used in Excel 97–2003 for a normal workbook (with or without macros)
.xltx Template for new workbooks .xla Format used in Excel 97–2003 for an add-in
.xltm Template for new workbooks that contains macros .xlt Format used in Excel 97–2003 for a workbook template (with or without macros)
.xlsb Binary formatted Excel 2010 workbook .ods Open Document workbook
Getting Help
Excel offers an extensive Help system that can be displayed by clicking the on the right side of the Ribbon. Alternatively, you can get context-sensitive help (based on what
is selected, the tooltip displayed, etc.) by pressing <F1>.
Excel 2010 Terminology
Cell. A cell is a rectangle contained within a Formula. A Formula is an equation that performs various Workbook. A Workbook is a file that contains a 3-D arrangement
workbook or worksheet; it can contain text, operations (typically, mathematical calculations). of one or more worksheets.
values or formulas that return a value. It is at the Template. A predefined workbook that makes it quick
Worksheet. A Worksheet is a 2-D page of cells (16,384 columns
intersection of a column and a row, such as B5. to create a type of document, such as an expense report,
Dialog Box. A window that provides access to budget or loan amortization. Some come with Excel, but wide and 1,048,576 rows long) that are contained within a
the advanced functionality in Excel. hundreds more are available from Office Online. workbook.
Working with Documents – Backstage View
The Backstage view is a new feature in Excel 2010 that brings • In the Available Templates section, click Saving a Workbook
together all the related options and capabilities in a single place. The on the Sample Templates icon to view the It is very important to save your workbooks frequently to
Backstage view is similar in all Office applications. The screenshot templates installed with Excel. If the desired prevent data loss. Once a file has been saved for the first
here will be used to describe all the options available, even though template can’t be found (and there is an time (giving it a name), Excel won’t prompt for a name
all are not shown to save space. active Internet connection), use the links again; rather, it will update the file each time it is saved.
under the Office.com Templates heading to If a copy of a file is needed, use the Save As command
download one instead. to make a copy with a new name and/or location. To
• Double-click the desired template.
save a file, simply click the Save button in the Quick
Opening a Workbook Launch Toolbar or select Save from the File tab.
TIP: If you save in a file format that doesn’t support
To open a file recently accessed:
all of the features in the file, you’ll be warned first and
• Click on the File tab and click Recent.
allowed to save, potentially losing some data, or to save
• Select the desired file from the list on the
in an alternate format to avoid any data loss.
right of the menu.
TIP: You can click the pushpin to the right To save a file with a different name or in a
of the file to keep the file always on the list. different folder:
• Click on the File tab, and then click Save As.
To open any file:
• Select the desired file type from the Save as type
• Click on the File tab, and then click Open.
drop-down list.
• Navigate to the desired folder in the Open
dialog box. TIP: Select the Excel Workbook format unless you
Microsoft® need to share it with a user running a prior version
Creating a New Workbook • Select the name from the list.
of Excel (or a different spreadsheet platform) or
Help in creating many types of workbooks can be had by simply TIP: Clicking the Preview Pane button you want to save it as a PDF or XPS (smaller and not
using a predefined template. Many templates come with Excel and (in the Open dialog box) will display the editable). The Excel Binary format is very useful for
hundreds more are available from Office Online. selected file in a pane on the right-hand side very large spreadsheets.
To create a new workbook using a template: of the dialog box. Not all file types are able • Navigate to the desired folder.
• Click the File tab and select New. to be previewed, however. • Enter a name in the File name box.
• Click the Open button. • Click the Save button.
2
Working with Documents - Backstage View (continued)
Printing a Workbook - Select collated or uncollated. Use the Document Inspector to remove • Click the Remove All button next
The Backstage view is especially useful - Landscape vs. portrait orientation. things before distribution: to each item to clean up any of that
for printing, as many settings relative to Can also be set on the Page Layout • Click on the File tab; the Info button will content type.
printing can be set in one place and, tab. be selected by default (if you have selected
WARNING! Some of the editing
as the settings are changed, the print - Paper size (letter, legal, etc.). Can something else, just click Info).
may not be able to be undone, so
preview on the right-hand side of the also be set on the Page Layout • Click the Check for Issues button and select
screen will automatically update. tab. Inspect Document. save a copy of the original before
To print a document: - Margins (Normal, Wide or Narrow; - If you have unsaved changes, you will be completing this action.
• Click on the File tab, and then click Custom Margins may also be prompted to save. • Click Close when finished.
Print. specified here). Can also be set on - This dialog box will appear: Use the Compatibility Checker to
• Select the printer you wish to print the Page Layout tab. Microsoft®
verify that nothing will be lost
to and observe its status (Ready, - Specify scaling options (No Scaling
when accessed with a previous
Needs Attention, or the number of [the default], Fit Sheet on One
documents waiting to be printed on Page [per sheet], Fit All Columns version of Excel:
that printer). on One Page, Fit All Rows on One • Click on the File tab; the Info button
TIP: Click on the Printer Properties Page or Custom Scaling Options will be selected by default (if you
link under the printer name if printing [a specified percentage smaller or have selected something else, just
properties (color vs. black & white, larger, or number of pages wide
click Info).
contrast, etc.) need to be set. by number of pages tall]; can also
• Click the Check for Issues button
• Select the number of copies desired. be set on Page Layout Tab, along
• Specify what to print (Print Active with additional options. and select Check Compatibility.
Sheets, Print Entire Workbook, Print • In the Select versions to show
Preparing Documents for
Selection; pages within the above drop-down list, select Excel 97–2003
print range can also be specified). Distribution and/or Excel 2007.
• Review (and modify as necessary) If the document needs to be shared
• Review any potential issues (if any).
any of the following: with others, you may want to remove • Select the desired options and click Inspect.
- Single- or double-sided printing personal information or make sure that • When Excel has finished reviewing the document, TIP: Click Help to review the
(and if double, on the long or short features used will be accessible to those it will display the results. Items that were OK are documentation on the issue; click
side of the page). using previous versions of Excel. shown with a √ and the rest with an !. OK when finished.
Undo & Redo – Quick Access Toolbar
Undo: Reverses the last action (typing or formatting) taken. Remembers the last 99 actions. Redo: Undoes the last undo. Remembers the last 100 Undo actions (most,
Multiple items can be undone in order by selecting them from the drop-down menu. but not all undone actions can be redone).
Introduction to Formulas
This guide provides only a brief overview of formulas and Using Functions • Text. This category is for manipulating text, such
functions; for more information on advanced functions Functions in Excel 2010 are tools that provide the as taking part of a cell (LEFT, MID and RIGHT),
and capabilities, see the QuickStudy Excel – Advanced ability to perform complex calculations. Functions can determining the length of a cell (LEN), or converting
and Excel Tips & Tricks guides. perform financial, analytical and statistical calculations, the case of text (UPPER, PROPER and LOWER),
Order of Operations among others. Simplified, a function is a predefined among others.
formula that can perform common calculations without • Date & Time. Functions used to manipulate dates
When creating any formula, Excel always follows the
the need to create and enter the underlying formulas and times, including: Weekday, which returns the day
mathematical (algebraic) order of operations. When
themselves. [For specific examples and uses of of the week a given date falls on and NOW, which
evaluating a formula, the order is as follows (from highest
common functions, see the QuickStudy Excel Tips & returns the current date and time, and is updated with
to lowest priority):
Tricks guide.] each recalculation.
1. Parentheses ()
• Lookup & Reference. Generally, this group of
2. Negation (EX: –1) Function Categories
functions is used to look up a value in a table,
3. Percentage (%) Excel 2010 has a new Formulas tab on the Ribbon to
returning a value, such as given a ZIP code, weight
4. Exponentiation (^; EX: 2 squared [22] is written as 2^2) make it easy to enter functions. The Function Library
of a package, and shipping charge (HLOOKUP or
5. Multiplication and Division (* and /) group is shown here:
VLOOKUP).
6. Addition and Subtraction (+ and –)
• Math & Trigonometry. Functions commonly used
To illustrate, 9+4*6 on most calculators is 78 (9+4=13*6), Microsoft® in math, except those used in statistics, including:
while Excel will calculate it as 33 (4*6=24+9). Be sure to The Insert Function button can also be used to add a ABS for the absolute value of a number; CEILING,
verify that calculations are created with this in mind. function to a formula. which rounds a number up; FLOOR, which rounds
Creating Simple Formulas Excel breaks down the list of functions (there are several a number down; LOG10 for base-10 logarithmic
The power of Excel lies not in the ability to make data look hundreds of them) into categories to make it simpler to calculations; PI, the constant accurate to 15 digits;
pretty (though it can do that), but rather in its ability to find the function desired. The categories are: RAND to generate a random number between 0 and
perform powerful calculations. All formulas begin with an • Recently Used. Places functions you use frequently 1 (use RANDBETWEEN to find a random number
equal sign (=). Constants (numbers, such as 1or 5), functions at your fingertips; not a function category, but between two specified numbers).
and cell references may all be used in formulas. Functions represented by a button in the Function Library • The More Functions category (on the Ribbon; not
may contain other functions, constants, and/or cell references group. separated in the Insert Function dialog box) includes
as well. A few examples of formulas will help illustrate the • Financial. Functions used in accounting, including: the following:
kinds of formulas that can be used in Excel: PMT to determine a periodic payment for an item a. Statistical. Statistical functions, from the common
Example Result (such as a house or car; EX: in the screenshot on page (including AVERAGE, MEDIAN, MAX and MIN)
=7+12 Places the value 19 in the current cell 1, D21 displays the payment given the balance in D4, to the specialized (from AVEDEV, the average
=5^2 Places the value 25 (52) in the current cell the interest rate in D5, and the number of payments deviation from the mean, to Z.TEST, the 1-tailed
Multiplies the value in cell A1 by the in D19) and DDB for calculating depreciation using P-value of a z-test, and everything in between). Note
=A1*B2 value in B2 and places the result in the the double-declining balance method. that many functions in this category are new to Excel
current cell • Logical. Functions that allow data to be compared 2010, offering many forms of the same function and
logically (using AND, OR and NOT), as well as testing are usually denoted with a “.” in the name, such as
Subtracts the value in A7 from the value in
=D7-A7 a value and returning different results depending on VAR.P (the variance of an entire population) and
D7 and places the result in the current cell
the criteria specified (IF; EX: in the screenshot on VAR.S (the variance of a sample of the population).
Adds all of the cells in the range A5
page 1, E11 displays “Payment OK” if at least the The functions used in previous versions of Excel are
=SUM(A5:D5) through D5 (A5+B5+C5+D5) and places
interest is covered in the monthly payment listed in still available for backwards compatibility and are in
the result in the current cell
D11 or “Payment too low” if it doesn’t). the new Compatibility category.
3
Introduction to Formulas (continued)
b. Engineering. These functions can be used to - Select the type of function that is to be inserted by AutoSum Function
convert between base systems (hexadecimal, clicking the appropriate category from the Select The AutoSum capability uses the SUM function, which
decimal and octal), as well as many functions a category drop-down list in the Insert Function calculates the sum of the numeric values of all cells
commonly used in engineering. dialog box. specified within a range. A range can be specified by
c. Cube. Functions used with cubes; this is - Select the function by clicking the desired function selecting the cells, or Excel 2010 will automatically
advanced functionality beyond the scope of in the Select a function pane. suggest a range. Automatic selections usually include
this guide. - Click the OK button. all cells that are contiguous (in a single direction) to
d. Information. Most of these functions return • A dialog box will be displayed that provides text the cell where the AutoSum function is entered.
a TRUE/FALSE answer, such as ISTEXT boxes in which to enter the information necessary for
To use the AutoSum function:
and ISNUMBER, as well as the value “Not the function. Enter the data that is requested. The data
• Select the range of cells to be totaled, including a
Available” with NA. requested will vary with the function selected (EX: for
blank cell to the right of a row or at the bottom of
e. Compatibility. Functions (mostly statistical) the SUM function, it is the cells to be added together,
a column.
that have improved results or more accurate whereas for the PMT function, an amount, frequency
definitions in Excel 2010 than in previous of payments, and interest rate per period are required. • Click the AutoSum button.
versions. For example, Mode returns the value Some functions, such as NOW, don’t require any OR
that occurs most often in a set of numbers; parameters at all). The current value of the function Click the arrow next to AutoSum to select a count,
in Excel 2010 the MODE.SNGL function is is also displayed and updated as new parameters are average, minimum or maximum.
equivalent, but if the set of numbers has more added. Auto Fill
than one number that is used most frequently, • Click the OK button. Auto Fill is a capability that has been in Excel for
only one will be returned; use MODE.MULTI Function Argument ScreenTip many versions. Its functionality varies, depending
to return an array that contains all the numbers To use function argument ScreenTips, simply on the type of data selected. The following table
that occur most frequently. begin entering a function in a cell and a drop- summarizes the behavior of Auto Fill in various
• Database. This category is not directly accessible down list of functions that start with the characters situations.
on the Ribbon, but is available via the Insert
you’ve entered so far will appear (as shown here) Selected Data Result when data is filled
Function button. It is similar to the Statistical
A single day of the
category, except that criteria can be specified in The next day of the week
week
the data, such as the average salary for female
Multiple days of the The next day of the week
employees (DAVERAGE) vs. the average salary
week based on the series selected
for all employees (AVERAGE).
A single month The next month
To insert a Function (the Insert Function A single number The same number
Wizard): and the arguments for that function will appear near M tichroeso ft® A series of numbers A continuation of the series
• Select the cell in which the function is to be formula in the formula bar (or cell, depending on where A linear continuation of the
entered. the formula is being typed). For example, to get help with A random collection data using the least squares
• Select the function from one of the types listed the IF function, simply enter =IF( and the following will of numbers method
in the Function Library group (on the Formulas
Microsoft® To use Auto Fill:
tab)
• Select the cell or cells with the existing data.
OR appear: . Note
• Place the cursor in the bottom right-hand corner
that the function name and the current argument (the
Click the Insert Function button on either one that is bolded) can be clicked on to obtain the Help of the cell or range of cells on the Auto Fill handle
the Formula Bar or in the Function Library
definition. If more information is needed on the function, . Drag the handle down or to the right to
group.
Help or the Insert Function wizard can be consulted. continue the series.
Basic Formatting of Cells – Home Tab
Clipboard Group Font Group
Fill Color: Changes the Wrap Text: Makes
Cut: Removes the currently selected cell(s) Font: Displays the font of the background color of the text that spills over into
from the workbook and places it/them on the currently selected cell(s) and allows selected cell or drawing object adjacent cells fit within
clipboard for pasting. Cell(s) are not actually it to be changed to any installed to a theme color, a standard the horizontal margins
removed until it/they is/are pasted elsewhere. font via the drop-down menu. color or a custom color, or of the cell by making the
Copy: Copies the currently selected cell(s) Font Size: The size of the selected removes the fill. Color is cell tall enough to hold the
from the workbook and places it/them on the cell(s) is displayed and can be displayed below the paint contents and wrapping the
clipboard for pasting. Click the down arrow and changed by selecting from the bucket; others can be selected text between the left and
select Copy as Picture to copy a picture of the drop-down menu or typing in a from the drop-down list. right margins.
selected cell(s) instead of the actual contents. number (in points). Font Color: Changes the text Align Text Left: Formats
Paste: Places the contents of Increase Font Size: Increases the color of the selected cell(s) to a the selected text to have
the clipboard into the current font size of the selected cell(s) to theme color, a standard color or an even left margin and
document where the cursor is. the next larger size listed in the a custom color. Selected color a ragged right margin
The drop-down arrow describes Font size drop-down menu. shows under the A; others can (default for text).
(waos rksshheoewt nd isphlearyes),, a anpdre vitehwe Dfoenctr seiazsee oFfo nthte S sizeele: cDteedc rceealsle(ss) t htoe bdeo wsne lliesctt.ed from the drop- Cseelenctteerd: texCt ebnettewrse en tthhee
of what the option pointed at the next smaller size listed in the Alignment Group left and right margins of the
would look like (known as Paste Font size drop-down menu. cell, leaving both margins
Preview); other ways to paste Top Align: Align text vertically ragged.
data include Paste Values (of the Bold: Changes the selected cell(s) at the top margin of the cell.
Microsoft® to bold. Align Text Right: Formats
formulas) only, Formulas only (no formatting), Middle Align: Align text the selected text to have a
Transpose (switch the data in rows to columns Italic: Italicizes the selected vertically in the center, between ragged left margin and an
and vice versa), etc. Point at (but don’t click) cell(s). the top and bottom margins of
even right margin (default
an option for a description; click to select it. Underline: Underlines the selected the cell. for numbers).
Format Painter: Picks up the formatting of cell(s); click the down arrow to Bottom Align: Align text Decrease Indent:
the currently selected cell(s) and allows it to be select double underline instead of vertically at the bottom margin
Decreases the left margin
applied on cell(s) highlighted while the format single underline. of the cell. indent of the selected
painter icon is the cursor. When single-clicked, the Borders: The icon for this button Orientation: Rotates the text cell(s) by one character.
next cell(s) highlighted will be changed and the will change, depending on what in the cell 45° or 90° clockwise
Increase Indent: Increases
cursor returned to normal. When double-clicked, border is displayed. Other styles or counterclockwise, or can
the left margin indent of
format painter will remain active until the format can be selected from the drop- stack letters in the normal
the selected cell(s) by one
painter icon is clicked again or <Esc> is pressed. down list. orientation below each other. character.
4
Basic Formatting of Cells - Home Tab (continued)
• To move the contents, click the Cut • Select the style of the border by
Merge and Center: Merges cells Cell Styles: Simple way to format cells
choosing one of the presets or by
horizontally (multiple columns into (good, bad or neutral), as well as notes, button on the Home tab.
clicking the desired border buttons.
one) and centers text across multiple explanation, heading styles, etc. Styles [NOTE: Nothing will be removed
• Select the color of the border by
cells (Merge & Center), or leaves it vary based on Theme specified for the from the worksheet after it is cut
selecting one of the colors from the
left aligned (Merge Across). Rows workbook. until it is pasted elsewhere (unlike
workbook’s Theme colors, standard
are not merged (just columns) unless many other apps).]
Cells Group colors, or any other color in the
Merge Cells is selected which Data that is cut or copied will be
Color drop-down menu.
merges all selected cells into one big Insert Cells: Inserts cells, highlighted like this: . It will
• Select the line style from the list of
cell. Can also Unmerge Cells. rows, columns or sheets; based on the remain available for pasting as long as
line styles.
currently selected cell(s), or click the the cell or cells are thus highlighted.
Number Group • Click OK.
arrow and select what to insert.
To paste an item that is copied
Number Format: Drop- Delete Cells: Deletes cells, or cut: Insert or Delete a Row or
down list of number formats, with rows, columns or sheets based on the • Select the destination cell(s) for the Column
a graphical preview. Can choose
currently selected cell(s), or click the item that is to be pasted. Many times, a new column of data is
currency, number, date, time,
arrow and select what to delete. TIP: Only select one cell if multiple necessary between existing columns,
fractional (instead of decimal) and
scientific formats. Format: Formats cells in 4 cells were copied/cut, and Excel or a new row between existing rows.
categories: will automatically use it as the top Conversely, when data is not needed,
Accounting Number Format: • Cell Size. Change column width or row left corner and will use adjacent it can be easily removed. (Individual
Displays the currency symbol at the height, manually or automatically fitting cells as necessary based on the cells cells can also be inserted or removed
left margin of the cell (instead of the contents. originally selected. If multiple cells in a similar fashion.) All formulas
next to the number, as in currency), • Visibility. Hide or unhide rows, columns were selected in the destination will automatically adjust to refer to
with commas every thousand and or sheets. and the shape of the source isn’t the same data now located in a new
two digits to the right of the decimal. • Organize Sheets. Rename the current the same, an error message will be location.
(Thousands separator [comma] and worksheet, move or copy the worksheet, displayed. To insert a row or column:
number of digits after the decimal
can be changed in the Regional or change the tab color. • Click the Paste button on the • Place the cursor in the row or
• Protection. Lock individual cells or the Home tab. column where the new row or
and Language Options of Control
entire worksheet (allowing changes only The Paste Preview options provide column is desired.
Panel.) Drop-down list displays a
to unlocked cells) to prevent changes the ability to paste only formulas, the • Click the arrow next to the Insert
list of currency formats (including
$, £, ¥ and €). to some cells, such as formulas, while values of the formulas, formatting or Cells button, and then
optionally leaving others changeable, comments from the cell(s) that is/are select Insert Sheet Rows or Insert
Percentage Style: Displays the such as interest rate, mortgage amount copied. It is also very useful to switch Sheet Columns (as desired).
number as a percentage. and time period for a loan; can also data in rows to columns and columns
Comma Style: Same as Accounting prevent formatting, sorting, etc. A to rows (Transpose). To delete a row or column:
Number Format but without the password is prompted for and is required • Place the cursor in the row or
To paste specific portions of
currency symbol. to unprotect the sheet in the future. column to be deleted.
what was copied:
Can also be used to open the Format • Click the arrow next to the Delete
Increase Decimal: Displays one • Copy or cut the cell(s) that are to be
more digit after the decimal; does Cells dialog box. used with Paste Preview. Cells button, and then
not change value in cell or result of Editing Group • Select the destination cell(s) for the select Delete Sheet Rows or Delete
Sheet Columns (as desired).
calculation. Sum: Automatically adds the cells to item(s) that is/are to be pasted.
Decrease Decimal: Displays one the left or above the current cell [see • Select Paste Preview by clicking on Changing Column Widths
less digit after the decimal; does the Formulas section under AutoSum the arrow below the Paste button, As data is entered in cells, sometimes
not change value in cell or result of for more information on this button]; and the window illustrated in the the data requires more space than
calculation. click the arrow for other common Clipboard group will be displayed. available in a column. The column
functions, including average, min and • Select the desired special action widths can be easily modified.
Styles Group
max. and click OK.
Manually changing the column
Conditional Formatting: An TIP: Point to, instead of clicking
Fill: Can create a series based on the width:
updated feature from Excel 2007 on, various options to get a preview
currently selected cell(s), as described • Place the cursor on the line to the
that formats cells according to rules in the worksheet of the results.
in the Formulas section under Auto right of the column to be sized. The
specified (>, <, between, containing Fill. Cell Borders
text, even duplicate values or errors; Microsoft® cursor will appear as follows: .
can also show top or bottom items or Clear: Used to remove the formatting, Drag the line left or right to shrink
% of items, as well as values above contents, hyperlinks or comments or widen the width of the column
or below the average of the values). associated with the selected cell(s), or OR
Can use different colors or fills, data removes all of them. Place the cursor in the column(s) to
bars and/or icons to represent the Sort & Filter: Used to sort data be sized.
various values, and the formatting (selected data or all adjacent cells, with • Click on the arrow to the right of
changes with the underlying data, data based on column with active cell; Format on the Home
unlike the other formatting options can also specify multi-column sorts) tab, and then choose Column
that apply to the cell regardless of and to filter data to show desired data Width from the options.
the value of the cell. Excel 2010 only (drop-down lists of data appear To apply a border to a cell or • Enter the column width from 0
now displays positive and negative in first row of cells; select from list to group of cells: (which hides the column) to 255.
values in the data bars (previously show only matching values). • Select the cell or cells that a border • Click OK.
only positive values). Find & Select: Find (and optionally is to be added to. Automatically sizing the column
Format as Table: Can format a range replace) text throughout the workbook; • Click the arrow to the right of width to fit the widest entry:
of cells as a table with many various format of the text can also be specified. the Borders button on the • Place the cursor on the line to the
styles (similar to the table formatting Can be used to select all cells with Home tab, and choose from 1 of 13 right of the column to be sized. The
styles in Word 2010). Select the formulas, comments, constants, etc. predefined formats; or select More
cursor will appear as follows: .
checkbox My table has headers in Copying & Pasting Data Borders and select custom settings Double-click the line
the range confirmation dialog box to in the dialog box shown here
make the first row of the table display To copy or cut the contents of a cell: OR OR
differently from the rest of the table, • Select the cell(s) that contain the data Right-click on the cell(s) and select Place the cursor in a cell that is too
and provide the ability to filter and that is to be copied or cut. Format Cells from the shortcut wide for the column width.
sort the values in the column by • To copy the contents, click the Copy menu, then select the Border tab. • Click on Format on the
clicking the arrow to the right of the button on the Home tab. The dialog box illustrated above Home tab, and then choose AutoFit
column label, as shown here. OR will be displayed. Column Width from the menu.
5
New User Interface (UI)
The UI is the most striking feature of Excel 2007 & 2010 Exporting the Ribbon to another user’s instance There are 2 types of styles – in-ribbon and drop-down. In-
for those who haven’t used it. It came about as the program of Excel (same or different computer): ribbon galleries have scroll bars to view different options.
became increasingly complex, with more and more options. • Click on the File tab to display the Backstage view and Some galleries can be used in either format (in-ribbon or
This section reviews the changes in each section and is select Options. drop-down); others are one format only. The example here is
designed to help you intuitively know where to look for the • Select the Customize Ribbon option. the Chart Styles in drop-down format. Drop-down galleries
options you want (and along the way, discover some things • Click the Import/Export button and choose Export all are displayed by clicking the drop-down arrow .
you never knew existed!).
customizations. Live Preview
The Ribbon • Browse to the desired location to save the customization Live Preview is a new feature in Excel that allows you
The Ribbon replaces all the menus that were part of every file (be sure the other user has access to the folder, USB to see the results of a change before you make that change.
Windows program and is becoming the standard on many key, network location, etc.), name the file and click Simply point at a gallery option and the selected cell(s),
programs, even Paint and WordPad in Windows 7. It Save. object, etc., will be formatted that way in the document.
is designed to put the tasks and options you need at your • Click OK. However, unless you click on the icon, nothing changes
fingertips and to show you those that are only applicable once you point at something else in the document. Excel
Importing a customized Ribbon:
when a particular kind of object is being worked with. 2010 extends the capability to paste options as well,
• Click on the File tab to display the Backstage view and allowing data to be pasted to be previewed first, and its
1 2 Microsoft® select Options. format selected from available options.
• Select the Customize Ribbon option.
Quick Access Toolbar
• Click the Import/Export button and choose Import
The Quick Access Toolbar places common commands on
customization file.
1. Group. Breaks the Ribbon tab down into related areas of the screen at all times, no matter which tab on the Ribbon
functionality, such as formatting characters or numbers. • Browse to the location where the customization file was is displayed. They are common icons that can always
2. Tab. Groups tasks into areas of functionality. Home saved, select the file and click Open. be used, such as Save and Undo, but any button can be
has the most common options in one place. • Click Yes to acknowledge that all existing customizations added to it by simply right-clicking on a button on any
It is not optimized for any screen resolution and will adapt its will be lost when the new customizations are imported. tab and selecting Add to Quick Access Toolbar.
contents automatically. It supports the traditional button, as • Click OK. Dialog Boxes & Dialog Box Launchers
well as galleries (described in Galleries, next column), and Changing tab and group order: Dialog boxes still exist in Excel 2010, but they are
can launch dialog boxes (windows) if they are required. • Click on the File tab to display the Backstage view and primarily to provide access to advanced functionality.
One of the new features in Excel 2010 is the ability to select Options. For example, Underline is on the Home ribbon, but not
customize the Ribbon (in 2007, it was only possible all underline styles can be set there. To do so, the Format
• Select the Customize Ribbon option.
programmatically). Cells dialog box must be used. To open any dialog box,
• Select the desired tab or group and click the Move Up
Customizing the Ribbon: simply click on the Dialog Box Launcher icon at the
• Click on the File tab to display the Backstage view and or Move Down buttons as necessary (groups end of a Group name.
can be moved between tabs in this way as well).
select Options. MiniBar
• Repeat the previous step as necessary until all
• Select the Customize Ribbon option.
The MiniBar is a small toolbar of the most commonly
• To remove a group from a tab, select it on the right side modifications are complete. used options; it appears when a cell is right-clicked.
of the dialog box (expand the tab and group to view • Click OK.
Super Tooltips
individual commands) and click Remove Contextual Tools
OR Microsoft®
To add a command to an existing custom group (on a
standard or custom tab), expand the tab and group on the
right side, select the command from the list on the left
side of the dialog box (different groups of commands, One of the features of the Ribbon is the ability to show Microsoft®
new tabs based on the object currently being used. In the Super Tooltips are an enhanced version of tooltips that
such as Commands Not in the Ribbon, Macros or All
example shown here, the cursor is in the chart at the bottom have been available for years in previous versions of
Commands, can be selected in the Choose commands Excel. In the past, they simply provided the name of the
of the sheet, so an additional 3 tabs are shown: Design,
from drop-down list), and then click Add tool that was pointed at. In Excel 2007 and 2010, they
OR Layout and Format. Notice that Chart Tools is shown provide descriptions (as shown above) and may also
To add a new tab, click the New Tab button above these 3 tabs, showing the context. Also notice that show a graphical preview of what the tool does (as shown
OR the text Chart Tools is in green, making it easy to see that below). While the tip is displayed, pressing <F1> will
To add a new group, select the desired tab and click the the 3 tabs contain contextual tools. open Help on the subject.
New Group button
Galleries
OR Microsoft®
To rename a tab or group, select it and click Rename.
• Repeat the previous step as necessary until all
modifications are complete. Microsoft®
• Click OK. Keyboard Shortcuts & Navigation
Microsoft®
Restoring a single tab or the entire Ribbon to the
default:
• Click on the File tab to display the Backstage view and
Keyboard shortcuts that start with <Ctrl> (such as
select Options. <Ctrl+B> for bold) work as they have in past versions
• Select the Customize Ribbon option. (see Help for a list of them by looking under Accessibility
Galleries are different from standard options, in that they
• To restore a single tab to the default, select it, click Reset Options). Excel 2010 also uses the function keys (F1–
Defaults, and then select Reset only selected Ribbon show the results of what is selected instead of how to make F10) for many common functions (the same Help topic
tab those results appear (colors, borders, shading, etc). They lists these as well). To make selections from the Ribbon,
OR make it simple to apply a coordinated look to an object. press <Alt> and the shortcuts will appear, first to select
To restore the entire Ribbon to the default, click Reset However, the individual elements can be formatted to the appropriate tab, then the option on the tab (<Alt>
Defaults, and then select Reset all customizations. override the style if desired. does need to be held down), as shown above.
Price: U.S.$5.95 free downloads &
Author: John Hales q u hiucndkresdtsu odf ytit.lecso amt CWues twoemlceor mHoet lyinoeu r# f e1e.8d0b0a.2c3k0 .s9o5 2w2e can maintain
and exceed your expectations.
ISBN-13: 978-142321403-8
ISBN-10: 142321403-X DISCLAIMER: Screen shots may vary depending on the version installed. This guide is based on the software version shipping at the time of
publication and is accurate to that version. Access®, Excel®, Microsoft®, MSN®, Office®, Outlook®, SharePoint® and Windows® are either registered
trademarks or trademarks of Microsoft Corporation® in the United States and/or other countries. Screen shot(s) reprinted by permission from Microsoft
Corporation®. This guide is intended for informational purposes only. Due to its condensed format, it cannot possibly cover every aspect of this
software application. BarCharts, Inc., its writers and editors are not responsible or liable for the use or misuse of the information contained in this guide.
All rights reserved. No part of this publication may be reproduced or transmitted in any form, or by any means, electronic or mechanical, including
photocopy, recording, or any information storage and retrieval system, without written permission from the publisher.
©2010 BarCharts, Inc 0610
6