excel

Cheatsheet Content

Exploring Excel Identifying Screen Elements A spreadsheet program organizes data into horizontal rows and vertical columns. This portion of Excel is called the worksheet area . Rows are numbered and columns are identified by letters. Above the worksheet area, the Ribbon is a collection of commands. To open Excel: Start > All Programs > Microsoft Office > Microsoft Excel 2010 . A blank document, called a workbook , opens. Title bar : Shows application name and file name. Active cell : The currently selected cell, with a thick border. Name box : Shows the cell address (e.g., A1) of the active cell. Insert Function button : Opens the Insert Function dialog box. Formula bar : Displays the contents of the active cell. Scroll bars : Horizontal and vertical, for navigating the worksheet. Sheet tabs : New workbooks open with three worksheets (Sheet1, Sheet2, Sheet3). Status bar : Provides feedback on the current state (e.g., "Ready" for data entry). Zeroing in on the Ribbon The Ribbon (introduced in Office 2007) visually displays commonly used options grouped under functional tabs. Excel program icon : Top-left corner, displays options for minimizing/closing. Quick Access Toolbar : Provides access to basic file functions (Save, Undo, Redo by default). Customizable. Minimize the Ribbon button : Hides all but the Tabs from the Ribbon. Changes to "Expand the Ribbon" button. Tabs : Commands organized under: File, Home, Insert, Page Layout, Formulas, Data, Review, View . Contextual tabs appear when needed (e.g., Chart Tools). Groups : Each tab is divided into collections of related tasks. Dialog Box Launcher : Small arrow icon in bottom-right of some groups, opens a dialog box for more options. Galleries : Collections of related formatting options (e.g., Themes). Introducing the Backstage View Accessed via the File tab. Contains commands for managing documents. Info tab : Establish security options, record file data. Recent tab : Displays a list of recently opened documents. New tab : Open a new blank spreadsheet or create from templates. Print tab : Change page layout, add headers/footers, select printer, specify print area. Save & Send tab : Send as email attachment, create PDF, save online. Help tab : Access help, product support, tutorials. Moving Around the Excel Screen Using the Keyboard Efficient navigation using keyboard shortcuts: Keystroke Movement Arrow keys Moves one cell at a time in the direction of the arrow Tab Moves one cell right Shift+Tab Moves one cell left Enter Moves one cell down Shift+Enter Moves one cell up Page Up Moves one full screen up in the current column Page Down Moves one full screen down in the current column Home Moves to the first cell in column A of the current row Ctrl+Home Moves to first cell in the spreadsheet, cell A1 Ctrl+End Moves to the last cell in the spreadsheet that has any data in it Alt+Page Down Moves right one screen in the current row Alt+Page Up Moves left one screen in the current row Ctrl+Arrow key Moves to the next adjacent cell that contains data Ctrl+Page Down Moves to the next worksheet Ctrl+Page Up Moves to the previous worksheet Using a Mouse Click any cell to make it active. Use scroll bars to make more of the worksheet visible. Click sheet tabs to switch between worksheets. Type a cell address into the Name box and press Enter to jump to that cell. Using the Ribbon for Navigation The Go To command allows activating a specific cell or area. Home > Editing > Find & Select > Go To . In the Go To dialog box, enter the cell address into the Reference box. Click OK . Keyboard shortcut: Press F5 or Ctrl+G to open the Go To dialog box. Entering Excel Data Excel recognizes three types of data: labels (text), values (numbers), and formulas (equations). Select the cell. Type information. Press Enter or Tab to accept and move. Entering Labels Data containing letters is assumed to be a label. Labels are left-aligned and cannot be calculated. If a label is longer than the cell width (default 8.43 characters), Excel extends it beyond the right border. Entering Values Numerical data or data that can be calculated. Values are right-aligned . If a value is too long, Excel may display #### or scientific notation. The data remains intact. Entering Dates Dates contain text and numbers, but are technically values (right-aligned). Excel assigns a numerical value to dates (e.g., Jan 1, 1900 = 1). Dates before this are labels. Excel has 17 preset date formats. Caution : Excel may interpret number-like text (e.g., "01-21") as a date. Using AutoFill Automates entering series data (months, days, numbers, quarters). Type the first item (e.g., "Sunday", "January") into a cell. Click the fill handle (small black box in lower-right corner of active cell). Drag the fill handle across desired cells. Release mouse button; Excel fills the series. For number series (e.g., 1, 2, 3... or 2, 4, 6...): Enter the first two data items in adjacent cells. Select both cells. Drag the fill handle. For label/value sequences (e.g., "Quarter 1"): Excel continues the number series, keeping the label. Selecting Cells on a Spreadsheet Cell range : A single cell, or a group of cells. Selected cells are highlighted. Using the Mouse Click and drag over desired cells. Select an entire row: Click the row number. Select multiple adjacent rows: Drag over several row numbers. Select an entire column: Click the column letter. Select multiple adjacent columns: Drag over several column letters. Select non-adjacent cells: Click first cell, then hold Ctrl and click each additional cell/range. Mouse pointer shape : Should be a white cross for cell selection. Changes to a black arrow over row/column headings. Using a Keyboard Keystroke Result Shift+arrows Selects multiple adjacent cells. Ctrl+Spacebar Selects the entire column. Shift+Spacebar Selects the entire row. Editing a Worksheet To edit cell content: Double-click the cell, or press F2 , to enter Edit mode . Insertion point blinks. Use arrow keys to position insertion point. Type changes. Use Delete (right) or Backspace (left) to remove characters. Press Enter to accept. Alternative : Edit content directly in the Formula bar . Using Undo and Redo Undo : Reverse last action. Click Undo on Quick Access Toolbar or press Ctrl+Z . Click Undo down arrow to undo multiple actions. Redo : Restore undone action. Click Redo on Quick Access Toolbar or press Ctrl+Y . Inserting and Deleting Cells Inserting rows/columns: Select the row/column where new one(s) should be inserted. Home > Cells > Insert (arrow) and choose Insert Sheet Rows or Insert Sheet Columns . Excel inserts new blank row/column and shifts existing content. Inserting single cell(s) or range: Select cell(s) adjacent to insertion point. Home > Cells > Insert (arrow) > Insert Cells . In Insert dialog box, choose shift option (e.g., Shift cells right , Shift cells down ). Click OK . Deleting cells/rows/columns: Select cell(s)/row(s)/column(s) to delete. Home > Cells > Delete (arrow) and choose Delete Cells , Delete Sheet Rows , or Delete Sheet Columns . Deleting a block of cells: Select the block of cells. Right-click selection and choose Delete... . In Delete dialog box, choose shift option (e.g., Shift cells left , Shift cells up ). Click OK . Moving and Copying Data Dragging and Dropping Data Select cells to move. Move mouse pointer to highlighted border (changes to four-sided arrow). Click and hold, drag cells to new destination. Release mouse button. Using Cut, Copy, and Paste Uses the Windows Clipboard. Action Keystroke Ribbon Path Cut Ctrl+X Home > Clipboard > Cut Copy Ctrl+C Home > Clipboard > Copy Paste Ctrl+V Home > Clipboard > Paste To move data: Select data cells. Press Ctrl+X (marquee appears around cells). Select destination cell and press Ctrl+V . To copy data: Select data cells. Press Ctrl+C (marquee appears). Select destination cell and press Ctrl+V . Transpose feature : Home > Clipboard > Paste (arrow) > Transpose . Re-orients data from rows to columns or vice-versa. Copying Values Instead of Formulas To copy only the calculated values, not the formulas: Select cell(s) with formula(s). Ctrl+C . Select destination cell(s). Click arrow on Paste button (Home tab). Choose: Values , Values & Number Formatting , or Values & Source Formatting . Working with Range Names Assigning a descriptive name to a single cell or range of cells for quicker identification. Naming a Range of Cells Select cell(s) to name. Click the Name box (left of Formula bar). Type new name (e.g., "Spring_Sales"). Press Enter . Rules for range names: Cannot contain spaces (use underscore _ ). Must begin with a letter (not a number). Cannot be a valid cell address (e.g., Q3 is invalid, Quarter3 is valid). Shorter names are better, but descriptive. Finding Named Ranges Home > Editing > Find & Select > Go To , select name from list. Click down arrow in Name box , select name from list. Using the Name Manager Formulas > Defined Names > Name Manager . Edit, delete, or create new named ranges. To add a new name: Click New button. Enter range name in Name box. Click Collapse button (at end of Refers to field). Use mouse to select cells for range. Click Collapse button again. Click OK . Understanding Data Validation Restricts the kind of data entered into a cell or range of cells. Data restriction types: Values : Whole numbers or decimals, with min/max values. Dates and Times : Specific date/time or range. Text : Specific length. Lists : Entries must be from a predefined list. Can also decide whether to accept blank cells (uncheck Ignore Blank option). Applying Data Validation Select cells for validation. Data > Data Tools > Data Validation . On Settings tab: Open Allow drop-down, choose validation type. Refine criteria (e.g., Data , Minimum , Maximum ). If List , enter values or reference a named range. Check In-cell drop-down . On Input Message tab: Add a Title and Input Message (pop-up when cell selected). On Error Alert tab: Add a Title and Error Message . Choose Style : Stop : Prevents invalid data. Warning : Allows invalid data after warning. Information : Informs of invalid data, allows entry. Click OK . Using Data Validation Tools Find cells with validation : Home > Editing > Find & Select > Data Validation . Circle Invalid Data : Data > Data Tools > Data Validation (arrow) > Circle Invalid Data . Remove validation : Select cells, Data > Data Tools > Data Validation , click Clear All . Saving a Worksheet Saving the First Time File > Save As . File name : Descriptive, up to 255 characters. Location : Choose folder. Save As Type : Default is Excel Workbook (.xlsx) . Can change to Excel 97-2003 Workbook (.xls) for compatibility. Optional metadata : Save Thumbnail : Stores miniature picture. Tags : Add keywords. Title : Add a descriptive title. Excel has an AutoSave feature (every 10 minutes) but save frequently. Closing and Exiting Excel File > Exit . Alt+F4 . Click Close button (upper-right corner). Click Excel program icon > Close command (upper-left corner). Working with Formulas Formulas perform calculations. They begin with an equals sign = and reference cells. Results are displayed in the cell, formula in the Formula bar. If data changes, Excel automatically recalculates formulas. Using Mathematical Operators Operator Description + Addition - Subtraction * Multiplication / Division ^ Exponentiation Formulas can also contain constants (numbers), e.g., =32*2.75 . Percentages can use % (e.g., 25% ). Creating a Simple Formula Includes one mathematical operator. Click cell for result. Type = . Click/type first cell reference (e.g., B12). Excel outlines it with a colored box. Type operator (e.g., - ). Click/type second cell reference (e.g., C12). Press Enter . Cell references are not case-sensitive (e.g., C12 is same as c12). Using Range Names in Formulas If a cell/range is named (e.g., Gross_Pay ), use its name in formulas: =Gross_Pay*.062 . Creating a Compound Formula Contains more than one mathematical operator. Click cell for result. Type = . Click/type first cell reference. Type operator. Click/type next cell reference. Repeat steps 4-5 as needed. Press Enter . Considering the Order of Operations Excel follows PEMDAS/BODMAS: P arentheses / B rackets E xponents / O rders M ultiplication and D ivision (left to right) A ddition and S ubtraction (left to right) Use parentheses to control order: =(A2+B2+C2)/3 . Editing Formulas Click cell with formula. Formula appears in Formula bar. Click in Formula bar, or press F2 . Referenced cells are outlined. Edit: Drag border of referenced cell to new cell. Move insertion point (click or arrow keys), use Backspace / Delete , type new characters. Press Enter . Quick edit : Double-click result cell to edit directly in cell. Controlling Recalculation By default, Excel recalculates automatically. To change to manual recalculation: Formulas > Calculation > Calculation Options > Manual . To recalculate: Current worksheet: Click Calculate Sheet (Formulas tab). All open workbooks: Click Calculate Now (Formulas tab). Copying Formulas Excel automatically adjusts cell references when copying formulas. Copying with AutoFill For adjacent cells: Click cell with formula. Move mouse to AutoFill handle (lower-right corner, changes to cross). Drag handle to adjacent cells. Release mouse. Copying with Copy and Paste For non-adjacent cells or different worksheets/workbooks: Click cell with formula. Ctrl+C (marching ants border appears). Select destination cell(s). For non-adjacent, hold Ctrl and click. Ctrl+V . Creating an Absolute or Mixed Formula Reference Prevents cell references from changing when formulas are copied. Creating an Absolute Formula Reference Add $ before column letter and row number (e.g., $C$31 ). Click cell for result. Type = . Click/type first cell reference (e.g., C22). Type operator (e.g., / ). Click/type second cell reference. Press F4 to toggle absolute reference (e.g., $C$31 ). Press Enter . Creating a Mixed Formula Reference Only part of the cell address is absolute (e.g., C$10 or $C10 ). Click cell for result. Type = . Click/type first cell reference (e.g., C5). Type operator (e.g., / ). Click/type second cell reference. Press F4 two or three times to cycle through mixed references (e.g., C$10 ). Press Enter . Using Excel Functions Functions are pre-programmed formulas that perform operations on a specified range of cells (e.g., =SUM(C5:C9) ). Understanding Function Syntax All functions have the same basic elements: Starts with = . Function name (e.g., SUM , not case-sensitive). Opening parenthesis ( . Arguments : Data fed to the function, enclosed in parentheses. Separated by commas. Can be cell ranges, values, range names, dates, expressions, text (in quotes). Closing parenthesis ) . Optional arguments are shown in square brackets [] in ScreenTips. Creating a Total with the SUM Function Totals values in a range of cells. Can take multiple ranges as arguments: =SUM(I8:K8,M8,O8:P8) . Entering a SUM Function Manually Uses Formula AutoComplete to help. Click cell for result. Type =SUM . AutoComplete list appears. Select SUM from list (Tab key). Opening parenthesis added. Type first argument (e.g., A1:A40 ) or drag to select range. ScreenTip shows argument. If multiple arguments, type , then next argument. Type ) and press Enter . Entering a SUM Function with the Function Wizard Click cell for result. Click Insert Function button ($f_x$) on Formula bar. In Insert Function dialog box: Type description in Search For a Function box, click Go . Or select category from Or Select a Category list (e.g., Math & Trig ). Select SUM from Select a Function list. Click OK . Function Arguments dialog box appears. Enter arguments (e.g., Number1 ). Click Collapse button ($ \uparrow $) to select range with mouse. Click OK . Entering a SUM Function with the AutoSum Button Quickly enters SUM function for adjacent cells. Click cell for result (ideally adjacent to cells to sum). Click AutoSum button ($\Sigma$) on Home tab. Excel guesses range (marching ants border). Drag to select desired range if guess is wrong. Press Enter . AutoSum can also be used for AVERAGE, COUNT, MIN, MAX. Click arrow next to AutoSum button and select function. Calculating Results Without Entering a Formula Select a range of cells. Excel automatically calculates AVERAGE , COUNT , and SUM and displays them on the Status bar . Right-click Status bar to customize displayed functions (e.g., Numerical Count , Minimum , Maximum ). Nesting Functions Using one function as an argument for another, e.g., =ROUND(AVERAGE(A2:G4),2) . Excel solves innermost calculations first. Click cell for result. Type = . Begin main function (e.g., =ROUND( ). Enter arguments for main function. If argument is another function, type its name and arguments (e.g., AVERAGE(A2:G4) ). Type closing parenthesis for all functions. Press Enter . Using Other Functions Functions are grouped by category on the Formulas tab (e.g., Financial, Logical, Text, Date & Time, Lookup & Reference, Math & Trig ). Using the Financial Functions Used for financial calculations (e.g., loan payments, interest). PMT (Payment): Calculates loan payment, interest earned, or investment needed. Loan payment: =PMT(Rate, NumberofPayments, LoanAmt) . E.g., =PMT(.0725/12, 6*12, -10000) . LoanAmt usually negative. Interest on investment: =PMT(Rate, NumberofPayments, CurrentValue) . E.g., =PMT(.0375, 5, -5000) . Investment needed for future amount: =PMT(Rate, NumberofPayments,, FutureValue) . E.g., =PMT(.06, 15,, -50000) . Note double comma for skipped argument. FV (Future Value): Finds future value of an investment. =FV(Rate, NumberofPayments, PmtAmt, PresentValue, Type) . PV (Present Value): Finds present value of an investment. =PV(Rate, NumberofPayments, PmtAmt, FutureValue, Type) . Using Logical Functions Display text/value or perform calculation based on a condition. IF : =IF(Condition, ActionIfTrue, ActionIfFalse) . Condition uses comparison operators: =, <>, >, =, . E.g., =IF(C4>64, "Pass", "Fail") . Can be nested. SUMIF : Adds cells only if a condition is true. =SUMIF(RangeToCheck, "Condition", RangeToSum) . E.g., =SUMIF(C5:C17, "Eastern", E5:E17) . Condition in quotes. SUMIFS : Adds cells based on multiple conditions. =SUMIFS(RangeToSum, RangeToCheck1, "Condition1", RangeToCheck2, "Condition2", ...) . E.g., =SUMIFS(E5:E17, C5:C17, "Eastern", E5:E17, ">1000000") . COUNTIF : Counts cells that meet a condition. =COUNTIF(RangeToCount, "Condition") . COUNTIFS : Counts cells that meet multiple conditions. =COUNTIFS(RangeToCount1, "Condition1", RangeToCount2, "Condition2", ...) . Using Text Functions Manipulate text, often in database worksheets. CONCATENATE : Combines text from several cells. =CONCATENATE(Text1, Text2, Text3, ...) . Arguments can be cell addresses or text strings (in quotes). E.g., =CONCATENATE(I2, " ", J2) . Can also use & operator. LEFT : Extracts specified number of characters from left of text string. =LEFT(TextCell, NumberOfCharacters) . RIGHT : Extracts specified number of characters from right of text string. =RIGHT(TextCell, NumberOfCharacters) . E.g., =CONCATENATE(LEFT(A8,2), RIGHT(B8,4)) . TEXT : Converts a number to equivalent text with a specified format. =TEXT(Number, "Format") . Format in quotes. E.g., =TEXT(G4, "m/d/yy") . VALUE : Converts text string that looks like a number into an actual numerical value. =VALUE(Text) . Using Date and Time Functions Display dates/times, perform date calculations. TODAY : Displays current date. =TODAY() . Updates automatically on recalculation. NOW : Displays current date and time. =NOW() . Updates automatically. DAY, MONTH, YEAR : Extract day, month, or year from a date. =DAY(Date) , =MONTH(Date) , =YEAR(Date) . E.g., 2011-YEAR(I10) . DATE : Composes a date from year, month, day arguments. =DATE(Year, Month, Day) . E.g., =DATE(YEAR(E4), MONTH(E4)+3, DAY(E4)) . NETWORKDAYS : Calculates number of workdays (Mon-Fri) between two dates, optionally excluding holidays. =NETWORKDAYS(StartDate, EndDate, Holidays) . Holidays is an optional range. Using Lookup and Reference Functions Locate data in large databases. VLOOKUP : Looks up data vertically in a column. =VLOOKUP(ItemToFind, Range, ColumnNumber, Type) . ItemToFind : Value to find in first column of Range . Range : Table array, must include column with ItemToFind . ColumnNumber : Column index from which to return value. Type (optional): TRUE (approximate match, default) or FALSE (exact match). HLOOKUP : Looks up data horizontally in a row. =HLOOKUP(ItemToFind, Range, RowNumber, Type) . Using Mathematical Functions INT : Rounds a number down to the nearest integer. =INT(Number) . E.g., =INT(G4) . ROUND : Rounds a value to a specified number of digits. =ROUND(Number, Digits) . E.g., =ROUND(G21, 2) (nearest penny), =ROUND(G21, 0) (nearest whole number), =ROUND(G21, -2) (nearest hundred). ROUNDUP/ROUNDDOWN : Force rounding up/down. =ROUNDUP(Number, Digits) , =ROUNDDOWN(Number, Digits) . Using Other Functions (Statistical) Often used via AutoSum button. AVERAGE : Mean of values in range(s). =AVERAGE(Range1, ...) . COUNT : Counts cells with numbers in range(s). =COUNT(Range1, ...) . MIN : Lowest value in range(s). =MIN(Range1, ...) . MAX : Highest value in range(s). =MAX(Range1, ...) . COUNTA : Counts non-blank cells (numbers and text). =COUNTA(Range1, ...) . COUNTBLANK : Counts empty cells. =COUNTBLANK(Range1, ...) . Correcting Formula Errors Excel flags some errors with messages (e.g., #DIV/0! ) but not all. Can also trace precedents/dependents. Displaying Formulas in a Worksheet Shows formulas instead of results for easier checking. All-or-nothing, worksheet-level feature. Go to worksheet. Formulas > Formula Auditing > Show Formulas . (Cells expand). Keyboard shortcut : Ctrl+` (accent key). Understanding Formula Error Messages Error messages start with # . #### : Cell content is wider than column. Widen column. #VALUE! : Wrong data type used in formula/function. #DIV/0! : Division by zero (cell is zero or empty). Can use =IF(B5=0, "", A5/B5) to avoid. #REF! : Invalid cell reference (e.g., deleted/pasted over referenced cells). #NUM! : Non-numeric argument in function or number too large/small. #NULL! : Missing comma between ranges in a function (e.g., =SUM(A1:A6 C1:C6) ). #NAME? : Excel doesn't recognize text (misspelled range name/function, text string not in quotes, missing colon in range). #N/A : Value not available (e.g., VLOOKUP cannot find item, missing required argument). Circular Reference : Formula references itself (e.g., =SUM(B2:B10) in cell B10). Message on status bar. Avoiding Common Formula Errors Start all formulas with = . Match all parentheses. Use four-digit years in date formulas. Referencing cells on other worksheets: ='Sheet Name'!CellRef . Inconsistent formulas in surrounding cells are flagged. Formulas omitting adjacent cells that contain data are flagged. Enter all required arguments for functions; use correct data types. Do not include formatting ( $ , , ) in numeric arguments. Telling Excel Which Errors to Flag Customize error checking rules: File > Options > Formulas . In Error Checking Rules section, select/deselect checkboxes for errors to flag. Click OK . Telling Excel to Flag Result Cells with an Error Displays a small green triangle in upper-left corner of error cells. File > Options > Formulas . Select Enable Background Error Checking . (Optional) Change error triangle color. Click OK . To fix flagged errors: Click flagged cell. Error Checking button ($!$) appears. Click button. Menu of options: Help on this Error, Trace Error, Ignore Error, Edit in Formula Bar, Error Checking Options . Checking for Errors Automatically One at a Time Like a spelling checker. Formulas > Formula Auditing > Error Checking . Error Checking dialog box highlights first error. Review error description. Choose action: Help on this Error, Trace Error, Ignore Error, Edit in Formula Bar, Show Calculation Steps, Options . Fix error. Click Next for next error. Using the Watch Window Monitors values in selected cells, even if off-screen, as formulas/data change. Formulas > Formula Auditing > Watch Window . Click Add Watch . Type/select cell(s) to watch. Click Add . Make changes, observed in Watch Window. Close Watch Window. Evaluating a Formula Steps through complex formulas to see intermediate results. Click cell with formula. Formulas > Formula Auditing > Evaluate Formula . Click Evaluate repeatedly to step through calculation. Click Restart or Close . Identifying Formula Precedents and Dependents Dependents : Cells whose value depends on the current cell. Click cell. Formulas > Formula Auditing > Trace Dependents . Blue arrows appear. Precedents : Cells referred to by the formula in the current cell. Click cell. Formulas > Formula Auditing > Trace Precedents . Blue arrows appear. Red arrows indicate error. Worksheet icon means cell is on another sheet/workbook. To clear arrows: Formulas > Formula Auditing > Remove Arrows . Tracing an Error Quickly trace precedents of a cell with a recognized error message. Click cell with error. Formulas > Formula Auditing > Error Checking (arrow) > Trace Error . Arrows point to precedents. Controlling Whether Errors Print To prevent error messages (e.g., #DIV/0! ) from printing: Page Layout > Page Setup > Dialog Box Launcher . Click Sheet tab. Click down arrow on Cell Errors As menu. Choose blank , dashes , or #N/A . Click OK . File > Print , then Print . Making your Worksheet Look Good Adjusting Columns and Rows Ensures legibility. Adjusting affects onscreen display and print layout. Changing Column Width: Double-click : Point to seam between column letters, double-click to auto-fit to widest entry. Click and drag : Point to seam, drag left/right. Ribbon : Select columns, Home > Cells > Format > Column Width . Enter specific measurement. Changing Row Height: Double-click : Point to seam between row numbers, double-click to auto-fit to tallest entry. Click and drag : Point to seam, drag up/down. Ribbon : Select rows, Home > Cells > Format > Row Height . Enter specific measurement. Formatting Cell Content Using Home tab for styling. Applying Fonts, Sizes, Styles, and Text Color: Font : Choose from installed fonts. Size : Adjust font size (points). Increase/Decrease Font Size : Changes by 2-point increments. Bold, Italic, Underline : Apply emphasis. Font Color : Choose from Office Themes, Standard colors, or More Colors palette. Adding Borders and Shading: Fill Color : Apply background color to cells. Borders : Apply borders to cell sides. More Borders opens Format Cells dialog for customization (thickness, style, color). Working with Alignment and Spacing: Vertical Alignment : Top, Middle, Bottom. Orientation : Angle column headings (e.g., Format Cells > Alignment tab). Wrap Text : Allows text to wrap within cell. Horizontal Alignment : Align Text Left, Center, Align Text Right. Indent : Increase/Decrease indent. Merge & Center : Merge selected cells (e.g., for titles). Options include Merge Across, Merge Cells, Unmerge Cells . Applying Numeric Formatting Home > Number group. Number Format : Drop-down menu with options like General, Number, Currency, Accounting, Date, Time, Percentage, Fraction, Scientific, Text, More Number Formats . Currency : Applies currency symbol. Percent Style : Converts to percentage (rounds up by default). Comma Style : Adds thousands separator and two decimal places. Increase/Decrease Decimal : Add/remove decimal places. Right-click cell > Format Cells for more options (six tabs). Applying Conditional Formatting Formatting applied when certain conditions are met. Creating Cell Rules (Highlighting Rules): Select cells. Home > Styles > Conditional Formatting > Highlight Cells Rules . Select condition (e.g., Greater Than ). Enter value and choose formatting style. Click OK . Top/Bottom Rules work similarly (e.g., Top 10 Items, Above Average). Creating a New Rule: Select cells. Home > Styles > Conditional Formatting > New Rule . Select Rule Type (e.g., Format only cells that contain ). Edit rule (e.g., Format Style, Value Type, Value, Color ). Click OK . To remove conditional formatting: Conditional Formatting > Clear Rules . Using Data Bars, Color Scales, and Icon Sets: Graphical representations of values within cells. Data Bars : Horizontal bars in cells, length proportional to value. Color Scales : Cell background color changes based on value (e.g., red-yellow-green). Icon Sets : Icons (arrows, flags) indicate value range/trend. Select cells, Conditional Formatting , then choose from Data Bars, Color Scales, Icon Sets . Applying Office Themes and Layouts Page Layout tab tools adjust the overall look of the entire worksheet. Themes : Collections of compatible colors, fonts, borders. Can customize Colors, Fonts, Effects individually. Page Setup : Controls print margins, orientation, paper size, print area, page breaks, background, print titles. Scale to Fit : Forces content to fit specified pages (e.g., Fit Sheet on One Page ). Sheet Options : Controls visibility/printing of gridlines and headings. Arrange : Controls stacking order of graphic elements (charts, pictures, shapes). Managing Large Data Working with Multiple Worksheets An Excel workbook starts with three worksheets (Sheet1, Sheet2, Sheet3). Can rename, add, delete. Moving Between Worksheets: Click its tab. Use tab-scrolling buttons (left of tabs) to reveal hidden tabs. Drag tab split box (left of first tab-scrolling button) to adjust visible tabs. Inserting Additional Worksheets: Home > Cells > Insert (arrow) > Insert Sheet (inserts before current sheet). Right-click sheet tab > Insert > Worksheet . Click Insert Worksheet button (+) next to sheet tabs. Deleting Worksheets: Click tab of sheet(s) to remove. Home > Cells > Delete (arrow) > Delete Sheet , OR right-click tab > Delete . If sheet has data, confirm deletion. (Cannot Undo). Renaming Worksheets: Click tab of sheet to rename. Home > Cells > Format > Rename Sheet , OR right-click tab > Rename , OR double-click tab. Type new name (up to 31 characters, letters, numbers, spaces, special chars). Press Enter . Copying Worksheets: Click tab of sheet(s) to copy ( Ctrl+click for multiple). Home > Cells > Format > Move or Copy Sheet , OR right-click tab > Move or Copy . In Move or Copy dialog: Select To Book (current or another open workbook). Select Before Sheet (where to place copy). Check Create a Copy . Click OK . (Copies named e.g., "Sheet1 (2)"). Quick copy : Ctrl+drag tab to new location. Moving Worksheets: Click tab of sheet(s) to move ( Ctrl+click for multiple). Home > Cells > Format > Move or Copy Sheet , OR right-click tab > Move or Copy . In Move or Copy dialog: Select To Book . Select Before Sheet . Do NOT check Create a Copy . Click OK . Quick move : Drag tab to new location. Changing Worksheet Tab Colors: Click tab(s) to color. Home > Cells > Format > Tab Color , OR right-click tab > Tab Color . Select color from palette. ( More Colors for custom). Displaying Data Stored Elsewhere in the Workbook Reference cells on same or other worksheets to avoid retyping. Click cell for data to appear. Type = . For same worksheet: Click/type cell address (e.g., =D4 ). For another worksheet: Click sheet tab, then click cell. Or type ='Sheet Name'!CellRef (e.g., ='Unit Prices'!B8 ). Press Enter . Displaying Data Stored in Another Workbook Reference data from other open workbooks. Open both workbooks. Click cell for data to appear. Type = . Switch to other workbook/worksheet, click cell. Or type =[WorkbookName.xlsx]SheetName!CellRef (e.g., =[HHQtr1Breakdown.xlsx]January!K8 ). Press Enter . Warning : Excel will prompt to update content when re-opening workbook with external links. Click Enable Content . Linking to Elsewhere in the Same or Different Workbook Hyperlink : Text or graphic that, when clicked, displays related information. To link within current workbook: Select cell/graphic for link. Insert > Links > Hyperlink . Click Place In This Document . Enter cell address in Type The Cell Reference . Select worksheet if needed. (Optional) Click ScreenTip to add description. Click OK . To link to another workbook or file: Select cell/graphic. Insert > Links > Hyperlink . Click Existing File or Web Page . Enter path in Address , or select file from Look In . (Optional) Click Bookmark to link to specific place in file. (Optional) Click ScreenTip . Click OK . To link to a Web Page: Select cell/graphic. Insert > Links > Hyperlink . Click Existing File or Web Page . Enter URL in Address , or click Browse the Web . (Optional) Click Bookmark to link to specific part of page. (Optional) Click ScreenTip . Click OK . To link to a New Workbook: Select cell/graphic. Insert > Links > Hyperlink . Click Create New Document . Type name in Name of New Document . Choose Edit the New Document Now or Later . (Optional) Click ScreenTip . Click OK . To link to an E-Mail Address: Select cell/graphic. Insert > Links > Hyperlink . Click E-mail Address . Enter address in E-Mail Address . Type subject in Subject . (Optional) Click ScreenTip . Click OK . Manage Links: Edit text: Click nearby cell, navigate with arrow keys, edit in Formula bar. Edit target: Click link cell, Insert > Links > Hyperlink , make changes. Remove: Click link cell, Insert > Links > Hyperlink > Remove Link . Using Find and Replace Searching for Data Searches data, formulas, or comments. (Optional) Select range. Otherwise, click any cell. Home > Editing > Find & Select > Find . Type data to find in Find What box. (Optional) Click Options : Format : Search by format. Within : Worksheet or Workbook. Search : By Rows or By Columns. Look In : Formulas, Values, or Comments. Match Case : Exact case match. Match Entire Cell Contents : Exact cell match. Click Find Next or Find All . Click Close . Wildcards : ? (single character), * (any number of characters). Use ~? or ~* to search for literal wildcards. Replacing Cell Data (Optional) Select range. Home > Editing > Find & Select > Replace . Type data to find in Find What . Type replacement data in Replace With . (Optional) Click Options for search/replace format, scope, direction, match options. Click Find Next , Replace , or Replace All . Click Close . Managing Worksheet Views Zooming In and Out Affects onscreen size, not print size. 100% : View > Zoom > 100% . Zoom to Selection : View > Zoom > Zoom to Selection . Custom Zoom : View > Zoom > Zoom . Select percentage or enter custom value. Mouse : Ctrl + scroll wheel . Zoom slider : Bottom-right of Excel window. Changing Worksheet Views Normal view : Default, data in rows/columns, marked cell. Most work done here. Page Layout view : Displays as printed (page by page, margins, headers/footers, rulers). Editable. Page Break Preview : Condenses print area, shows/allows adjustment of page breaks (blue dashed lines for auto, solid for manual). Full Screen view : Maximizes viewing space by hiding Ribbon, Quick Access Toolbar, etc. Press Esc to exit. Switch views: View > Workbook Views , or use icons in Status bar. Freezing Row and Column Headings Keeps headings visible when scrolling in large worksheets. Click cell below column headings and to the right of row headings to freeze. View > Window > Freeze Panes > Freeze Panes . Dark lines appear. Freeze First Column / Freeze Top Row options also available. To unfreeze: View > Window > Freeze Panes > Unfreeze Panes . Splitting the Excel Screen Divides window into up to four panes, each with independent scrolling (in one direction). Click any cell. View > Window > Split . Split bars appear. Scroll within panes. To remove split: View > Window > Split again. Hiding Rows and Columns Temporarily hides data, not deleted. Not printed. Select row(s)/column(s) to hide. Home > Cells > Format > Hide & Unhide > Hide Rows/Columns , OR right-click selected rows/columns > Hide . To unhide: Select rows/columns adjacent to hidden ones (e.g., if row 23 is hidden, select 22 and 24). Home > Cells > Format > Hide & Unhide > Unhide Rows/Columns , OR right-click selection > Unhide . Sorting Data Arranges lists in a specific order (ascending/descending) by one or more fields. Sorting from the Ribbon Quick sort by a single column. Click any cell in the column to sort by. Data > Sort & Filter > A-Z (Ascending / Smallest to Largest) or Z-A (Descending / Largest to Smallest). Working with the Sort Command For multiple-field sorting. Click any cell in the database/list. Data > Sort & Filter > Sort . Sort dialog box appears. Sort by : Choose first field from drop-down. Sort On : Choose Values (default), Cell Color, Font Color, Cell Icon . Order : Choose A to Z, Z to A , or Custom List . Click Add Level for additional sort fields. Repeat steps 3-5 for new level(s). Click OK . My Data Has Headers checkbox ensures first row isn't sorted as data. Options button allows case sensitivity and changing sort orientation (top-to-bottom vs. left-to-right). Creating a Subtotal Report Performs calculations (e.g., COUNT, SUM, AVERAGE) on sorted groups within a list. Requires a sorted list that creates groups (e.g., sorting by State). Click any cell in the list. Data > Outline > Subtotal . Subtotal dialog box appears. At Each Change In : Choose field for grouping (e.g., State ). Use Function : Choose calculation (e.g., Average , Count , Sum ). Add Subtotal To : Choose field(s) to apply calculation to. (For first subtotal) Ensure Replace Current Subtotals is checked. Click OK . Expanding and Collapsing Your Subtotal Report Numbered buttons appear left of Column A. 1 button : Collapses to show only grand total. 2 button : Shows major subtotals (e.g., state averages). 3 button : Shows all detail. To add more subtotal levels: Repeat steps, but uncheck Replace Current Subtotals . To remove: Open Subtotal dialog box, click Remove All . Creating Manual Groups from Sorted Rows Groups data without calculating subtotals. Uses Data > Outline > Group button. Sort list. Select rows that make up a group. Click Group button. (1 and 2 buttons appear). Repeat for other groups. To collapse/expand a group: Click 1 or 2 button. To ungroup: Select group rows, click Ungroup button. Filtering Data Temporarily hides data that doesn't meet criteria. Data is not deleted. Creating an AutoFilter Click anywhere in worksheet table. Data > Sort & Filter > Filter . Filter arrows ($ \nabla $) appear on each column header. Applying the Filters Click filter arrow on column header. Drop-down menu shows unique entries. Uncheck Select All . Check desired filtering option(s). Click OK . Filter arrow changes to funnel icon. Status bar shows number of records displayed. To remove filter: Click funnel icon > Clear Filter , OR check Select All , OR Data > Sort & Filter > Filter (toggles off). Copying Filtered Data Select filtered data. Home > Clipboard > Copy . Paste into new location (Excel or other app). Only visible data is copied. Performing a Secondary Filter Selection Apply filters sequentially to further narrow data. Apply first filter using filter arrow. Click filter arrow for next column. Choose criteria. Repeat as needed. To clear all filters: Data > Sort & Filter > Clear . Exploring Special Filters Searching for Blank Cells : Data > Sort & Filter > Filter . Click filter arrow for column. Uncheck Select All . Check (Blanks) at bottom of list. Click OK . Filtering by Values (Number Filters) : For numerical data. Click filter arrow for column. Choose Number Filters (e.g., Equals, Greater Than, Top 10, Above Average ). Enter criteria in dialog box. Click OK . Filtering by Date (Date Filters) : Common filters : Fixed criteria (e.g., Equals, Before ). Dynamic filters : Factor in today's date (e.g., Next Week, This Month, Quarter 1 ). Click filter arrow for column. Choose Date Filters . Select filter. Enter criteria. Click OK . Filtering by Color : If cells/fonts are colored. Click filter arrow for column. Choose Filter by Color . Select color. Creating Custom Filters For more specific text or multiple criteria. Filtering Text : Click filter arrow for column. Choose Text Filters (e.g., Equals, Begins With, Contains ). Enter criteria in dialog box. Click OK . Adding Multiple Comparison Criteria : Using Custom AutoFilter dialog box. Apply initial filter. In Custom AutoFilter dialog, choose AND or OR . Select another filter option, type term. Click OK . Wildcards : ? (single character), * (any number of characters). Using Formulas with Filtered Data Standard functions (SUM, COUNT, AVERAGE) calculate on total database, not filtered view. Use SUBTOTAL function to calculate only visible cells. =SUBTOTAL(function_number, range) . Function Number Formula 1 AVERAGE 2 COUNT 9 SUM ...and others (see Table 8-3 in original document) Preparing to Print Setting the Print Area Specifies which cells to print. Highlight range to print. Page Layout > Page Setup > Print Area (arrow) > Set Print Area . Dotted lines indicate print area. To clear: Page Layout > Page Setup > Print Area (arrow) > Clear Print Area . Previewing Your Worksheet File > Print opens Print Preview window. Changing Orientation Portrait (vertical, default) or Landscape (horizontal). In Print Preview: Click Orientation option, select. Choosing a Paper Size Default is Letter (8.5x11 inches). In Print Preview: Click Paper Size option, select from list. More Paper Sizes : Add custom sizes via Page Setup dialog. Making It Fit (Scaling) Forces print area to fit required number of pages without changing worksheet formatting. Scale Option Description Fit Sheet on One Page Entire print area scaled to fit on a single page. Fit All Columns on One Page Entire print area scaled to be one page wide. Fit All Rows on One Page Entire print area scaled to be one page long. In Print Preview: Click Scaling option, select desired scale. Switching Worksheet Views for Layout View > Workbook Views or Status bar icons. Normal view : Standard working view. Page Layout view : Shows pages as they print, including margins, headers/footers, rulers. Editable. Page Break Preview : Condenses print area, allows dragging blue lines to adjust page breaks. Page Layout tab (Ribbon) groups: Themes, Page Setup, Scale to Fit, Sheet Options, Arrange . Working with Margins Whitespace around printed content. Predefined: Page Layout > Page Setup > Margins . Custom: Margins > Custom Margins (opens Page Setup dialog). Use spinners or type values. Can also center on page. In Print Preview: Click Margins icon (lower-right), drag square handles. Manually Changing Page Breaks In Page Break Preview mode: Drag blue dashed lines (automatic) to new position. Becomes solid (manual). To insert: Select first cell of new page. Page Layout > Page Setup > Breaks > Insert Page Break . Solid blue line appears. To remove: Drag off printout. To reset all: Page Layout > Page Setup > Breaks > Reset All Page Breaks . Creating Backgrounds Image behind gridlines/cell content, visible only in Excel (not printed). Page Layout > Page Setup > Background . Select picture, click Insert . To remove: Delete Background button appears where Background button was. Printing Gridlines and Headings By default, not printed. To print: Page Layout > Sheet Options . Check Print box for Gridlines or Headings . Including Titles (Repeating Rows/Columns) For worksheets spanning multiple printed pages, repeat header rows/columns. Page Layout > Page Setup > Titles . Page Setup dialog opens to Sheet tab. Click Collapse button for Rows to Repeat at Top . Select row(s). Click Collapse . Repeat for Columns to Repeat at Left if needed. Click OK . Adding Headers and Footers Text/images at top/bottom of printed page. Repeated on all pages. Using predefined: In Page Layout view : Click in header/footer area (left, center, right section). Header & Footer Tools Design tab appears. Header & Footer Tools Design > Header & Footer > Header (or Footer) . Select option from drop-down menu. Using custom: In Page Layout view : Click in header/footer area. Header & Footer Tools Design > Header & Footer Elements . Select elements (e.g., Page Number, Current Date, File Name ). Excel inserts code (e.g., &[Page] ). (Optional) Type directly into area. (Optional) Use Go to Header/Footer for navigation. (Optional) Check Different First Page or Different Odd & Even Pages . Ampersand (&) in text : Type && to display a literal ampersand. Inserting a Watermark Graphic behind text on printed document (e.g., company logo, "Draft"). Insert > Text > Header & Footer . Design tab appears. Click Picture button. Insert Picture dialog. Select picture, click Open . ( &[Picture] code inserted). Modify with Format Picture button on Design tab. Printing and Other Output Formats Printing Your Workbooks Connect to printer. File > Print . Print Preview window appears. Review, adjust settings. Click Print . Keyboard shortcut : Ctrl+P . Handling Special Printing Needs Selecting Multiple Worksheets to Print: Single worksheet: Click tab. Adjacent: Click first tab, Shift+click last tab. Non-adjacent: Click first tab, Ctrl+click additional tabs. Then File > Print . Changing Orientation in the Same Print Job: Select worksheet tab. Page Layout > Page Setup > Orientation (arrow) . Set orientation. Repeat for other worksheets. Select all desired worksheet tabs. File > Print , confirm. Click Print . Printing Multiple Pages on a Single Sheet: Select worksheet(s) that print on multiple pages. File > Print . Click Printer Properties link. In printer dialog, click Finishing tab. Set Pages Per Sheet . Click OK . Print. (All sheets must have same page orientation). Making Comments Visible: Page Layout > Page Setup > Dialog Box Launcher . Click Sheet tab. Click Comments arrow. Choose As displayed on sheet or At end of sheet . Click OK . Print. Showing Off Your Formulas: Formulas > Formula Auditing > Show Formulas . Print worksheet. Click Show Formulas again to hide. Printing Named Ranges: Select named range (from Name box drop-down). Page Layout > Page Setup > Print Area (arrow) > Set Print Area . Print. If non-adjacent cells in named range: Excel adds page breaks. Or hide unused rows/columns before setting print area. Printing Charts Click to select chart. File > Print . Chart appears scaled to full page in preview. Print. To print in black and white: Page Setup link > Chart tab > Print in Black and White . Printing a Draft Strips images, charts, lines for faster printing. Format remains, but elements are not printed. Page Layout > Page Setup > Dialog Box Launcher . Click Sheet tab. Check Draft Quality option. Click OK . Print. Changing Printers File > Print . Click Printer drop-down, select printer. Review preview, click Print . Printing Without Opening Excel From Windows Explorer: Right-click Excel file name. Choose Print . (Prints active worksheet from last save). Choosing an Alternative to Printing Creating a PDF: File > Save As . Save As Type : Select PDF . Click Save . E-Mailing Your Worksheet: File > Save & Send > Send Using Email . Select Send as Attachment (original format) or Send as PDF . Complete email, click Send . Printing from the Quick Access Toolbar Add Quick Print button to Quick Access Toolbar ( Customize QAT > Quick Print ). Click Quick Print button. (Sends to Windows print queue, prints active worksheet with default settings, no comments). Managing Print Queue: Double-click printer icon in Windows taskbar. Right-click Excel file in queue. Choose Pause or Cancel . Click Printer > Close . Creating Excel Charts Graphical representation of data. Turn numbers into compelling images. Choosing the Right Kind of Chart Types of charts ( Insert > Charts group): Column : Shows trends and comparisons. 2D/3D, various subtypes (individual/stacked columns). Line : Shows trends. 2D/3D, with/without data points. Pie : Shows proportions of a whole (one data series only). 2D/3D, with/without exploded slices. Bar : Same as Column, but horizontal bars. Area : Combines trend/comparison of Column/Line with simple comparison of Pie. 2D/3D, stacked/rows. Scatter : Shows frequency, relationships between two numerical variables. Other Charts : Stock, Surface, Doughnut, Bubble, Radar. Selecting Chart Data and Creating the Chart Open worksheet with data. Select cells to plot, including column/row headings. Insert > Charts . Click button for desired chart type, select subtype. Chart appears, overlapping data. Drag to unpopulated section. Move chart to own sheet : Right-click chart > Cut . Go to new sheet, Paste . Or Chart Tools Design > Location > Move Chart . Resizing Your Chart Drag corner handles while holding Shift to maintain aspect ratio. (Not possible if chart is on its own sheet). Changing the Chart Options Chart Tools (appears when chart is selected) has Design, Layout, Format tabs. Switching Your Chart’s Focus: Swaps data between legend and category (X) axis. Click chart. Chart Tools Design > Data > Switch Row/Column . Changing Chart Colors: Individual data points/series : Double-click column/bar/slice/line. Format Data Series dialog opens. Or select data point, Chart Tools Format > Shape Styles > Shape Fill . Entire chart : Click chart. Chart Tools Design > Chart Styles (scroll through options). Formatting Your Chart Wall, Area, and Gridlines: Double-click element (wall, area, gridline). Appropriate Format dialog opens (e.g., Format Chart Area ). Format tab : Select element, Chart Tools Format > Shape Styles > Shape Fill . Format Selection button : Select element, Chart Tools Layout > Current Selection > Format Selection . Customizing Your Legend: Format font : Select legend, Home > Font group, or Chart Tools Format > WordArt Styles > Text Fill/Outline/Effects . Placement : Double-click legend, Format Legend dialog. Or Chart Tools Layout > Labels > Legend . Adding Titles: Chart Title : Chart Tools Layout > Labels > Chart Title . Choose position, type text. Axis Title : Chart Tools Layout > Labels > Axis Titles . Choose axis (Horizontal/Vertical), position, type text. Text Boxes : Chart Tools Layout > Text > Text Box . Draw box, type text. Enhancing Your Chart with Data Labels and Tables: Data Labels : Show actual values for data points. Click chart. Chart Tools Layout > Labels > Data Labels . Choose Show or position. (For Pie charts) More Data Labels Options to show percentage. Data Table : Displays underlying data in a table within the chart. Click chart. Chart Tools Layout > Labels > Data Table . Choose Show Data Table or Show Data Table with Legend Keys . Changing Chart Types Can create issues if new type is incompatible with data (e.g., Pie chart with multiple data series). Click chart. Chart Tools Design > Type > Change Chart Type . Select new type and subtype. Click OK . Adding/Removing Data to/from a Chart: Click chart. Chart Tools Design > Data > Select Data . In Select Data Source dialog, drag through new range or use Ctrl to add/remove cells. Update Legend Entries and Axis Labels as needed. Inserting Illustrations Graphical content objects (pictures, clipart, shapes, SmartArt) to enhance data. Adding Saved Pictures Digital photos, line art, engineering drawings. Select cell for picture. Insert > Illustrations > Picture . Insert Picture dialog. Select image, click Insert . Alternatively : Copy image to Clipboard, Home > Clipboard Dialog Box Launcher , click image. Using Clipart Microsoft's collection of images (illustrations, photos, video, audio). Insert > Illustrations > Clip Art . Clip Art pane opens. In Search For box, type description. Check Include Office.com content if online. (Optional) Filter Results Should Be by media type. Click Go . Click desired image (or arrow > Insert ). Formatting Illustrations When selected, Picture Tools Format tab appears. Adjusting Illustrations: Corrections : Brightness, contrast, sharpen/soften. Color : Tone, saturation, color filter. Artistic Effects : Chalk sketch, pencil sketch, blur, etc. Compress Pictures : Reduces file size (may reduce quality). Change Picture : Replaces with another image. Reset Picture : Restores defaults, removes all adjustments. Applying Picture Styles: Tools in Picture Styles group (e.g., Picture Border, Picture Effects ). Arranging Illustrations: Move by dragging. Resize by dragging sizing handles. Selection Pane : Picture Tools Format > Arrange > Selection Pane . Lists all objects, allows naming and selecting. Rotate : Rotates selected image. Bring Forward / Send Backward : Changes stacking order. Align : Aligns multiple selected pictures. Group / Ungroup : Combines multiple pictures into one object. Changing the Size: Use Height and Width boxes in Picture Tools Format > Size group. Crop : Remove unwanted portions. Select picture, Picture Tools Format > Size > Crop . Drag cropping handles. Working with Shapes Predefined shapes (arrows, boxes, circles, flowcharts). Insert > Illustrations > Shapes . Gallery appears. Select shape. Mouse pointer changes to cross. Drag to draw shape. (Optional) Change color/size using Picture Tools Format tab. Creating SmartArt Professionally designed graphics combining shapes and text to show relationships, cycles, etc. Insert > Illustrations > SmartArt . Choose a SmartArt Graphic dialog. Select category ( List, Process, Cycle, Hierarchy, Relationship, Matrix, Pyramid ). Select object. Click OK . Object inserted. SmartArt Tools Design and Format tabs appear. Customizing SmartArt: Add text : SmartArt Tools Design > Create Graphic > Text Pane . Type text in bulleted list. Change layout : SmartArt Tools Design > Layouts . Change color : SmartArt Tools Design > SmartArt Styles > Change Colors . Adding Pictures to SmartArt: For SmartArt graphics with picture options. Select Picture SmartArt . Add text. Double-click picture icon on graphic. Insert Picture dialog. Select picture, click Insert . Changing SmartArt Shapes: Select individual object(s) within SmartArt. SmartArt Tools Format > Shapes > Change Shape . Select new shape. Using Screenshots Captures images of open windows on computer. Insert > Illustrations > Screenshot . Drop-down menu of available windows. Select window. Screenshot inserted. Screen Clipping tool : Screenshot > Screen Clipping . Dulls screen, drag crosshairs over area to capture. Adding WordArt Applies special effects (styles) to text, making it a graphic element. Insert > Text > WordArt . Gallery appears. Select style. Text box with "Your Text Here" inserted. Type own text. Drawing Tools Format tab appears. Use Drawing Tools Format > WordArt Styles > Text Effects > Transform to change shape of text. Change background shape: Drawing Tools Format > Insert Shapes > Edit Shape . Using Security Options Inspecting for Private Information Remove metadata (author, company, old versions, hidden data, comments) before sharing. Save workbook. File > Info . Click Check for Issues > Inspect Document . Document Inspector dialog. Select checkboxes for data types to search/remove. Click Inspect . Click Remove All for desired items. Click Close . Save workbook. Hiding Data Rows/Columns/Cells: Covered in "Managing Large Data". Hiding Worksheets: Click tab of sheet(s) to hide. Home > Cells > Format > Hide & Unhide > Hide Sheet , OR right-click tab > Hide . To unhide: Home > Cells > Format > Hide & Unhide > Unhide Sheet , OR right-click visible tab > Unhide . Select sheet from list. Hiding a Workbook: View > Window > Hide . Workbook disappears from screen. To unhide: View > Window > Unhide . Select workbook from list. Protecting Data Locking cells, protecting worksheets/workbooks, preventing opening. Locking and Unlocking Cells: Cells are locked by default. Unlock desired cells BEFORE protecting worksheet. Select cell(s) to unlock. Home > Cells > Format > Lock Cell (toggles off). To hide formulas: Click result cell. Home > Cells > Format > Format Cells > Protection tab . Check Hidden . Click OK . Protecting a Worksheet: Home > Cells > Format > Protect Sheet , OR Review > Changes > Protect Sheet . Protect Sheet dialog. (Optional) Type Password to Unprotect Sheet . In Allow All Users... , select allowed actions (e.g., Select Locked Cells, Format Cells, Insert Columns ). Click OK . (Confirm password if set). To unprotect: Home > Cells > Format > Unprotect Sheet , OR Review > Changes > Unprotect Sheet . Enter password if any. Protecting a Workbook (Structure/Windows): Review > Changes > Protect Workbook . Protect Structure and Windows dialog. Check Structure (prevents adding/deleting/hiding sheets) or Windows (prevents resizing workbook window). (Optional) Type Password . Click OK . (Confirm password if set). Preventing a Workbook from Being Opened (Password): File > Info . Click Protect Workbook > Encrypt With Password . Encrypt Document dialog. Type Password . Click OK . Confirm password. Save workbook. Marking a Workbook as Final Makes workbook read-only to discourage changes. Users can override. File > Info . Click Protect Workbook > Mark as Final . Confirm dialogs. "Marked as Final" indicator appears on status bar. To edit: Click Edit Anyway button on InfoBar. Collaborating with Others Considering Your Collaboration Options Use Track Changes to monitor edits and Comments for additional information. Turning Track Changes On and Off: Open workbook. Review > Changes > Track Changes > Highlight Changes . Check Track Changes While Editing . (Optional) Customize When, Who, Where . Click OK . Workbook becomes shared . To protect from turning off: Review > Changes > Protect Shared Workbook . To turn off: Review > Changes > Track Changes > Highlight Changes . Uncheck Track Changes While Editing . Choosing Which Changes to Track: In Highlight Changes dialog: When : Since last save, not yet reviewed, all, since date. Who : Everyone, Everyone But Me. Where : Specific cell range. Highlight Changes Onscreen : Shows blue triangle in corner of changed cells. Mouse over for details. Editing with Track Changes Edit normally. Blue triangles indicate changes. Mouse over for info (who, when, what changed). Can turn off onscreen highlighting: Review > Changes > Track Changes > Highlight Changes . Uncheck Highlight Changes On Screen . Listing Tracked Changes in a New Sheet Creates a "History" sheet detailing all changes. Save workbook. Review > Changes > Track Changes > Highlight Changes . Check List Changes on a New Sheet . Click OK . History sheet can be sorted/filtered. (Disappears after saving/reopening workbook). Accepting and Rejecting Collaborative Changes Review > Changes > Track Changes > Accept/Reject Changes . In Select Changes to Accept or Reject dialog: Customize When, Who, Where . Click OK . In Accept or Reject Changes dialog: Click Accept , Reject for individual changes, or Accept All , Reject All . Click Close . Using Comments in Collaboration Provide useful context for cell content. Adding Comments: Right-click cell > Insert Comment . Yellow box appears with user name. Type comment. Click outside box. Alternatively : Click cell, Review > Comments > New Comment . Editing and Deleting Comments: Edit : Click cell with comment, Review > Comments > Edit Comment . Or right-click cell > Edit Comment . Delete : Click cell with comment, Review > Comments > Delete . Or right-click cell > Delete Comment . Formatting Comments: Click cell with comment. Review > Comments > Show/Hide Comment . Select text within comment box. Right-click > Format Comment . Format Comment dialog: Change font, size, color, style. Click OK . Show All Comments / Previous / Next buttons on Review > Comments group. Validating Excel Worksheet Content Ensure data accuracy and consistency. Setting Up Rules, Messages, and Alerts Select range of cells for validation. Data > Data Tools > Data Validation . On Settings tab: Allow : Choose data type (e.g., Whole number, Decimal, List, Date, Time, Text Length, Custom ). Set criteria (e.g., between, equal to, greater than ) and values. On Input Message tab: Add Title and Input Message (appears when cell selected). On Error Alert tab: Add Title and Error Message . Choose Style ( Stop, Warning, Information ). Click OK . To turn off validation: Select cells, Data Validation > Clear All . For Input Message , uncheck Show Input Message... . Looking for Duplicate Entries Find and optionally remove duplicate values. Select range of cells. Data > Data Tools > Remove Duplicates . Remove Duplicates dialog: Check/uncheck columns to include in duplicate check. Click OK . Importing and Exporting Your Excel Content Using Word, PowerPoint, and Excel Together Share content across Office applications. Word tables to Excel : Select Word table, Ctrl+C . In Excel, click cell, Ctrl+V . Each Word table cell becomes an Excel cell. Excel cells to Word table : Select Excel cells, Ctrl+C . In Word, Ctrl+V . PowerPoint/Word graphics to Excel : Select graphic, Ctrl+C . In Excel, Ctrl+V . Graphics float, can be moved/resized. Excel Charts to PowerPoint/Word : Select chart, Ctrl+C . In other app, Ctrl+V . Chart remains linked to Excel data, updates automatically. Using Access Tables and Excel Worksheets Together Excel data to Access table: In Excel, select range (include headings for new table). Ctrl+C . In Access: Create > Tables > Table . Click Select All box in new table. Ctrl+V . Confirm paste. Access table to Excel worksheet: In Access, open table. Select entire table (or part). Ctrl+C . In Excel, click cell. Ctrl+V . Using Your Excel Data in Other Applications File > Save & Send . Send Using E-Mail : Send as attachment, link, PDF, XPS, Internet Fax. Save to Web : Requires Windows Live ID. Save workbook/worksheets to online storage. Save to SharePoint : Save to SharePoint environment. Change File Type : Save as other Excel formats (.xls, .xlsm, .xltx, .xltm) or non-Excel formats (e.g., CSV, TXT). Create PDF/XPS Document : Save as PDF (Adobe) or XPS (Microsoft). Using PivotTables Tool to dynamically summarize and analyze data from a list, viewing it from different perspectives. Creating a PivotTable Click any cell in your data list. Insert > Tables > PivotTable . Create PivotTable dialog. Choose your data : Default is selected list in active sheet. Can choose external source. Choose where to place the PivotTable report : New Worksheet (default) or Existing Worksheet (specify location). Click OK . Setting Up Your PivotTable Layout PivotTable Field List pane appears (right side). Drag fields from PivotTable Field List to four section boxes: Report Filter : Fields to filter the entire report (e.g., Department). Column Labels : Fields to display horizontally (e.g., Job Title). Row Labels : Fields to display vertically (e.g., Last Name). Values : Fields containing numbers to be summarized (e.g., Salary). Default is SUM, can change function. To remove a field: Drag it out of the section box or uncheck it in PivotTable Field List . Delay Layout Update checkbox: Prevents immediate update, requires clicking Update button. Using the PivotTable Tools PivotTable Tools tabs appear when PivotTable is selected: Options and Design . Options Tab: PivotTable : Options for generating/viewing. Active Field : Expand/Collapse data, Field Settings (subtotals, layout, print). Group : Group/Ungroup rows/columns (e.g., managerial positions). Group Selection . Sort & Filter : A-Z/Z-A sort, Sort dialog for more options. Insert Slicer (for Excel 97-2003 Compatibility Mode). Data : Refresh All (updates from source data), Change Data Source . Actions : Clear, Select, Move PivotTable . Calculations : Summarize Values By (change function), Show Values As (percentage), Fields, Items, & Sets (calculated fields). Tools : PivotChart , OLAP tools. Show : Toggle Fields List, +/- Buttons, Field Headers . Design Tab: Layout : Subtotals, Grand Totals, Report Layout (Compact, Outline, Tabular). PivotTable Style Options : Row Headers, Column Headers, Banded Rows/Columns. PivotTable Styles : Apply predefined visual styles (colors, banding). Sorting and Filtering PivotTable Data Filtering PivotTable Fields Reduce displayed data using filter arrows on field names. Click drop-down arrow next to field name (e.g., Department). (Optional) Check Select Multiple Items . Check desired entries. Click OK . To clear: Select All or Select All from drop-down menu. Changing Sort Order For row/column fields, filter fields, value fields. Click drop-down arrow next to field name. Choose A to Z or Z to A for simple sort. For more options: Choose More Sort Options . Manual : Drag records to custom order. Ascending/Descending : Choose field to sort by. More Options : AutoSort, sort by calculated field values. Quick sort : Click cell in column, Data > Sort & Filter > A-Z / Z-A . Updating a PivotTable Report To reflect changes in source data: Select any cell in PivotTable. PivotTable Tools Options > Data > Refresh All . Formatting Your PivotTable Home tab : For basic font, size, color, fill formatting of cells. PivotTable Tools Design tab : For layout (subtotals, grand totals, report layout) and predefined PivotTable Styles. Creating a PivotChart Graphical representation of PivotTable data. Linked to PivotTable, updates with filters/sorts. Select any cell in PivotTable. PivotTable Tools Options > Tools > PivotChart . Insert Chart dialog. Choose chart type and subtype. Click OK . PivotChart buttons : Allow filtering/sorting data directly on chart (e.g., Report Filter, Legend, Axis fields). PivotChart Fields list : Shows Legend and Axis fields instead of Column/Row labels. To move PivotChart: Copy/paste to new sheet. To change chart type: PivotChart Tools Design > Type > Change Chart Type . Excel 2010 Keyboard shortcuts Ctrl combination shortcut keys Key Description Ctrl+Shift+( Unhides any hidden rows within the selection. Ctrl+Shift+& Applies the outline border to the selected cells. Ctrl+Shift+_ Removes the outline border from the selected cells. Ctrl+Shift+~ Applies the General number format. Ctrl+Shift+$ Applies the Currency format with two decimal places. Ctrl+Shift+% Applies the Percentage format with no decimal places. Ctrl+Shift+^ Applies the Scientific number format with two decimal places. Ctrl+Shift+# Applies the Date format with the day, month, and year. Ctrl+Shift+@ Applies the Time format with the hour and minute, and A.M. or P.M. Ctrl+Shift+! Applies the Number format with two decimal places, thousands separator, and minus sign (-) for negative values. Ctrl+Shift+* Selects the current region around the active cell. In a PivotTable, it selects the entire PivotTable report. Ctrl+Shift+: Enters the current time. Ctrl+Shift+" Copies the value from the cell above the active cell into the cell or the formula bar. Ctrl+Shift+Plus (+) Opens the Insert dialog box to insert blank cells. Ctrl+Minus (-) Opens the Delete dialog box to delete the selected cells. Ctrl+; Enters the current date. Ctrl+` Toggles between displaying cell values and displaying formulas in the worksheet. Ctrl+' Copies a formula from the cell above the active cell into the cell or the formula bar. Ctrl+1 Opens the Format Cells dialog box. Ctrl+2 Toggles to apply or remove bold formatting. Ctrl+3 Toggles to apply or remove italic formatting. Ctrl+4 Toggles to apply or remove underlining. Ctrl+5 Toggles to apply or remove strikethrough. Ctrl+6 Toggles between hiding and displaying objects. Ctrl+8 Toggles to display or hide the outline symbols. Ctrl+9 Hides the selected rows. Ctrl+0 Hides the selected columns. Ctrl+A Selects the entire worksheet. If the worksheet contains data, pressing Ctrl+A selects the current region. Pressing Ctrl+A a second time selects the entire worksheet. When the cursor is to the right of a function name in a formula, pressing Ctrl+A opens the Function Arguments dialog box. Ctrl+Shift+A Inserts the argument names and parentheses when the cursor is to the right of a function name in a formula. Ctrl+B Toggles to apply or remove bold formatting. Ctrl+C Copies the selected cells. Ctrl+D Uses the Fill Down command to copy the contents and format of the topmost cell of a selected range into the cells below. Ctrl+F Opens the Find And Replace dialog box, with the Find page active. Shift+F5 also displays this page, whereas Shift+F4 repeats the last Find action. Ctrl+Shift+F Opens the Format Cells dialog box, with the Font page active. Ctrl+G Opens the Go To dialog box. F5 also opens this dialog box. Ctrl+H Opens the Find And Replace dialog box, with the Replace page active. Ctrl+I Toggles to apply or remove italic formatting. Ctrl+K Opens the Insert Hyperlink dialog box for new hyperlinks or the Edit Hyperlink dialog box for selected existing hyperlinks. Ctrl+L Opens the Create Table dialog box. Ctrl+N Creates a new, blank workbook. Ctrl+O Opens the Open dialog box to open or find a file. Ctrl+Shift+O Selects all cells that contain comments. Ctrl+P Displays the Print page in the Backstage view. Ctrl+Shift+P Opens the Format Cells dialog box, with the Font page active. Ctrl+R Uses the Fill Right command to copy the contents and format of the leftmost cell of a selected range into the cells to the right. Ctrl+S Saves the active file with its current file name, location, and file format. Ctrl+T Opens the Create Table dialog box. Ctrl+U Toggles to apply or remove underlining. Ctrl+Shift+U Toggles between expanding and collapsing the formula bar. Ctrl+V Inserts the contents of the Microsoft Office Clipboard at the cursor and replaces any selection. Ctrl+Alt+V Opens the Paste Special dialog box. Ctrl+W Closes the selected workbook window. Ctrl+X Cuts the selected cells. Ctrl+Y Repeats the last command or action, if possible. Ctrl+Z Performs the Undo command to reverse the last command or to delete the last entry that you entered. Function keys Key Description F1 Displays the Excel Help pane. Ctrl+F1 Displays or hides the ribbon. Alt+F1 Creates an embedded chart of the data in the current range. Alt+Shift+F1 Inserts a new worksheet. F2 Opens the active cell for editing and positions the cursor at the end of the cell contents. Ctrl+F2 Displays the print preview area on the Print page in the Backstage view. Shift+F2 Adds a cell comment or opens an existing comment for editing. F3 Opens the Paste Name dialog box (if names exist). Shift+F3 Opens the Insert Function dialog box. F4 Repeats the last command or action. Ctrl+F4 Closes the selected workbook window. Alt+F4 Exits Excel. F5 Opens the Go To dialog box. Ctrl+F5 Restores the window size of the selected workbook window. F6 Switches between the worksheet, ribbon, pane, and Zoom controls. Ctrl+F6 Switches to the next workbook window when more than one workbook window is open. Shift+F6 Switches between the worksheet, Zoom controls, pane, and ribbon. F7 Opens the Spelling dialog box. Ctrl+F7 Performs the Move command on the workbook window (when not maximized). F8 Turns extend mode on or off. Ctrl+F8 Performs the Size command (on the Control menu for the workbook window). Alt+F8 Opens the Macro dialog box. Shift+F8 Enables adding nonadjacent cell or range to a selection. F9 Calculates all worksheets in all open workbooks. Ctrl+F9 Minimizes a workbook window to an icon. Shift+F9 Calculates the active worksheet. Ctrl+Alt+F9 Calculates all worksheets in all open workbooks (regardless of change). Ctrl+Alt+Shift+F9 Rechecks dependent formulas, then calculates all cells in all open workbooks. F10 Turns key tips on or off (same as Alt). Ctrl+F10 Maximizes or restores the selected workbook window. Shift+F10 Displays the shortcut menu for a selected item. Alt+Shift+F10 Displays the menu or message for an Error Checking button. F11 Creates a chart of the data in the current range in a separate Chart sheet. Alt+F11 Opens the Microsoft Visual Basic Editor. Shift+F11 Inserts a new worksheet. F12 Opens the Save As dialog box. Other useful shortcut keys Key Description Arrow keys Moves one cell up, down, left, or right. Ctrl+Arrow key Moves to the edge of the current data region. Shift+Arrow key Extends the selection of cells by one cell. Ctrl+Shift+Arrow key Extends the selection to the last nonblank cell in the same column or row. Left Arrow or Right Arrow Selects tab when ribbon is selected. Switches between main/submenu. Down Arrow or Up Arrow Selects next/previous command in menu/submenu. Navigates tab group. Moves between dialog box options. Down Arrow or Alt+Down Arrow Displays a selected drop-down list. Backspace Deletes one character to the left in formula bar. Clears active cell content. Delete Removes cell contents (data and formulas) without affecting formats/comments. End Turns End mode on. Moves to next nonblank cell or last cell in row/column. Ctrl+End Moves to the last cell on a worksheet (lowest used row of rightmost used column). Ctrl+Shift+End Extends selection to the last used cell on the worksheet. Enter Completes cell entry and selects cell below (default). Performs action for default dialog button. Alt+Enter Starts a new line in the same cell. Ctrl+Enter Fills the selected cell range with the current entry. Shift+Enter Completes cell entry and selects the cell above it. Esc Cancels entry. Closes open menu/dialog. Closes full screen mode. Home Moves to beginning of a row. Moves to upper-left cell when Scroll Lock is on. Ctrl+Home Moves to the beginning of a worksheet (A1). Ctrl+Shift+Home Extends the selection of cells to the beginning of the worksheet. Page Down Moves one screen down in a worksheet. Alt+Page Down Moves one screen to the right in a worksheet. Ctrl+Page Down Moves to the next sheet in a workbook. Ctrl+Shift+Page Down Selects the current and next sheet in a workbook. Page Up Moves one screen up in a worksheet. Alt+Page Up Moves one screen to the left in a worksheet. Ctrl+Page Up Moves to the previous sheet in a workbook. Ctrl+Shift+Page Up Selects the current and previous sheet in a workbook. Spacebar Performs action for selected button or selects/clears checkbox in dialog. Ctrl+Spacebar Selects an entire column. Shift+Spacebar Selects an entire row. Ctrl+Shift+Spacebar Selects entire worksheet (or current region, then entire worksheet). Selects all objects. Alt+Spacebar Displays the Control menu for the Excel window. Tab Moves one cell to the right. Moves between unlocked cells in protected worksheet. Moves to next option in dialog. Shift+Tab Moves to the previous cell or option in dialog. Ctrl+Tab Switches to the next page in a dialog box. Ctrl+Shift+Tab Switches to the previous page in a dialog box.