Excel Dynamic Search Filter

Excel Dynamic Search Filter

The standard dynamic search usually means you list out the criteria, and have Excel look for IDs with those criteria. Criteria can be relevant attributes of a data point, such as name and age. What if you want to see the list of IDs with those attributes instead?...
Find Top 10 in List Beyond Certain Value

Find Top 10 in List Beyond Certain Value

The topic of interest is how to write formulas that will list the top 10 biggest values above x and the bottom 10 smallest values that are no greater than y, where x and y are defined by user. Data Data consists of a column of dollar amounts in column F and a column...
Sum Until Sign Changes

Sum Until Sign Changes

If you wish to sum rolling totals every time there is a sign change, you can use one of IF(), IFS() or SWITCH() function to get the job done. Excel Sum Until Sign Changes is a practical question that you will encounter when you use spreadsheets. Example of Rolling...
Alternative Rows to Column

Alternative Rows to Column

If you have data you copied from the internet, instead of a nicely presented two-column table, everything could be in one column of alternate rows. There are many ways to separate them into the supposed two column. Choose a method that serves you best....
Excel Column List Rearrangement

Excel Column List Rearrangement

This is a special case where column list needs to be rearranged in Excel into tabular form without the use of formulas. Above is the original data starting from column D onwards. The preferred result is to turn the above into a column of State and a column of Cities:...
Excel Convert Number to Words

Excel Convert Number to Words

This post provides a spreadsheet template that you can use to convert number to words in Excel. There is no VBA, and the formulas work in Excel 2007 onwards. The only caveat is that numbers such as 1,100 are described as One Thousand One Hundred instead of Eleven...
Transpose Excel

Transpose Excel

To transpose Excel from column to row based on another column, you can use either Excel 365 functions that consist of Unique(), Transpose() and Filter(), or use previous functions involving Offset() and Indirect() amongst other candidates. Column-To-RowDownload Simple...
Sort Pivot Table By Value

Sort Pivot Table By Value

To sort pivot table by value you first need to select a cell from the Values field instead of category items in Row Labels field. If not, you may end up sorting the Row Labels field from A to Z or Z to A, which may have been sorted that way to begin with....
Google Sheet Dynamic Range

Google Sheet Dynamic Range

In Google Sheet, if you wish to capture a data range as it shrinks or expands, you would apply the concept of dynamic range. Google Sheet Download Why Use a Dynamic Range Google Sheet functions such as SUMIF() and Subtotal() can be applied to a dynamic range to yield...
Sort Multiple Columns in Excel

Sort Multiple Columns in Excel

If your Excel worksheet has become quite large, using the Sort dialogue box to sort on multiple columns can make it easier to find the data you need. The Sort dialogue box lets you tell Excel what column to sort on next if two cells in the main...

Pin It on Pinterest