Excel
Formulas begin with
=
To average
=average(
To display a function as text
=formulatext(A2)
To add
=sum(
The File button takes us to
A backstage view
What is a pivot table's purpose?
A fast, powerful way to create summary totals
What is a workbook?
A file in excel
What is a pivot table?
A tool that excel creates automatically to summarize data!!!!
Keystroke for autosum
Alt + =
To create a chart
Alt F1
To select visible cells only (you've hidden cells and don't want to copy them)
Alt+;
A quick way to create a chart on the same page
Alt+F1
To create a chart on current page
Alt+F1
To close excel
Alt+F4
Zoom in
Alt+ctrl++
Zoom out
Alt+ctrl+-
To create a new text line within a cell! (Create the text spacing you want!!!)
Alt+enter
N function
Among other things, this adds documentation so that you can explain a function you've just written =whatever function +N("a bonus based on a 5 star rating")
Line charts show...
Changes across time
To shift the ways data is displayed on the axes...
Click "switch row/column
To add fast
Click Autosum on homepage ribbon right or formulas ribbon left
To freeze multiple squares...
Click in one cell, then view > freeze panes> freeze panes This will freeze all cells above and to the left of active cell.
To ungroup sheets
Click on any sheet tab
Instead of typing =sum(...
Click on cell where you want sum to appear, then type Alt+= and then add cells to be summed
To create a chart with recommendations from excel, highlight square in data, then...
Click on insert > recommended charts. Then look at options
How to select just a couple of sheets?
Click on one sheet, then hold down ctrl and click on any other sheet tabs you'd like to select
Options to manipulate your content to print...
Click on the dialog box launcher under page setup
How do you select multiple sheets?
Click on the first sheet tab, then press and hold shift, then click on last sheet tab, they'll all be selected. He word "group" appears in green ribbon at top of screen
How do you share a file?
Click on the right most arrow box in the top green ribbon (customize quick access tool bar)>
Smith way to access page setup commands...
Click print, then page setup is available at bottom of left menu
To select entire worksheet?
Click the diagonal button in upper left corner
To unhide all hidden columns/rows at once?
Click the upper right hand corner, then right click anywhere in column/row numbers/letters and click unhide
If you delete a sheet and are in panic mode, do you have an option?
Close the excel file without saving and open it up again. If you haven't taken other steps, it should still be there.
What is the default chart type in excel?
Clustered column
What are the big four charts?
Column, line, bar, pie
To open up decimal places in a cell...
Ctrl + 1
To keep the active cell in place
Ctrl + Enter
How to get to the bottom of rows?
Ctrl + down arrow key
How to get to upper left corner?
Ctrl + home key
How to get to the last column?
Ctrl + right arrow
What is the redo command?
Ctrl + y
What is there undo command?
Ctrl + z
How do you see the perimeter of your active cells?
Ctrl and .
How do you see the full zone of active cells (highlight them in gray)?
Ctrl and A
How do you move quickly between open excel workbooks?
Ctrl tab
To select all cells
Ctrl+A
To copy all data
Ctrl+C
To display a function instead of the resulting value
Ctrl+~
How to show the actual categories chosen ina pivot table instead of "row labels" and "column labels"?
Design > Report Layout > show in outline/tabular form
What are the buttons in the lower right corner of command boxes in the ribbon called?
Dialogue box launchers
How do you create a best fit column size?
Double click boundary
How to actually display the formula in the adjoining cell?
Double click on cell; highlight all content; ctrl+C; esc; click on adjoining cell; double click to display space bar; ctrl+v (each of these steps is mandatory!!!)
To eliminate split screen...
Double click on split line OR click on split icon under view
How to edit text in a cell?
Double click where you want to edit and your cursor will appear
If you highlight and copy the data on a filtered sheet...
Excel only copies the visible data!
How to access the help menu?
F1 key
To create a chart on a different page
F11
How do you hide the ribbon if you're presenting data?
Far right of quick access tool bar has a filew/arrow icon that includes those choices
Where is the auto fill function?
Fill handle in lower right hand corner of cell
What is filtering?
Filtering puts the drop down arrow in the column header and lets you look at only cells that you define by pressing filter and selecting your criteria!
How do you protect data in excel?
Highlight a column, right click, choose format cells, click on protection, choose locked or hidden; THEN go to review tab>protect sheet; you'll be asked to create a password.
To make several columns a best fit?
Highlight all total columns, then double click on one column boundary
To split the screen,
Highlight cell, click View> split screen> split
To total two directions at once
Highlight cells with empty cells in two directions, then Alt+=
To insert a row without disrupting data to the side?
Highlight cells you want to move down, right click within highlighted cells, click on shift cells...
To create chart with only some of the data...
Highlight cells, press ctrl and highlight additional cells, then Alt F1 or insert...
To total
Highlight cells, then Alt+=
To copy data...
Highlight cells, then, with cursor on any edge and with control key pushed,drag to new location. Release mouse first.
To create a pick list in excel...
Highlight column>data>data validation>allow>list, then with blinking cursor in box, highlight cells containing list options, and enter
To insert multiple columns?
Highlight multiple columns, right click insert, multiple columns appear to the left
To insert multiple rows?
Highlight multiple rows, right click, click insert and new rows appear above
To print only the data you want...
Highlight selection, then under page layout, select print area, click on set print area
How to access shortcut menu?
Highlight space to manipulate, then right click and you may see the command you need...
How to autosum horizontally and vertically at once?
Highlight square with one row and column extension, then Alt + =
How do you make columns the exact width?
Highlight the relevant columns, then drag one on the boundary
to insert rows or columns
Highlight, then Ctrl Shft +
To move data...
Highlight, then, with cursor on any edge, drag to new location
How do you keep the active cell in place?
Hit ctrl+enter instead of enter (useful when you're going to extend a formula down a column!)
To zoom in excel...
Hold down ctrl and roll the mouse wheel
Where is strikethrough?
In the dialogue launch button of the font command box in the ribbon
How to create your own?
Insert > pivot table, then check for your row labels and drag your column labels into the column box and your values labels into the values box!!!
How to have excel create one?
Insert > recommended pivot table
Can autosum do more than sum?
It can average
What happens if you auto fill with a "Q" in the text of a cell?
It will auto fill 4 cells for "quarter"
How do you preserve integrity of headers when sorting?
Make sure when you click on sort that the box "My data has headers" is checked
How do you pivot a pivot table?
Move the labels into different boxes (columns, rows, values,etc.)
If cells for a chart are surrounded by empty cells...
No need to highlight the whole block, just click on a cell in the group
What are the three buttons in the right lower white band?
Normal, Page Layout, Page Break Preview
Where do you find the pivot table feature?
On the insert tab...like with charts...
Middle square button in lower right white line
Page layout
To add a sheet
Plus the + in lower ribbon or Shift F11
To copy a sheet
Press and drag while holding the ctrl button
What is the small green line command set at the top of the screen?
Quick access tool bar
When creating a chart...
Remember that the upper left corner square is perceived as empty. Labels to top and left of data leave that square unoccupied
How to customize the quick access tool bar?
Right Click on any tab not on your home screen and choose "add to quick access tool bar" option. Remove by right clicking on added icons and selecting remove
Insert a new column
Right Click on column letter, click on insert, new column appears to the left
In excel numbers are automatically...
Right aligned
Where is the "mini tool bar"
Right click on particular cells
To insert new row?
Right click on the row number, click insert, row appears above
How do you change a bunch of cells at once?
Select a bunch of cells, change one, then hit Ctrl and Enter
To align chart edges with cell boundaries
Select chart, hold down Alt key, then release mouse first
To activate the shortcut menu...
Shift+F10
How do you add a header in page layout form?
Slide mouse above columns to expose header boxes; fill under "Design" in ribbon
To complete a function from the drop down list
Start typing, when you see the option, then double click, highlight cells with your mouse, and press return
What does a right click do?
Take you to short cuts
How do you cancel filter with one stroke?
The clear button to the right of filter in the top ribbon
What is the thick white command set below the quick access tool bar?
The ribbon
To get rows to print at the top of each page...
Under page layout, click print titles, then under rows to repeat at top, highlight row with cursor and click on "okay"
How to keep rows or columns in view as you scroll...
Under view, click freeze panes, and follow directions
IF function
Used when you want to assign a value to a cell based on a logical test (If A2>5,2000,0). Meaning if cell A2 says you get a 5 star rating, then we'll give you a 2,000 bonus but if not, then no bonus
To freeze first column
View > freeze panes > freeze first column
To unfreeze top row...
View > freeze panes > unfreeze top row
To freeze top row...
View> freeze panes > freeze top row
How to change chart type?
When chart is selected, click on "change chart type"
Any change made to one sheet when a group of sheets is selected...
Will be made to all sheets
Do roe sizings work the same way column sizing works?
Yes
If you format the whole column instead of just the data, then when you highlight it...
You see really useful info (average, count, sum,etc) down in the bottom ribbon!
To move data into cells with existing data that will get pushed down,
highlight, then drag on an edge while holding shift key. This is a shift drag.
To make a piece of a formula absolute (so that every cell refers to the value in cell D5 no matter what column it's in...
you push F4 (you'll know it worked because $ will appear in front of the cell letter and number in the formula)
