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:
Tips: The shortcut key for Paste Special is CTRL+ALT+V.
Using the TRANSPOSE Function:
See also:
Populate a Dropdown List Based on Cells Value
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.
Quick Analysis in excel
Quick Analysis in Excel is a powerful tool that helps you explore and visualize your data quickly and easily.
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.
See also:
Copy values quickly from the status bar
See also:
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]