Given multiple text values in a cell which are separated by a semi-colon, one may want to split data into multiple rows along with existing data in other columns. This is something similar to Data > Text to columns – the only difference being that data has to be split by rows instead of columns. […]
Read More →
Given a list of names in a worksheet range, one may want to pull out, the most frequently appearing names in descending order of frequency, to another range. Furthermore, one may want to provide an additional date criteria as well. You may refer to my solution in this workbook.
Read More →
Given the net annual salary figure and progressive tax rates for different income slabs, one may want to calculate the gross annual salary. You may refer to my solution in this workbook.
Read More →
With blanks appearing in a range of numbers, one may want to: 1. Average the first n numbers; and/or 2. Sum the last n numbers (in a horizontal and vertical range) Because of blanks, the range for averaging will need to automatically keep expanding till the nth number is reached. You may refer to my solution in this […]
Read More →
{ 0 Comments }
When creating a two y-axis chart (referred to as “Line-column” on 2 axis in Excel 2003), the usual way is to present column on primary vertical axis and line on secondary vertical axis. You may refer to my solution in this workbook.
Read More →
In a scenario where text values are interspersed with numeric data columns, the usual SUMPRODUCT function will not work. One will have to use a combination of array formulas and the SUMPRODUCT function. You may refer to my solution in this workbook.
Read More →
Having created primary and secondary validation drop downs, the issue is that when a primary validation cell changes, the value in the secondary validation cell does not become blank (or change automatically to first entry of the secondary validation list). Instead the secondary validation cell remains the same as what was already selected earlier. In […]
Read More →
Conditionally formatting duplicate values across all versions of MS Excel is quite simple. However, to colour different duplicate values in different colours, refer to this workbook.
Read More →
{ 0 Comments }
There may be many array formulas in a workbook. One may want to create a two column table in a sheet named “Array formula log” which lists down sheet names and cell address of all array formulas. Furthermore, the cell references listed on the “Array formula log” sheet should be hyperlinked. You may refer to […]
Read More →
Assume a column of data with blanks appearing in a few random cells. One may want to use this range of cells for creating a Validation drop down in a specified cell. After creating the validation drop down, the blanks from the source data range would obviously show up in the drop down as well. The task is […]
Read More →
Performing a text to rows operation
{ 20 Comments }