ITEC - EXCEL

Copy using Drag and Drop

1.Select the cell range. 2.Hold the Ctrl key down while you position the mouse pointer on an edge of the selection (that is, the expanded cell cursor). The pointer changes from a thick, shaded cross to an arrowhead with a + (plus sign) to the right of it with the drag-and-drop ScreenTip beside it. The plus sign next to the pointer is your signal that drag and drop will copy the selection rather than move it. 3.Drag the cell-selection outline to the place where you want the copy to appear and release the mouse button. If, when using drag and drop to move or copy cells, you position the outline of the selection so that it overlaps any part of cells that already contain entries, Excel displays an alert box that asks whether you want to replace the contents of the destination cells. To avoid replacing existing entries and to abort the entire drag-and-drop mission, click the Cancel button in this alert box. To go ahead and exterminate the little darlings, click OK or press Enter.

Comparison Operators

= is equal to <> is different than < is less than <= is less than or equal to > is greater than >= is greater than or equal to Insert function, search for a function, select IF and click ok, click logical test box and select Cell that will be compared, type the comparison operator, click cell that you want to compare with, click value if true or value if false

Wrap Text

ALT + Enter or Home, Alignment

Sum Product

Adds and multiplies together Formula, Functions, Math and Trig

Hide or Unhide a worksheet

CTRL to select multiple, HOME, Format, hide/unhide,

Chart box - Style

Chart Styles

Chart - Data Table

Chart Tools, design tab, chart layouts group

Chart - Change the Chart type

Chart tools Design, Type group,

Chart - Shape Outline

Chart tools format tab, Shape styles group, click the desired color, clik the shape outline drop down arrow click weight, choose font size

Chart - Gradient Fill

Chart tools, Format tab, Shape Styles group, click on color, click on the shape fill drown down arrow again, choose gradient to open the gallery OR Use home tab

Chart - Chart element

Chart tools, Format tab, Shape styles group

Chart - Shape Style

Chart tools, Format tab, current selection group, ****if in the format group the chart elements opens, close it.

Chart - Word Art

Chart tools, Format, Word Art Style

Chart - Filter the source Data

Chart tools, click on chart filters button it looks like a wine glass, click to remove the check mark fo rth series click apply, close chart filters

Chart - insert shapes

Chart tools, format tab, insert shapes

Chart - Add and Remove Chart Title

Click on Chart Elements to the right of the chard, click to place a check mark nex to Chart Title, chose a position for the title, and press enter To edit, Triple click the Chart title placeholder

Table Styles New

Click on any cell, clik on design, table styles, New table style, type a style name in the Name box, select a table element and click format, check or uncheck default box

Chart - Trendline

Click on chart elements, hover over trendline check bock until the arrow button appear then click on arrow, select the type of trendline you want

Trace error formula

Click on the cell that contains the error message to select it. Click on the Yellow triangle with ! Select correct option

Chart -Data Labels

Click the chart elements button to the right of the chart click on data Labels

Chart - Switch Row and Column Data

Click the chart object or chart sheet, Design tab, Data group, Switch Row/Column

Data Connections

Data, Connections, click refresh to to update the imported data, click the link "click her to see where the selected connnections are used" click properties to open the connections Properties dialog box

Importing - Access Table

Data, get external data group,

Chart _ Sparkline edit

Design,

Table Styles

Design, table style

Remove Duplicates

Design, tools group, emove duplicates

Chart - Pie Chart - Rotate

Double click chart to open the Format data series task pane click on Series Options, Set the angel of the firl=st lsice by dragging the slider control to the degree setting.

Show Formulas

Formula tab, Formula auditing group, Cntrl + lets you toggle between formula and results view

SUMIF

Formula, Functions, Math and Trig

Insert Function

Formula, Functions, insert function or use short cut

PMT function - mortgage calculator

Formula, Functions, insert function, enter Financial, select PMT

Apply names

Formulas , Define names, apply names

New Name

Formulas, Define names, open *new Names dialog box*, type name, then go select the range and go to *use formula*, select paste, past cells if needed

Average

Formulas, Sum Function, Average

Count Function

Formulas, Sum function, Count

SUM Function

Formulas, ribbon bar

Format

Home, Cells group

Hide or unhide columns or rows

Home, Cells group, format,

Auto Fit Columns and Rows

Home, Cells group, format, autofit

Format Painter

Home, Clipboard

Paste

Home, Clipboard, Paste options

Format Cells dialog box

Home, Font or Cntrl +1

Number Format

Home, Number group - styles are under the cell styles

Date and Time

Home, Number, Number format =Today() = Now() Formula, Date and time to view

Table

Home, Styles, format as table

Clipboard

Home, clipboard

Merge and center

Home, merge and center button

Mixed cell reference

Includes on Relative and one Absolute

Open Document Properties panel

Info, properties, advanced properties

Header footer

Insert tab, Text

Chart - 3D Pie chart

Insert tab, charts group, choose 3 -D Pie

Chart - Task Pane

Open format task pane by clicking on the Format selection button under Chart tools, Format tab, selection group.

page breaks

Page layout button

Themes

Page layout, themes

Chart - display secondary axis

Select Chart object, click on the change chart type button (Chart tools design tab, type group), Choose th echart type, click to place or remove a chack mark for a secondary axis, click OK

Auto calculate

Select cell range, Right click and drop down menu will appear as status bar

Importing

Select the cell where you want the imported data to start, click the *From Text* (Data, Get External Data), select file and click Import text file, in *text import wizred* click the *Delimited* radio button

Chart - Sparkline Delete

Sparkline tools design tab, group choose clear selected sparklines

Absolute Cell reference

To change it to Absolute cell reference you press F4 or use $ symbol before each cell name They do not change when copied

Chart - Data Source

To reduce the data range use sizing pointer in lower right corner of the data range, To add a a data series you use the Select Data source dialog box Chart tools, design, data OR open the select data source dialog box an click select date in the Chart filters pane

View Multiple worksheets at the same time

View tab, Arrange all

See windows side by side

View tab, Windows group,

Switch Window using the ribbon

View tab, Windows group,

switch workbook views using ribbon

View tab, page layout,

manual page break

View tab, workbook views, page break

Preview page breaks

View, page break

Look up Function

Vlookup and Hlookup Values must be sorted in Ascending order and each cell must have a value Here's an example of a list, or database. In this case it's a list of products that our fictitious company sells: Vlookup(student information, Cells to search, what column number to find it. Formula, Functions, insert function, type in Vlookup

Trace Dependents

Which formula references the cell

3D Cell reference

can be used in several workbooks ='[WorkbookA.xlxs.Sheet2'!B2]

Copy a *mixed cell*

click formula bar and type $

Insert or delete a worksheet

click new sheet button (a little + sign), click on bottom half of INSERT button,

Chart box - Move

click on the *Move Chart* button on the chart tools design tab, Location group Another way is to right click the chart object and choose move chart

Correct a formula

click on the cell with the incorrect formula, move to the trace error button (green with yellow inside) drop down arrow

Insert header and footer using page dialog box

click on worksheet tab or shift if clicking on two adjacent tabs, two non adjacent worksheets use CNTRL, all of them *select all sheets*, click Page layout, page set up, select header

Chart box - Quick Layout

click the chart object or chart sheet, click the Design button, quick layout

Flash Fill

click the first cell in the new column for data, suggestion for fill automatically comes up.

Range Finder

color coded editing tool, to activate, double click on the cell and click formula bar

Convert table to a range

design taab, tools group

Trace Precedence

displays all cells referenced in a formula

Chart - Format task pane to set data label options

double click the data label element, click the Label Options button in the format task pan, click Label option or Number to expand the group.

copy a formula with relative cell reference

fill handle or copy and paste or right click to display menu

copy a formula with absolute cell reference

formula bar, cell reference, press F4, press enter, reselect the cell to copy the formula

Formula Auditing

group of editing option in the formula tab that you can use to review and correct errors in formulas *Trace precedents and trace dependents* - formula auditing - color coded to help you edit *Evaluate formula* - used for more complex formulas Formula tab, Formula auditing

Table name and reference

initially named TableN where N is a number, to convert to regular cellreferencing select File, Options, formula, soekring with formulas category and remove the check next to Use table names in formulas

circular reference

it includes the cell in the equation

Chart - Sparkline

mini chart Insert tab, sparklines

text criteria

must be enclosed in "

scale to fit

page layout, scaling option

Chart - Stack option

repeats picture over and over

move worksheets

right click on the worksheet tab, select move and copy,

Round Function

rounds the number to a specified decimal places Formula, Functions, type Round

Name a cell

select cell, click *name* beside formula bar, type the name, to add it to sheet type =and type the first letters of name

name multiple cells

select the range and click on *create from selection*(formula tab, defined name)

Chart - Combination

select the range of cells to be chart, click Insert tab, charts group, Insert combo chart,

Range Names

title the cell formula to make it easier to use

Chart - Print

to print a charge with its source data click any cell in worksheet then use Print command from the file tab in and make your usual print choices

splitter button

view tab, window group

freeze pain

view tab, window group, freeze pane

zoom

view tab, zoom