Assume two data lists. List1 has Test1,Test2 and Test3 in A2:A4 and List2 has Test1,Test4,Test2 in B2:B4. I would like to align data from both lists to appear as follows: List1 List2 Test1 Test1 Test2 Test2 Test3 Test4 You may refer to my solution in this workbook. You may watch a short video of my solution here
Read More →
{ 9 Comments }
Visualise a dataset from where you want to create different columns for entries which are listed down in one single column. In a scenario where the entries which have to be shown under each of these columns so created are numeric, a simple Pivot Table will sufficed. However, if entries to be shown under the […]
Read More →
{ 6 Comments }
Visualise a workbook with two worksheets. The tab name of the first worksheet is “My Table” and of the other worksheet is “My colleagues Table”. Each worksheet has 4 columns – First Name, Last Name, Pin code and Mobile Number. In “My Table”, the first, second and third columns are filled up and in “My […]
Read More →
When creating a Pivot Table one can easily apply the Maximum function for figures in the Value area section by right clicking and choosing Maximum from “Summarise Values By” > Maximum. The maximum so computed for figures in the Value area section is a “conditional maximum” because the numbers in the Value area section will […]
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 →
{ 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 →
{ 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 →
{ 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 →
{ 23 Comments }
One worksheet shows the date wise courses (External Course, Internal Course and Elearning Course) taken by staff members. The data also shows the Line Managers that they report to. Another worksheet lists down all staff members and the Line Managers that they report to. From data on the first worksheet, one can easily determine (via a Pivot Table) […]
Read More →
{ 0 Comments }
Performing calculations or writing formulas on large databases pose the following major problems: 1. Time taken to process is unduly long; and 2. File size increases disproportionately A typical problem is fetching data from another worksheet or workbook via the VLOOKUP() function. Quite often, when one confirms the VLOOKUP() formula in a single cell with the Enter […]
Read More →
Align data from two columns
{ 12 Comments }