Here are 20 useful Excel tricks

1. Pivot Tables: Summarize data to analyze and report.

2. VLOOKUP: Look up data in a table by row.

3. HLOOKUP: Look up data by column.

4. Conditional Formatting: Highlight cells based on criteria.

5. Data Validation: Control the type of data entered.

6. Keyboard Shortcuts: Use Ctrl + C for copy, Ctrl + V for paste, etc.

7. Flash Fill: Automatically fill in values.

8. Charts and Graphs: Visualize data with charts.

9. Filtering: Filter data to display only the rows that meet certain criteria.

10. Freeze Panes: Keep headers visible while scrolling.

11. IF Statements: Perform logical comparisons.

12. Concatenate: Combine text from different cells.

13. Remove Duplicates: Eliminate duplicate rows.

14. Text to Columns: Split text into different columns.

15. Index Match: A powerful alternative to VLOOKUP.

16. Macros: Automate repetitive tasks.

17. Quick Analysis Tool: Access common analytical tools quickly.

18. Transpose Data: Switch rows to columns or vice versa.

19. Goal Seek: Find input values to achieve a goal.

20. Solver Add-in: Solve complex optimization problems.

More in detail

1. Pivot Tables:

   • Select your data range.

   • Go to Insert > PivotTable.

   • Choose where you want the PivotTable report to be placed.

   • Drag and drop fields to the desired areas in the PivotTable Field List.

2. VLOOKUP:

   • Use the formula: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]).

   • Replace lookup_value with the value you want to search for.

   • table_array is the range where you want to search.

   • col_index_num is the column number in the table from which to retrieve the value.

   • range_lookup is optional; use FALSE for an exact match.

3. HLOOKUP:

   • Similar to VLOOKUP but searches by row: =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]).

4. Conditional Formatting:

   • Select your data cells.

   • Go to Home > Conditional Formatting.

   • Choose a rule or create a new rule.

   • Set the format options and click OK.

5. Data Validation:

   • Select your data cells.

   • Go to Data > Data Validation.

   • Set your criteria (e.g., list, date, number).

   • Enter the information for validation and click OK.

6. Keyboard Shortcuts:

   • Use Ctrl + C to copy, Ctrl + V to paste, Ctrl + Z to undo, etc.

7. Flash Fill:

   • Type a couple of examples of the format you want.

   • Select the cells where you want Flash Fill to apply.

   • Press Ctrl + E.

8. Charts and Graphs:

   • Select your data range.

   • Go to Insert > select the type of chart you want.

   • Customize your chart with Chart Tools.

9. Filtering:

   • Select your data range or table.

   • Go to Data > Filter.

   • Click the drop-down arrows in the header to filter by criteria.

10. Freeze Panes:

    • Click on the cell below and to the right of where you want to split.

    • Go to View > Freeze Panes.

    • Choose “Freeze Panes” from the dropdown.

11. IF Statements:

    • Use the formula: =IF(condition, value_if_true, value_if_false).

    • Replace condition with your logical test.

    • Specify what to return if true or false.

12. Concatenate:

    • Use the formula: =CONCATENATE(text1, [text2], ...).

    • Replace text1, text2, etc., with the cells or text you want to combine.

13. Remove Duplicates:

    • Select your data range.

    • Go to Data > Remove Duplicates.

    • Choose which columns to check for duplicates and click OK.

14. Text to Columns:

    • Select the column with text.

    • Go to Data > Text to Columns.

    • Choose ‘Delimited’ or ‘Fixed width’.

    • Set delimiters and click Finish.

15. Index Match:

    • Use the formula: =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)).

    • Replace return_range with the range that contains the data you want to return.

    • MATCH finds the position of your lookup value.

16. Macros:

    • Go to View > Macros > Record Macro.

    • Perform the actions you want to automate.

    • Stop recording and save your macro.

17. Quick Analysis Tool:

    • Select your data range.

    • Click on the Quick Analysis button that appears at the bottom right.

    • Choose a tool like Formatting, Charts, Totals, etc.

18. Transpose Data:

    • Copy the range you want to transpose.

    • Right-click on the destination cell.

    • Choose Paste Special and select Transpose.

19. Goal Seek:

    • Set up a formula in a cell.

    • Go to Data > What-If Analysis > Goal Seek.

    • Set your goal and specify which cell to change.

20. Solver Add-in:

    • Enable Solver by going to File > Options > Add-ins.

    • Go to Data > Solver.

    • Set your objective, add constraints, and click Solve.