How to pass an analytics job test – Part II – MS Excel.


How to pass an analytics job test - Part II - Microsoft Excel

          Even with the rise of use of R, Python, SAS and other more scientific analytical tools, Microsoft Excel remains the most popular data analysis tool. While we have gone over a solution for an analytics job test in SQL last month , you are much more likely to encounter a job test in Microsoft Excel for your next analytics opportunity. While I would personally argue that this particular test is actually better solved with SQL, the employer believes that the applicants instead need to apply their Excel skills to demonstrate their proficiency and acumen. As before, we should start by asking questions about the problem at hand and trying to get as much clarification as needed or state our assumptions. However, since spreadsheets are less forgiving from the presentation point of view than the databases, I would strongly recommend that we would also take a few minutes to format any workbooks provided by the prospective employer. Chances are they would recognize your level of professionalism by looking at clean and presentable file. Your stylistic preferences might be different, but as a minimum I would remove gridlines, add filters/format as tables larger datasets, freeze panes, and add at least one to two colors to the otherwise monochrome layout.

Continue reading

17 Excel Functions to use in 2017.


17 Excel Functions for 2017

          Versatility of Excel’s built-in functions is undeniably one of the main reasons behind this program’s popularity. As users, we have the flexibility to compose complex formulas incorporating multiple functions in our solution to achieve substantial gains in productivity. Most people have their own go-to Excel functions, be it: financial, date & time, math & trigonometry, statistics, look up & reference, database, text (manipulation), or logical. We’ve covered some of these functions already, but below is a compilation of 17 relatively under-used Excel functions you might want to add to your professional repertoire in the new year. Fair warning, you might need to have Office 365 version of Excel for all of the functions to work.

          1. PMT
          2. DATEDIF
          3. LARGE
          4. REPT
          5. LEN
          6. TRIM
          7. SUBSTITUTE
          8. TEXT
          9. IFS
          10. CONVERT
          11. NETWORKDAYS
          12. HLOOKUP
          13. INDIRECT
          14. OFFSET
          15. TRANSPOSE
          16. DSUM
          17. RAND
          BONUS: SUMPRODUCT

Continue reading

Using Excel IFS and SWITCH functions.


Using Excel IFS and SWITCH Functions

          Making Rent vs. Buy decisions always seemed like a no-brainer to me. If you hold on to your purchase long enough, you will be saving money: be it a car or a computer software. Case in point: it costs only $ 109.99 to purchase a license for Excel 2016 vs $69.99/year to lease Office 365 Personal, which includes Excel, as well as other Office products. Assuming you only need to use one product that truly matters, and you will use it for more than 19 months, buying is cheaper than renting. The longer you use the product without upgrading, the more money you save. This held true until February of this year, when Microsoft introduced 6 new functions available exclusively to Office 365 subscribers: TEXTJOIN, CONCAT, IFS, SWITCH, MAXIFS and MINIFS. This addition alone, coupled with introduction of Funnel charts might steer more users towards the subscription model. In this post we will review the IFS and SWITCH functions. Let’s say goodbye to nested if functions , we have already discussed on this blog.
Continue reading

Summarizing Excel PivotTables with GETPIVOTDATA Function.


Using Excel's GETPIVOTDATA Function

          Have you ever tried to select a cell within Excel’s PivotTable to create a regular link, only to realize that such formula cannot be easily copied over? You might be generating GetPivotData function without realizing it. As this Microsoft’s help page tells us: GETPIVOTDATA function “returns data stored in a PivotTable report. You can use GETPIVOTDATA to retrieve summary data from a PivotTable report, provided the summary data is visible in the report.” Main takeaways are the following: 1) GETPIVOTDATA is a summary function, and 2) it only works with visible PivotTable data. It’s syntax includes: Data_field – required field referencing data field of interest; Pivot_table – reference to any cell or range of cells withing a PivotTable report; and optional Field/Item combination, with text values enclosed in quotations. While, GetPivotData feature is activated by default, you can easily turn it off by selecting the Options menu on the ANALYZE Ribbon, and checking off “Generate GetPivotData” selection:

Continue reading