Category: Excel

Using advanced filter you can filter a range of data based on complex criteria. It provides more powerful filtering options than the regular filter (Autofilter) feature. Here’s how to use Advanced Filter:

When you want to view only those records that have specific data in certain cells, you should filter the data range.

The RAND function is a volatile function, you can use this volatility to your benefit and record a macro that sorts data immediately after you recalculate and force the RAND function to return another set of random numbers. You then can attach this macro to a button so that each time you want to shuffle (random sort) data, all you need to do is click the button.

“Custom lists” is a useful feature that allows you to sort and fill data according to your own criteria. You can create lists that are relevant to your industry, organization, or personal preferences. For example, you can create custom lists of product names, department names, or specific project phases. Custom lists are not limited to a single workbook or worksheet. Once created, you can use them across different workbooks or worksheets, allowing for consistent data entry and autofill functionality in multiple files.

How to sort days of the week and months of the year using custom lists.

You normally sort the records or rows of a table by the values in one or more columns of the list or table. However, Excel also lets you sort by the font color, fill color, or cell icons that you apply to them with conditional formatting criteria. This is how you can do it.

You can quickly rearrange or sort the records in a data range based on the data in a single column by first activating a cell in the column and then clicking the A-Z or Z-A option in the Sort & Filter group on the Data tab.

Comments in Excel are small notes or annotations that you can add to a cell to provide additional information or context. Comments are useful for documenting your work, explaining formulas or functions, or giving feedback to others.

Sometimes you need to extract the numeric portion from a cell that contains both text and numbers. For example, if you have a cell that contains “Product ID: 12345”, you may want to get only the number “12345”. There are different ways to do this, we will demonstrate some methods and examples for doing this task.

The contents of a cell might look like numbers, especially in imported data, but it still might be impossible to use these numbers in calculations. Here are a few ways in which you easily can convert these “text” numbers to true numbers.