Here’s a list of Excel tips, shortcuts, and common formulas:
Excel Tips
1. Double-click a cell’s corner to autofill.
2. Use Freeze Panes to keep headers visible.
3. Use Conditional Formatting for visual analysis.
4. Use Data Validation to create dropdown lists.
5. Split text into columns using Text to Columns.
6. Remove duplicates using the Remove Duplicates feature.
7. Use the VLOOKUP function for searching data.
8. Combine data with CONCATENATE or “&”.
9. Use PivotTables for data summarization.
10. Sort and filter data to manage large datasets.
11. Use Tables for easier data management.
12. Use Flash Fill to automate repetitive tasks.
13. Use Named Ranges for easier reference.
14. Hide rows/columns to focus on specific data.
15. Group data for better organization.
16. Use the Quick Analysis tool for instant data insights.
17. Protect sheets/workbooks to prevent unwanted changes.
18. Use Excel’s auditing tools to trace formulas.
19. Apply cell styles for consistent formatting.
20. Use the Format Painter to copy formatting.
Excel Shortcuts
1. Ctrl + N – Create a new workbook
2. Ctrl + O – Open an existing workbook
3. Ctrl + S – Save the workbook
4. Ctrl + W – Close the workbook
5. Ctrl + P – Print the workbook
6. Ctrl + F – Open the Find dialog box
7. Ctrl + H – Open the Replace dialog box
8. Ctrl + Z – Undo an action
9. Ctrl + Y – Redo an action
10. Ctrl + C – Copy
11. Ctrl + X – Cut
12. Ctrl + V – Paste
13. Ctrl + D – Fill down
14. Ctrl + R – Fill right
15. Ctrl + A – Select all
16. Ctrl + T – Create a table
17. Ctrl + ; – Insert current date
18. Ctrl + Shift + : – Insert current time
19. Ctrl + ` – Show/hide formulas
20. F2 – Edit the selected cell
21. Alt + Enter – Insert a line break within a cell
22. Ctrl + Arrow Keys – Move to the edge of data regions
23. Shift + Space – Select entire row
24. Ctrl + Space – Select entire column
25. Ctrl + Shift + L – Apply/remove filters
26. Ctrl + Shift + 5 – Apply percentage format
27. Ctrl + Shift + $ – Apply currency format
28. Ctrl + Shift + # – Apply date format
29. F4 – Repeat last action
30. Ctrl + 1 – Format cells dialog
31. Ctrl + K – Insert hyperlink
32. Alt + E + S + V – Paste Special values
33. Ctrl + Shift + + – Insert a row/column
34. Ctrl + – – Delete a row/column
35. Alt + = – AutoSum
36. Ctrl + Shift + L – Toggle filters
37. Alt + H + O + I – AutoFit column width
38. Alt + H + H – Fill color
39. Ctrl + Shift + ~ – Apply general number format
40. Ctrl + 9 – Hide selected rows
41. Ctrl + 0 – Hide selected columns
42. Ctrl + Shift + ( – Unhide rows
43. Ctrl + Shift + ) – Unhide columns
44. Ctrl + Page Up/Page Down – Switch between sheets
45. Ctrl + F6 – Switch between open workbooks
46. Ctrl + F12 – Open the Open dialog box
47. Ctrl + Shift + U – Expand/collapse formula bar
48. Ctrl + Alt + V – Open Paste Special dialog
49. Ctrl + Shift + K – Insert comment
50. Ctrl + Shift + U – Toggle formula bar expansion
Common Excel Formulas
1. =SUM(A1:A10) – Adds the numbers in cells A1 through A10.
2. =AVERAGE(A1:A10) – Calculates the average of the numbers in cells A1 through A10.
3. =COUNT(A1:A10) – Counts the number of cells that contain numbers in A1 through A10.
4. =COUNTA(A1:A10) – Counts the number of non-empty cells in A1 through A10.
5. =IF(A1>10, “Yes”, “No”) – Returns “Yes” if A1 is greater than 10, otherwise “No”.
6. =IFERROR(A1/B1, “Error”) – Returns “Error” if A1 divided by B1 results in an error.
7. =VLOOKUP(B2, A2:C10, 3, FALSE) – Looks for B2 in the first column of A2:C10 and returns the value in the third column of the matching row.
8. =HLOOKUP(B2, A1:C3, 2, FALSE) – Looks for B2 in the first row of A1:C3 and returns the value in the second row of the matching column.
9. =INDEX(A1:B10, 2, 2) – Returns the value in the second row and second column of the range A1:B10.
10. =MATCH(100, A1:A10, 0) – Returns the position of the value 100 within the range A1:A10.
11. =LEN(A1) – Returns the number of characters in cell A1.
12. =CONCATENATE(A1, ” “, B1) – Joins the contents of cells A1 and B1 with a space in between.
13. =LEFT(A1, 3) – Returns the first three characters in cell A1.
14. =RIGHT(A1, 3) – Returns the last three characters in cell A1.
15. =MID(A1, 2, 3) – Returns three characters from the middle of the text in cell A1, starting at the second character.
16. =TRIM(A1) – Removes extra spaces from the text in cell A1.
17. =ROUND(A1, 2) – Rounds the value in cell A1 to two decimal places.
18. =SUMIF(A1:A10, “>5”) – Adds up cells in A1:A10 that are greater than 5.
19. =COUNTIF(A1:A10, “Yes”) – Counts the number of cells in A1:A10 that contain “Yes”.
20. =SUMPRODUCT(A1:A10, B1:B10) – Multiplies corresponding elements in A1:A10 and B1:B10, then returns the sum of the products.
21. =TEXT(A1, “MM/DD/YYYY”) – Converts a date in A1 to the format MM/DD/YYYY.
22. =DATEDIF(A1, B1, “Y”) – Calculates the difference in years between dates in A1 and B1.
23. =NOW() – Returns the current date and time.
24. =TODAY() – Returns today’s date.
25. =EDATE(A1, 1) – Returns the date one month after the date in A1.
26. =EOMONTH(A1, 0) – Returns the last day of the month in A1.
27. =YEAR(A1) – Returns the year of the date in A1.
28. =MONTH(A1) – Returns the month of the date in A1.
29. =DAY(A1) – Returns the day of the date in A1.
30. =WEEKDAY(A1) – Returns the day of the week for the date in A1.
31. =AND(A1>0, B1<10) – Returns TRUE if both conditions are met.
32. =OR(A1>0, B1<10) – Returns TRUE if at least one condition is met.
33. =NOT(A1>0) – Returns the opposite of the condition.
34. =ABS(A1) – Returns the absolute value of A1.
35. =PI() – Returns the value of pi.
36. =SQRT(A1) – Returns the square root of A1.
37. =POWER(A1, 2) – Returns A1 raised to the power of 2.
38. =MOD(A1, 3) – Returns the remainder when A1 is divided by 3.
39. =RAND() – Returns a random number between 0 and 1.
40. =RANDBETWEEN(1, 10) – Returns a random number between 1 and 10.
41. =LARGE(A1:A10, 2) – Returns the second largest number in the range A1:A10.
42. =SMALL(A1:A10, 2) – Returns the second smallest number in the range A1:A10.
43. =SUBTOTAL(1, A1:A10) – Returns the average of the range A1:A10, ignoring hidden rows.
44. =SUBTOTAL(9, A1:A10) – Returns the sum of the range A1:A10, ignoring hidden rows.
45. =CELL(“address”, A1) – Returns the address of the cell A1.
46. =ISNUMBER(A1) – Returns TRUE if A1 contains a number.
47. =ISBLANK(A1) – Returns TRUE if A1 is blank.
48. =ISTEXT(A1) – Returns TRUE if A1 contains text.
49. =IFERROR(A1/B1, “Error”) – Returns “Error” if A1 divided by B1 results in an error.
50. =AVERAGEIF(A1:A10, “>5”) – Returns the average of cells in A1:A10 that are greater than 5.
This list covers a wide range of Excel tips, shortcuts, and common formulas that can help you work more efficiently and effectively in Excel. By mastering these, you can save time, improve your data analysis, and enhance your overall productivity with Excel.
Hope this is helpful,
Gage Gorman