Excel Shortcuts, Formulas and Tips(Based on Mike Girvin's excelisfun youtube channel)

 Here are some tips and tricks for Excel users. Pending minor edits.

 

1              Use tab to select a formula when it is selected in blue.

2              Number formatting is a façade.

3              Count function counts only numbers. Count function does not count words.

4              CountA function counts everything both words and numbers. Only those cells that are not empty.

5              Use SUM IFS and COUNT IFS rather than SUMIF and Count IF even if you have only one condition.

6              Pivots need to be refreshed for data updates.

7              You can adjust QAT and ribbons with the tools that you use frequently.

8              To exactly know what is contained in the cell,you must format it to general.

9              You can use 0,-1,-2 for rounding in ROUND formula. If you want to round to 100s or 1000s.

10           Under dates, there are serial numbers, which are the number of days since Dec 31, 1899.

11           Use evaluate formula in formulas tab to look into the formula step by step.

12           Please Excuse Me Dear Aunt Salie.

13           Use IS Functions - IS blank, IS text, IS number

14           You don’t need to put logical results - true or false in inverted quotes.

15           In V-lookup the look up range has to be sorted from smallest to largest.

16           Default behaviour for vlookup is approximate match. You can use approximate match only when the reference column is sorted in ascending order.

17           While using vlookup false - 0, true - 1.

18           Define a data set as a table and the linked drop down and vlookup get updated automatically.

19           Excel Options - Proofing - select adjacent row and columns by default. Auto selection of tables.

20           Using Conditional Formatting. Cell styles

21           Using Name Manager. Defined Names.Create from Selection.

22           Please note that defined names do not update automatically like tables. You need to expand the selection manually for new entries.

23           Excel is not case sensitive.

24           To convert a number stored as text, back into number you add 0 or use -- at the beginning.

25           Use "~"& for matching values with special characters. This can be done with match, countif or sumif.

26           You can use index match towards the left unlike a vlookup.

27           Max function requires ctrl + shift + enter while handling array operations. Sumproduct, index, aggregate and lookup functions do not require ctrl+shift+enter.

28           Beware of IMPLICIT INTERSECTION while using array formulas. If you forgot to use ctrl + shift and enter you get a #Value! Error.

29           Database functions - DGET, DSUM, DSTDEV

30           Array functions give you an array of values as opposed to a single value.

31           Connecting different pivot tables to one slicer. Right click on slicer and select connections.

32           Tables and relationships are saved in the data models. Instead of using index match or vlookup you can create relationships between tables in a data model and use them to create pivot tables.

33           The file size using tables and relationships is considerably lower than file size using pivot tables.

34           You need to be careful while sorting. It would not include items after spaces. If you want to include spaces you need to select the whole thing.

35           Use space for selecting or deselecting an item. Alt + down arrow--> tab ----> space

36           No chart junk in creating charts and graphs. This is the guiding principle.

37           Does every piece of information on a chart add value ?

38           Most people prefer bar charts over pie charts. Also research has shown that humans decipher bar charts better than pie charts.

39           In the Query Editor, you separate data by using the delimiters. It is important to define the data types appropriately.

40           Appending queries in query editor.

41           Sumproduct can handle external references when the workbooks are closed whereas countifs cannot.

42           You need to use the formula or a combination that has the minimum computation time. Large formulas can actually slow up the excel sheets.

43           Index, Aggregate, Lookup, Sumproduct - you can use these functions without arrays. Without Ctrl + Shift+ Enter.

44           SUMIF, SUMIFS, COUNTIF, COUNTIFS,AVERAGEIF and AVERAGEIFS can not handle array operations in range arguments. Even if you use CSE.

45           Use tables for dynamic ranges and for data updates.

46           double negative --faster than other operations in sumproduct when converting from true/false to boolean.

47           You can use XOR or COUNTIFS to avoid double counting error while using OR.

48           Be very careful while dragging array formulas down. Once you drag you can't change the formul in any one cell.

49           To convert a formula into a range you add the first cell at the beginning and together with the formula it becomes a range.

50           In F9 edit mode you cannot Ctrl + Z for more than two times.

51           Sum and Sumproduct ignore Characters or words.

52           Indirect formula for referencing other tabs. Indirect converts text to references.

53           In excel any single cell can fit 32767 characters.

54           Mod function gices you the remainder - modulus.

55           Mid function to pick up the text from a string from the specified point and number of characters.

56           LEN function to give the length of string. SUBSTITUTE function to substitute text in a string.

57           Choose function to choose a value or array of values from a given set of values.

58           [area_num] argument tells the function which table to look at and must be a number 1, 2, 3, etc.

59           You can use matrix multiplication instead of sumproduct to make more sense.

60           Text files with .txt extensions use delimiter tab. Text files with .csv use comma as a delimiter.

61           You can set the pivot table formatting as default in the new pivot formatting style.

62           In the pivot table count is counta and count numbers is count.

63           Check for blanks before using pivots. A revenues columns shows count of revenues if you have blanks are other data types in the column,that is your clue.

64           Double click on any calculation in pivot, you get all the records that went into that calculation.

65           Instead of frequency function, you may use pivot tables group feature.

66           Format Chart Area - do not move with the cells option - the chart would not disappear.

67           Locking columns in tables by using square brackets and column names. Table references change when then they move columnwise but not when they move row wise.

68           Remember Countifs ">="&Date, "<="& Date

69           Scope within the workbook or the sheet when you create names for selection.

70           LEFT extracts a given number of characters from the left

71           RIGHT extracts a given number of characters from the right

72           SEARCH tells you the starting position in a text string of text you specify. FIND is similar to SEARCH, but it is case sensitive.

73           REPLACE function replaces part of a text string with text you specify, given a starting number and the number of characters

74           LEN function counts characters

75           MID extracts from the midele giben a starting point and the number of charactesr that you want

76           **Any Math Operation on Text Numbers will convert them back to numbers. It will also remove extra spaces.

77           TRIM removes spaces from a text string except for single spaces between words

78           PROPER function changes all capital letters or all lower case letters to all lower case except for first letter in each word

79           The LEN function counts characters

80           LOWER converts all letters to lower case

81           UPPER converts all letters to upper case

82           SUBSTITUTE function finds some text and replaces it with some different text

83           Please note that for approximate match to work in the VLOOKUP formula the look up values should be sorted in ascending order. Whereas Match function has options 1,-1 and 0 for exact match. You can use values that can never be reached in the lower end to avoid #NA.

84           Hold a worksheet and hit control to create a duplicate copy.

85           A small scale on the axis will lead to more pronounced shapes in the graph. So we need to guard against small changes that look very big on a chart.

86           Flashfill works most of the time with two examples. Two examples and Ctrl + E.

87           While working on advance filters and date columns always use uniform date format settings.v

SNO	Function or Shortcut	Description
1	F12	Save As
2	Ctrl plus *	Select the whole table
3	F2	enter a cell and to put a formula in edit mode
4	Ctrl and left click on the sheet name	Fastest way to move a sheet
5	Alt + N + V	Pivot Table
6	Ctr + F1	To show and hide the ribbon
7	Ctrl+Shift+~	Apply general fomatting
8	EDATE	Adds or subtracts number of months to the given data
9	EOMONTH	Gives you end of the month date. 0,1,2 etc
10	Shift + F3	Opens up insert function
11	Ctrl + Backspace	Move back to the active cell for completing the formula
12	Ctrl+Tab	This shifts to next workbook window when multiple workbook windows are open
13	Alt+Tab	Shifts between multiple instances of any window
14	ALT+F1	Creates a chart out of the data in the current range
15	ALT+SHIFT+F1	Inserts a new worksheet into the workbook
16	F6	Switch between worksheet, ribbon, taskpane and zoom
17	Shift+F6	Reverse of F6 Shortcut
18	Ctrl + T	Enter any table data set and hit Ctrl + T to create a table
19	Ctrl + .	Alternate between the four corners of a selected cell space
20	Ctrl + B	Bold Formatting
21	Alt + Enter	Manual line break
22	Alt H W	Wrap text
23	Ctrl+Shift+F3	Create Name from Selection
24	Alt-> M -> C	Create Name from Selection
25	EFFECT ()	Gives effective interest rate for a given nominal rate and the number of periods. Useful for converting annual rate to monthly rate or daily rate.
26	NETWORKDAYS.INTL()	Calculates the number of functions excluding holidays.
27	AGGREGATE()	Returns an aggregate value ignoring the errors in an array. Other functions such as large or small return error when there are errors in the data set.
28	MATCH()	Returns the first matching value in an array of values. It is not case sensitive. If there are duplicates it returns the first matching value. It has three options less than, equal to and greater than.
29	Alt E A A	Clear All. Select the required range of cells and just use this shortcut to clear everything including formats, values etc.
30	Array Functions - TRANSPOSE()	Transpose() the number of rows and columns need to be interchanged.
31	Array Functions - MMULT()	Matrix Multiplication 4/3 * 3/2 = 4/2
32	Ctrl + E	Flash fill
33	SUMPRODUCT	Using Sumproduct - sumproduct is like magic.
34	LOOKUP()	Lookup formula has the ability to handle arrays. But the data has to be sorted always.
35	OFFSET()	Using OFFSET instead of lNDEX to handle arrays along with Ctrl+Shift+Enter
36	Alt + F1	Put a chart for the selection on the existing sheet.
37	Alt + F11	Put a default chart on a new sheet.
38	Alt+2+3+4	Ω Omega the last Greek Alphabet. No character can be bigger than Omega.
39	Ctrl + /	Select the array
40	Ctrl + F3	Define formula name
41	F3	To get the list of all the defined formula names in your worksheet
42	Alt D+L	Data Validation
43	Ctrl + Shift + 4	Format a cell as default currency
44	Alt-> A -> Q	Extract Unique Records
45	Alt->A->M	Remove Duplicates
46	Alt->A->B	Subtotal feature for a data set
47	Shift + Alt + RightArrow	Group selected rows
48	Shift + Alt + LeftArrow	Ungroup Selected Rows
49	Alt D, S	Sort a selected table or range
50	Alt D FF, Ctrl Shift L	Filter a selected table or range
51	Alt D F S	Clear all filters. Filter button stays on.
52	Alt H W	Wrap and unwrap text.
53	Alt J T A	Rename a table
54	Alt E A F or Alt H E F	Clear all formats
55	Alt A K	Edit Links
56	Ctrl + Shift + F3	Create names from selection. No spaces allowed. Spaces will be repladed by underscores.
57	EOMONTH()	Last date of current month using 0, 1 for next month, -1 for previous month etc.
58	Alt W N	New Window of the current workbook
59	=West	Any time you put a ' at the beginning of a cell it will be considered text.
60	MOD()	to give the remainder modulus function
61	len()	gives the length of the string
62	substitute	substitute function to substitute text in a string
63	ISTEXT function	Text yields TRUE
64	ISNUMBER function	A number yields TRUE
65	ISLOGICAL function	A Logical yields TRUE
66	ISBLANK function	An empty cell yields TRUE
67	ISERROR function	#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL! yields TRUE
68	ISERR function	Any error value except #N/A yields TRUE
69	ISNA function	An #N/A (value not available) yields TRUE
70	ISNONTEXT function	Anything NOT text (number, error, empty cell) yields TRUE
71	ISFORMULA	A formula yields TRUE
72	IFS()	When you have more than two conditions, Profit loss or breakeven. Trump, Biden or Tied.
73	TextJOIN()	Joins text with and without blank cells with a user defined delimiter.
74	EDATE()	Same day next month, previous month or the same date.
75	Ctrl + Shift + 4	Currency number formatting
76	CHOOSE()	Choose function helps you choose an index number from a set of tables, formulas, text etc. These are numbered in order staring from one.
77	LOOKUP()	Lookup function second formula always gives the last column or row. If rows are greater than columns then it is a vlookup and if columns are greater than rows then it is a hlookup. It uses only approximate match, the first column has to be sorted. Lookup formula ignores errors. Lookup can handle arrays unlike vlookup.
78	INDEX(D$14:D$48,ROWS($D14:$D$48))	Contractable range vs expandable range. So for a contractable range the count decreases.
79	Alt O D	Manage rules of conditional formatting
80	Ctrl + Alt + F5	Refresh data model source
81	Alt + W + G	Zoom to Selection
82	Ctrl + H	Replace
83	Ctrl + E	Flash fill
84	Alt A E	Text to columns
85	Alt A Q	Advanced Filter
86	Ctrl C,C	To open the clip board