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, you will need to install Microsoft Office Professional Plus 2013 (this version will already have two of the four components of Power BI - PowerPivot and Power View). Additionally, you will have to install the following add-ins from Microsoft (the other two components of Power BI)
1. Power Query; and
2. Power Map
I have tried to showcase the prowess of Power BI tools of Excel 2013 in these two workbooks:
1. An overview of the BRIC Economies
2. Sales data analysis
Just in case you do not have Microsoft Office Professional Plus 2013, you can see the features of Power BI in this 10 minutes video of the first workbook - "An overview of the BRIC Economies".
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:
1. Employees only (those who attended without spouse and children); and
2. Employees, spouse and children (Family); and
3. Employees and spouse (no children); and
4. Employees and children (no spouse)
You may refer to my solution in this workbook. I have solved this problem using Set Theory and Venn Diagram.
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 at the same position on both worksheets i.e. while the Inventory Date data is in column A on sheet1, it is in column H on sheet2.
You may refer to my solution in "Resultant Data - final" worksheet of this workbook.
I have solved this problem using Power Query - a free add-in from Microsoft for Excel 2010 and higher versions. The result is dynamic and refreshable (just as in a Pivot Table).
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 characters (? and *) to the lookup variable.
The task is to increase MS Excel's tolerance towards spelling mistakes and inexact text matches across two tables. For e.g., when performing a lookup, somehow MS Excel should be able to understand the Delhi City is the same as City, Delhi. In order to perform, inexact searches, you can download and install Microsoft's Fuzzy lookup add-in from here.
I have run this add-in on two live datasets in this workbook.
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 one attempts to write a calculated item formula within a Pivot Table, then variance rows will appear even for cases where Budget figures are equal to Actual figures i.e. where variance is equal to 0.
You may view the question, actual dataset, the failed Pivot Table attempt and final Power Query attempt in this workbook.
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) the Elearning courses taken by various staff members (along with their Line Managers). However, a Pivot Table will not show the staff members (along with their Line Managers) who did not take the Elearning Course.
You may refer to the data set and my workaround in this workbook. My solution uses the Microsoft Power Query add-in for MS Excel 2010 and higher versions. One can download and install the tool from here.
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 key, then at the bottom right of your MS Excel screen (the task bar), a white progress bar appears which reads "Calculating (4 processors) 1%". Once has to wait for the processing to complete before copying the formula down. The process of copying is obviously just as time consuming and resource intensive.
In these situations, a workaround is the JOIN the tables in MS Access and then get the data back in MS Excel. For accomplishing this, one must have at least some working knowledge of "SQL Query" writing.
If you are using the Power Query add-in for MS Excel 2010 and higher versions, then one can fetch data from another worksheet or workbook without writing a single formula. Furthermore, there is absolutely no lag in generating the result.
You may refer to my Power Query solution in this workbook (This is a 20 MB file)
Let's say the data sheet has Sales and Profit data by Product, Region, Salesman and Date. One may want to analyse the following via a Pivot Table
"The Sales Delta by year, month, salesman and product i.e. for May 2012, compute the [Sales in May 2012] less [Sales in May 2011] for every salesman and product. Do the same for every month for two year (2012 and 2013)."
While this is easy to accomplish using the "Show Value As" feature of a Pivot Table, the problem arises when a particular year is filtered out from the Pivot Table. This activity of filtering out a year from the Pivot Table causes the other computed Sales Delta figures to vanish.
This problem can be overcome by creating a Pivot Table from the PowerPivot tool of Excel 2010 and higher versions.
You may refer to the question, Pivot Table attempt and PowerPivot solution in this workbook.
Assume numbers from 1 to 8 in range B3:B10 and numbers from 9 to 16 in range C3:C10. The objective is to create all possible combinations in column E from these two ranges without using VBA (macros). This combined range of all possible combinations is called a Cartesian product.
You may refer to the following steps to create all possible combinations in column E
1. Type a heading in cell B2, say Data Set1. Format cell B2 to Bold (Ctrl+B).
2. Type a heading in cell C2, say Data Set2. Format cell C2 to Bold (Ctrl+B).
3. Select range B2:B10, press Ctrl+F3 > New and in the name box, type Data_set1
4. Select range C2:C10, press Ctrl+F3 > New and in the name box, type Data_set2
5. Select range B2:B10 and press Ctrl+T > OK
6. Select range C2:C10 and press Ctrl+T > OK
7. Save the file at your desired location, say on your desktop
8. Select cell E2 and go to Data > From Other Sources > From Microsoft Query > Excel Files > OK
9. In the Select Workbook dialog box, navigate to the folder and select the file which you just saved on the desktop (in step 7 above) and click on OK
10. With Data_set1 selected, click on the > symbol
11. Select Data_set2 and click on the > symbol
12. Click on Next > OK
13. Go to File > Return Data to Microsoft Excel
14. In the Import Data box, select cell E2 in the Existing Worksheet box and click on OK. Just in case, you do not see the Import Data box, after step 12, press Alt+Tab.
15. You should now see your desired result in range E2:F66.
Furthermore, since you had converted the ranges in column B and column C to Tables individually, if you add additional rows of data to column B and/or column C, just refresh while you are clicked on any cell of the result range. This will update the table in the result area.
Assume a beginning date in cell A7 and ending date in cell B7. In range C7:E7, the task is to compute the years (in cell C7), remaining months (in cell D7) and remaining days (in cell E7). As an example, if one types in 12-March-2013 in cell A7 and 24-March-2014 in cell B7, then the result in range C7:E7 should be 1,12 and 12 respectively. The formulas for getting these results are:
1. In cell C7 - =DATEDIF($A7,$B7,"y")
2. In cell D7 - =DATEDIF($A7,$B7,"ym")
3. In cell E7 - =DATEDIF($A7,$B7,"md")
So far so good. All the results are correct. However, there is a problem with the "md" syntax of the DATEDIF() function. Try this
1. In cell A7, enter 31-July-2007
2. In cell B7, enter 02-March-2009
3. In cell C7, enter =DATEDIF($A7,$B7,"y")
4. In cell D7, enter =DATEDIF($A7,$B7,"ym")
5. In cell E7, enter =DATEDIF($A7,$B7,"md")
The result in cell E7 will be -1 which is clearly incorrect. To get the correct result of 2 in cell E7, try this formula instead
Hope this helps.