Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

excel > Shortcuts > Flashcards

Shortcuts Flashcards

1
Q

All shortcut commands

A

ALT

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

Move to cell on the right after writing text

A

Tab

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

Mgtgrouove to cell below after writing text

A

Enter

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

Edit the contents of the selected cell

A

F2

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

Move cursor one word at a time

A

CTRL + ARROW

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

Select one character at a time

A

SHIFT + ARROW

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

Select one word at a time

A

CTRL + SHIFT + ARROW

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

Move to worksheet on the right

A

CTRL + Pg Dn

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Move to worksheet on the left

A

CTRL + Pg Up

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

Create new worksheet

A

SHIFT + F11

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

Delete worksheet

A

ALT + E, L

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Change name of worksheet

A

ALT + O, H, R

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

Move to next cell

A

→

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

Move to edge of data region

A

CTRL + →

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

Return to first cell in the selected row / move cursor to the first character in the cell

A

HOME

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

Return to cell A1

A

CTRL + HOME

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

Move down one screen

A

Pg Dn

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

Move up one screen

A

Pg Up

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

Move across one screen to the right

A

ALT + Pg Dn

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

Move across one screen to the left

A

ALT + Pg Up

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

Show Go-To Dialog box

A

F5

type in sheet name + cell number e.g. (sheet 3!D300)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

Select cells in the direction of arrow

A

SHIFT + →

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

Select all cells within data region

A

CTRL + SHIFT + →

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

Select all cells in data region

A

CTRL + A

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

Select entire row

A

SHIFT + Spacebar

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

Select entire column

A

CTRL + Spacebar

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

To select additional cells - edit multiple data regions

A

Shift + F8

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

Insert a row above selected row

A

ALT + I, R

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q

Insert a column to the right of the selected column

A

ALT+ I, C

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q

Repeats previous command

A

F4

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
31
Q

Delete selected row or column

A

CTRL + -

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
32
Q

Autofit column width on selected columns

A

ALT + O, C, A

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
33
Q

Manually choose column width

A

ALT + O, C, W

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
34
Q

Autofit row height on selected rows

A

ALT + O, R, A

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
35
Q

Manually choose row height

A

Right-Mouse button key+ R

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
36
Q

Split command (freeze panes)

A

Use Alt + Navigation to split the viewing pane into two sections each with a scroller so as to compare things easily (doesn’t work with frozen panes)

Select cell where want split

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
37
Q

Freeze / unfreeze panes to the left and above selected cell

A

ALT + W, F, F

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
38
Q

Open the zoom dialogue box

A

ALT + V, Z

If you want to view whole dataset - fit selection command

Select whole dataset - then alt vz + f = OK.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
39
Q

Open Format cells dialog box

A

CTRL + 1

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
40
Q

Pastes the format of our copied cell

A

ALT + E, S, T

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
41
Q

Access fill colour dropdown

A

ALT + H, H

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
42
Q

Show/Hide Gridlines

A

ALT + W,V, G

43
Q

Wrap text

A

ALT + H, W

44
Q

Merge & center

A

ALT + H, M, C

45
Q

Unmerge cells

A

ALT + H, M, U

46
Q

Group selected columns/rows

A

ALT + A, G, G

47
Q

Hide columns / rows

A

ALT + A, H

48
Q

Show columns / rows

A

ALT + A, J

49
Q

Ungroup selected rows / columns

A

ALT + A, U, U

50
Q

Paste formulas

A

ALT + E , S , F

51
Q

Paste values

A

ALT + E , S , V

52
Q

Show sort dialog box

A

ALT + A, S, S

53
Q

See a formula/jump back inside it

A

F2

54
Q

Counts the number of cells (with numbers) in the selected range - doesn’t count text

A

=Count(range)

55
Q

Count number of cells with text

A

=CountA(range)

56
Q

Conditional Arithmetic

A

=COUNTIF/SUMIF

You can count anything, including text.

57
Q

Count number of cells if comply with certain criteria

A

=CountIF(range,criteria [e.g. “>3000”])

=CountIFS(range1,criteria1,range2,criteria2)

58
Q

Sum number of cells that comply with certain criteria

A

=SumIF(range, criteria [e.g. “>3000”], range)

First range might be orders in march…. final range is the revenue

=SUMIFS(range1,criteria1,range2,criteria2)

  • easy to build - just need to understand range and criteria.
59
Q

Calculates average value within a range

A

=AVERAGE(range)

60
Q

Calculates average value for cells that fit a single/muliple criterion

A

=AVERAGEIF
=AVERAGEIFS

=AVERAGEA: Calculates the average for TRUE/FALSE values

61
Q

Calculates maximum or minimum value within a range

A

=MAX(range)

=MIN(range)

62
Q

Logical testing IF

Performs a logical test, returning a value if test is TRUE and another if test is FALSE

A

=IF(xxxx=>

63
Q

Logical test to check if more than one criteria is met

Returns TRUE if ALL logical tests return TRUE

A

=AND(xx11=>yy22) etc.

64
Q

Logical test of multiple criteria and return of a true value or false value

A

=IF(AND(xx11=<>yy11,xx22=>

65
Q

Returns TRUE if AT LEAST ONE logical test returns TRUE

A

OR

66
Q

Logical operators

A

not equal to: <>
greater than or equal to: >=
less than or equal to: <=

Put in “ “ and add &+cell

67
Q

Show formulas

Replaces cells with formulas

A

ALT + M, H

68
Q

Cycle through anchoring cells when inside cell formula

A

F4

69
Q

Naming cells

A

CTRL+F3;

Make sure to capitalise the name and use underscore = creates a constant that we can reference in cells.

Means you don’t have to anchor cells + great time saver.

(if you want to delete name - just F3 + Alt D)

70
Q

Array function

A

CTRL + SHIFT + ENTER

  • Allow you to perform calculations on arrays, not just individual cells
  • Can output to a single cell or to multiple cells (must be pre-selected)
  • Shown by curly braces around a cell formula
  • Common uses of array functions are MaxIF and MinIF formulas
71
Q

Convert row to column and vice versa

A

=TRANSPOSE(range) - need to include array function { } in order to execute.

If you want a whole range of column numbers to convert into rows, need to pre-select destination cells before pressing control shift enter (ARRAY COMMAND).

72
Q

Formula Auditing and Traces

A

Alt + M, P: Trace precedents
Alt + M, D: Trace dependents
Alt + M, A, A: Remove all traces

73
Q

Fill commands

A

CTRL + R - fills right

CTRL + D - fills down

74
Q

Information functions

A
  • Enable you to check the status of a cell
  • Accept a cell as an input and return TRUE/FALSE as an output
  • Often located inside IF functions as logical tests (e.g. “recheck order quantity” or “OK”
  • Typically used to check the quality and integrity of an imported dataset
  • Some useful functions include:
  • – ISBLANK( )
  • – ISERROR( )
  • – ISNUMBER( )
75
Q

Add filters to a dataset

A

CTRL + SHIFT + L

CTRL + A; CTRL + A + T

76
Q

Rounding Numbers

A

Rounding errors often occur in Excel and irritate a lot of managers

  • To solve this problem, you can use the ROUND( ) function
  • ROUND( ) enables you to specify the number of decimal places for a given value
  • ROUNDUP( ) and ROUNDDOWN( ) are also available as functions in Excel
77
Q

Remove filters from a dataset

A

ALT + A, C

78
Q

Generating random numbers

A

RAND (generates random number between 0-1) - can specify what you want to happen thereafter by having as follow on from IF formula

RANDBETWEEN (allows you to set an upper and lower bound. E.g. 1,3 would give you either 1-3; you then have to specify for the naming = e.g:

=IF(RANDBETWEEN(1,3)=1, “Internal”,IF(RANDBETWEEM(1,3)=2,”Survey”,”None”)

79
Q

Sentence Cases

A

=Lower()
=Upper()
=Proper() - capitalizes first letter

80
Q

Combining two cells of text

A

=First cell + & + second cell

E.g. =A2&B2

You can also combine formatting in formula:

E.g. A2&“_“&Lower(B2)

  • You can combine almost any string of works you want!
81
Q

Chopping characters from text

A

=LEFT()
=RIGHT()
=MID()

  • Select cells + enter number of characters you want to chop.
  • For mid; number of characters from start + number of characters to chop…
82
Q

Identify position of specific character

A

=FIND()

  • input character, plus relevant cell + also number of characters from where to start counting!
  • gives you number from start where this character is found!

Used with MID function, you can chop text within a string to extract what you want…

  • Will give you number if text exists.
  • Can convert with =ISNUMBER(FIND(“xx”,N2))
  • If you want other result than TRUE/FALSE; ADD =IF( - at beginning - and after: )),N2,””)

= Gives you the value in that cell, or if false, an empty string.

83
Q

Split the content in a cell

A

Split text to columns, wizard

= ALT A E

TRIM() - allows you to remove white space before a text

84
Q

Create date value using day/month/year

A

Datevalue()

85
Q

Manually widen a column

A

ALT + O, C, W

86
Q

Converting dates into days of week

A

=TEXT(xx, “dddd”)

mm: Displays the month as a number with a leading zero when appropriate
mmm: Displays the month as an abbreviation (Jan to Dec)
mmmm: Displays the month as a full name (January to December)
mmmmm: Displays the month as a single letter (J to D)
d: Displays the day as a number without a leading zero
dd: Displays the day as a number with a leading zero when appropriate
ddd: Displays the day as an abbreviation (Sun to Sat)
dddd: Displays the day as a full name (Sunday to Saturday)
yy: Displays the year as a two-digit number
yyyy: Displays the year as a four-digit number

87
Q

Combining two data points to allow for sorting by this way - e.g. date and time as one

A

= format all cells to general cells (CTRL + SHIFT + #)

As time and date are stored as fractions and numbers of same unit - we can just add these together.

Then you convert back into appropriate format (CTRL + 1)

88
Q

Select another cell when inside formula

A

F2 again…

89
Q

Adding to date/time

A

To date - just add #

To time - add “7:00”

If manipulating sheet this way, make this a cell on the front page - which you can then update accordingly.

90
Q

Covert to time value

A

CTRL + SHIFT + ‘

91
Q

Remove all digits after a decimal point

A

=INT() = if on a time/date, only leaves time. Covert digits to time value to get the correct display

92
Q

Naming arrays

A

E.g. Selection of cells

CTRL + F3

ALT + N

  • Name the array
  • Makes it easier to read formulas
93
Q

Alternative ways of performing LOOKUPS

A

INDEX = Select array + Row number + Column number

(not so useful by itself - combine with match):

MATCH = Value (name); Array with value (company names), 0 (exact match) => gives you number

  • use for both horizontal and vertical to give you the inputs necessary for index!

Combining into one formula: =INDEX(lookupvalue, MATCH(cell, cell array,0),MATCH(cell, cell array,0)

Advantages - no need to move columns
- Must faster lookup than v-lookup.

94
Q

Top 5/ Lowest 5

A

=LARGE(range,position) - position = which ranking
=SMALL(-“-)

You can embed an if function to this, as an array.

E.g. {=large(IF(Datarange=selectedcell,datarange,””),position

  • with position, you would select the cell with the relevant ranking - 1 through 5.
95
Q

Ways to look up multiple criteria

A

https://www.microsoft.com/en/microsoft-365/blog/

1 Create a MATCH that looks for the value of 1 from the product of our two criteria
2 Put this MATCH as the row_num input for an INDEX with one column
3 Copy/Paste for remaining cells

96
Q

Single lookups on multiple criteria

A

DSUM(database, field, criteria)

  • – database: Select all the columns in your dataset
  • – field: The column header you wish to perform sum on
  • – criteria: Array of cells encompassing the sales headers and criteria

Other database functions:

  • DAVERAGE
  • DCOUNT
  • DGET
  • DMAX
  • DMIN
  • DPRODUCT
  • DSTDEV (Standard deviation for population sample)
  • DSTDEVP (Standard deviation for a population)
  • DVAR (Variance for a population sample)
  • DVARP (Variance for a population)
97
Q

Updating forumlas with new data

For expanding datasets, you can program so that it will automatically update to take into account additional rows/columns

A

Dynamic ranges for updating formulas

  • Most companies add new data to existing datasets over time
  • Automatically updating your formulas to include new data is a huge timesaver
  • We use OFFSET and COUNTA to do this
  • Put the OFFSET formula in your named range to keep formulas readable

Formulas

OFFSET(reference, rows, cols, [height], [width])

  • – reference: The value off which you base the offset - normally column header
  • – rows: The number of rows the array is away from reference
  • – columns: The number of columns the array is away from reference
  • – height: Height of the array. Use COUNTA to count non-blank cells in column
  • – width: Width of the array in columns

Best practice is to create names arrays with offsets before you build your formulas - in worksheets where you will be adding rows

98
Q

Creating pivot table

A

Format data to contain no blank rows, then format as a table… Select any cell in region, and choose inset pivot table. Choose where you want it placed…

You could also just select entire dataset and chose insert pivot, but will then need to be updated manually if you add data.

  • Simply drag fields into the Column Labels, Row Labels and Values areas
  • Values determine what data will be included in the body of Pivot Table

Column and Row Labels

  • Only fields with repeating values (e.g. State, City) should be added as Labels
  • Row Labels determine the metric for each row of data
  • Column Labels determine the metric for each column of data
99
Q

Formatting Pivot tables

A

Chose style under format table.

Can also choose banded rows, format numbers to include commas to make it much more ledgible.

100
Q

Summing/showing values in the pivot

A

Automatically assumes that you want sum of revenues, but you can change this by right clicking and choosing:

  • summarise values by …
  • show values as …

Click subtotals icon, show subtotals at bottom of group.

Show as % of parent helps you to get at a share of the subtotals…

101
Q

Group data in pivot

A

Right click and select group information so as to not have to add another column to the original dataset.

You can also select two - e.g. month and quarters (pay attention to date range it uses to calculate)

Alt + AH
Alt + AJ

102
Q

Add qualifiers to pivots

A

You can add IF statements to categorise by certain traits for example. Will autofill, but you then need to update the data in the pivot table.

Alt + F5

103
Q

Sorting Data in Pivot Tables

A
  • The selected cell is determines which field is sorted
  • The most common sort options are alphabetically or by sum of revenue
  • However, you can specify the column you want to sort by in More options
  • Sorts are removed when you expand / collapse a group

excel (1 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions