Assume a simple Sales dataset from which a Pivot Table has been created. The Pivot Table has been sliced by two columns of the dataset. To represent data pictorially, a Pivot Chart has been created from this Pivot Table and the chart is placed on a separate worksheet (of the same workbook). Now let’s say, a […]
Read More →
Assume a table which lists attendees for a Company’s Annual day function. In this Table, data for every attendee is shown on a separate row so if an employee attends the function with his/her spouse and three children, then there will be 5 rows for that employee. The question is to determine the count of the following family configuration: […]
Read More →
A tournament has 18 participating teams with 25 players in each team. Each team has to play five rounds of the Tournament and not all players play all rounds. Scores earned by each player in each round are shown in individual cells. If a player does not play a round, that cell is left empty. The task […]
Read More →
Assume date wise revenue data has been shown on two sheets – Sports wear and Fitness equipment (Data worksheets). On each of these two worksheets, there are two columns – Date and Revenue. On the Summary worksheet, the user would like to summarise data (Count instances and sum revenue figures) based on multiple conditions. The […]
Read More →
{ 0 Comments }
Assume there is data for complaints received and complaints resolved for two years – 2009 and 2010 for some regions regions. Each Region has its own worksheet. There are three headings on each sheet – Particulars, 2009 and 2010. While the Particulars column has the text “Complaints received” and “Complaints resolved”, the year columns have number of complaints received […]
Read More →
{ 2 Comments }
In a range of cells, assume there is a list of invoices raised during a certain period. Since adequate care was not taken to number the invoices sequentially, there are missing invoice numbers in the Excel range. So, it is possible that after raising invoice number 501, 502 and 503 the next invoice raised was 507. Given […]
Read More →
In a list with multiple occurrences, the VLOOKUP() function in Excel will only return the first occurrence of the string being searched. Furthermore, the VLOOKUP() function returns data from one column only. To work with a list where there are multiple occurrences of a particular string and data has to be extracted from multiple columns, […]
Read More →
{ 0 Comments }
Data in a two column database (A3:B13) can be Auto filtered on column A with one or many conditions. Data in column B will be numbers only (positive, negative or 0’s) – no text values. After filtering data in column A, one may want to extract the unique numbers from column B to cell A18 (and then […]
Read More →
{ 0 Comments }
Assume a “Labour utilisation” report which shows day wise tasks performed by different employees (row labels are task names, column labels are days and data in the matrix shows employee names). Each employee can perform multiple tasks on the same day and same task on multiple days. Given a list of all employees, one may want to answer […]
Read More →
When downloading data from an ERP into Excel, a Remarks column has entries which can be as lengthy as 300 characters per cell. Before uploading this data into another database, entries in the Remarks column need to be split into multiple cells of upto 50 characters per cell. Furthermore, when splitting data into multiple cells, it should […]
Read More →
Show Slicer selection on a Graph
{ 7 Comments }