Visualise a Pivot Table with a few Fields dragged in the Report filter, Row labels and Value Area section. In the Column labels are two fields, Month and then Year – so in the column labels, for every month, there is data for three years 2005, 2006 and 2007. For some months, there is data […]
Read More →
{ 2 Comments }
Assume a dataset with a Key Performance Indicator (KPI) [appearing in one column] data for years ranging from 1985 to 2010 for 114 countries. This dataset has 170,000 rows of data and one row below the last row for every country, there is a total of the KPI column. So, if there are 25 rows […]
Read More →
{ 2 Comments }
Assume there is a list of names appearing in range A2:A9. Before these names, there are abbreviations such as Dr., Mr. Mrs. etc. To worsen the situation, there may/may not be a space after the abbreviation. A sample dataset may look like this: Mr.Ramakrishna Mr Ramakrishna Mr. Ramakrishna MrRamakrishna PhD.Saurav Dr (Mrs.) Indu Sharma Dr. (Mr) […]
Read More →
{ 9 Comments }
On the Data worksheet, data for payments received from different customers is shown invoice wise. Since payments are received in tranches, they are shown in different columns – column C and D show dates and amounts received in the first tranche respectively and column E and F show dates and amounts received in the second […]
Read More →
{ 10 Comments }
Assume quantity sold date by date and City in a three column database. The objective is to determine year wise, month wise and City wise running total of quantity sold in a Pivot Table. The issue which will arise with generating this result in a Pivot Table will be that the Show Values As > Running […]
Read More →
{ 6 Comments }
With Power Business Intelligence (BI) tools of Excel 2013, one can metamorphose raw data and/or results of complex calculations into stunning and interactive visualizations. Power View (one of the four components of Power BI) allows one to create a PPT like flow in Excel thus allowing one to weave a story. To be able to interact with/create visualizations, […]
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 →
{ 2 Comments }
Assume a file with two worksheets. The first worksheet has data in column A:H (8 columns) and the second one has data from B:J (9 columns). One may want to create a third worksheet with specific columns of data appended from both worksheets – the columns to be shown in the third worksheet are not […]
Read More →
Any lookup function i.e., VLOOKUP(), LOOKUP(), INDEX()/MATCH(), HLOOKUP() requires the text lookup variable (the first input of the VLOOKUP(), HLOOKUP() etc. ) to be spelled exactly the same as the entries in the lookup column. If the spelling does not match, the result is #NA. To some extent, inexact searches can be performed by concatenating wildcard […]
Read More →
{ 16 Comments }
Assume that a tabular database shows Year wise, month wise. Account and Activity wise expenditure under difference Expenditure heads. While it is easy to drag columns inside a Pivot Table to analyse data, one may want to compute the Account wise and Activity wise Variance between May and August for all expense types except two. If […]
Read More →
Perform different calculations in the Subtotal/Grand Total column of a Pivot Table
{ 33 Comments }