Microsoft Excel: Tips and Tricks

Connect with me: Youtube | LinkedIn | WhatsApp Channel | Web | Facebook | Twitter

• Download PDF
• To access the updated handouts, please click on the following link: https://yasirbhutta.github.io/ms-excel/docs/tips-and-tricks.html
• Youtube Playlist to learn excel

Modules - Tips and Tricks

Module 1

• Transpose (rotate) data from rows to columns or vice versa
• Populate a Dropdown List Based on Cells Value
• Fill a column with a series in excel
• Flash Fill in Excel

  Module 2

• Quick Analysis in excel
• Define and use names in formulas
• Copy values quickly from the status bar

Transpose (rotate) data from rows to columns or vice versa

Transposing data is the process of switching data from rows to columns or vice versa. This is often done to make the data easier to read or to improve the layout of a spreadsheet.

How to Transpose Data in Excel

There are two main ways to transpose data in Excel:

Using the Paste Special Feature:

• Video: Transpose (rotate) data from rows to columns or vice versa - Microsoft 365
  • Download a sample data workbook #1

Tips: The shortcut key for Paste Special is CTRL+ALT+V.

Using the TRANSPOSE Function:

• Video: TRANSPOSE function - Vertical range of cells as a horizontal range
  • Download a sample data workbook #2

See also:

• Transpose (rotate) data from rows to columns or vice versa - Microsoft Support

Populate a Dropdown List Based on Cells Value

• How to: Populate a Dropdown List Based on Cells Value

Fill a column with a series in excel

• Video: Excel Mobile - Fill a column with a series in excel

Flash Fill in Excel

Flash Fill is a feature in Excel that automatically fills in data based on patterns it recognizes in your existing data. It is a powerful tool that can save you a lot of time and effort, especially when working with large datasets.

Tips: ‘CTRL+E’ is the shortcut for Flash Fill.

Here are some examples of how to use Flash Fill:

Extracting data from text: For example, suppose you have a column of text that contains email addresses. You can use Flash Fill to extract the email addresses from the text and put them in a separate column.

Combining text from multiple columns: For example, suppose you have a list of first names in one column and a list of last names in another column. You can use Flash Fill to combine the first and last names into a single column.

• Video: Flash Fill in Excel
  • Download a sample data workbook

Quick Analysis in excel

Quick Analysis in Excel is a powerful tool that helps you explore and visualize your data quickly and easily.

• Video: Quick Analysis in Excel
  • Download a sample data workbook

Define and use names in formulas

By using names, you can make your formulas much easier to understand and maintain. You can define a name for a cell range, function, constant, or table.[^1]

Formulas with cell references can be difficult to understand, especially for complex calculations. Using names for cell ranges or tables makes them much easier to understand, especially for others using your workbook.

• Video: Define and use names in formulas - Learn the Basics Quickly
  • Download a sample data workbook

See also:

• Define and use names in formulas - Microsoft Support
• Names in formulas - Microsoft Support

Copy values quickly from the status bar

• Video: Copy values quickly from the status bar

See also:

• Copy values quickly from the status bar in Excel for Windows - Microsoft 365 Insider

Additional Topics

True/False (Mark T for True and F for False)

• You can copy values like sum, average, and count directly from the status bar in Excel for Windows by simply clicking on them. [True or False]

Multiple Choice (Select the best answer)

Practical Exercises

Review Questions

References and Bibliography

• [^1:] Define and use names in formulas - Microsoft Support