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
