I have expertise with all aspects of Microsoft Office 365 and Excel 2016 with special emphasis on using pivot tables for data analytics. Pivot tables are the most powerful and underused feature available in Excel.
Most Excel users only utilize a small portion of Excel's total capabilities. My focus is to harness these underutilized, yet powerful analytical capabilities to assist clients through optimized business intelligence used to generate new revenue, keep existing business, and stay ahead of the competition. This is accomplished by creating predictive analytical visual models while addressing the most common problems faced by companies.
Recent Projects
Two years worth of sales totaling over 3 million transactions based on time of sale, location, and user info
Real estate tax assessments for all properties in Cook County for comparable homes and prospecting for clients
Weather reports from major cities spanning 100 years of high and low temperatures
Rental properties in particular neighborhoods to reduce time on market through amenities and monthly cost
Real estate properties for sale in all Chicago using past sales, tax assessments, and similar homes on the market
Federal registrations of patent and trademarks updated on a weekly basis to identify trends in business activity
Winning lottery numbers for all jackpot games in Illinois for players to visualize randomness of probability
Quotes for the NASDAQ / S&P 500 detailing high/low & volume of trades compared to competitors
Fuel economy for every make and model of all vehicles sold in the U.S. over the past ten years
Pivot Tables
My expertise with pivot tables include solving limitations and utilizing advanced techniques. This also includes using Power BI Desktop to import data from Excel for enhanced analysis:
Complete understanding and assessment of the underlying data source to dynamically build useful reports in an arrangement best suited to what the analysis is intended to answer.
Scenarios when pivot tables are the most effective: (1) large amount of data too difficult to analyze or summarize in a meaningful way; (2) finding relationships or unique values for one field; (3) data trends over various time periods; (4) frequent changes to data analysis; (5) when subtotals frequently include new additions; and (6) formatting data in way that is easy to chart.
Compatibility with older versions of Excel and limitations imposed by different file extensions (i.e. saving the pivot table as hard data).
Prepare data for effective pivot table reports: tabular layout, section headings, groups/columns, gaps/blank cells, and formatting. Other errors include blank spaces that trigger the count function instead of summation.
The limitations of built-in recommendations, such as rearranging, adding, or manipulating the automatically generated pivot tables.
Slicers to filter one or more pivot tables at the same time. The Timeline slicer is used for date fields.
Refreshing the cache when changes are made to the data source or rows/columns have been added. Sharing cache and deferring layout updates due to large data sources.
Management: style, number format, blanks/zeros, fields, layout styles, themes, subtotals, calculations, group/sort/filter, and date hierarchy.
Calculated Fields/Items to add a virtual column/row to the pivot table for analysis that requires data not in the original data source.
Pivot chart limitations: changing the pivot table dynamically impacts the chart, x/y axis structure, formatting, creating a chart from the data instead of the pivot table, conditional formatting, and preprogrammed scenarios.
Disparate data sources, such as external data (i.e. text files and Access) and multiple ranges (i.e. different worksheets). One feature is the Data Model for relationships between tables. Techniques also include data management to separate database tasks from using Excel as the presentation layer for data analysis.
ETL (extract, transform, load) using Power Query and limitations, such as refreshing the view and separation from the original data source. Also OLAP databases and cubes that contain the relationships and hierarchies.
Sharing pivot tables while limiting access to the underlying data or ability to manipulate the report (such as creating an interactive web page or through Power BI dashboards).
Power Pivot: join two related tables, analyze 100 million records, and DAX (data analysis expressions) calculations.
Create interactive dashboard elements from power pivot tables using Power View; and animate pivot tables over time using 3D Map.
Macros to record a sequence of keystrokes and instructions. Provide easy ways for others to manipulate pivot tables by clicking a single button to run an automated series of steps.
Create pivot tables with VBA (Visual Basic for Applications). The macro recorder is considered 90% perfect. The remaining 10% requires programming to complete. VBA is also used for advanced pivot table techniques, such as showing the top markets, filtering a recordset, conceptual filters, filter fields (i.e. allowing a user to select a particular product), and selecting items from a slicer.
Advanced techniques: automatic refresh when the workbook opens, refreshing all pivot tables in a workbook at the same time (i.e. using VBA), unique sorting orders, create hard data instead of saving the pivot table, rankings, using a defined range to automatically resize as data is added, AutoFilter, multiple number formats, frequency distribution, and user restrictions.
Using the GetPivotData formula to populate a shell report. This involves creating a pivot table with every possible data point that could ever be needed. A completed shell report contains all the necessary formatting. GtPivotData is then used to populate the shell report.
Standard Excel Features
Expertise covers the full range of Excel features: PivotTables, slicers, and relationships; PowerPivot; PivotCharts; formulas, equations, and functions; management of worksheets and workbooks; table filtering and sorting; charts; and graphs.
The majority of all spreadsheets contain typos, simple mistakes, or cut-and-paste errors. Other fixable problems include incorrect values being returned, the wrong formula being used, or omissions.
Other common issues involve statistical errors, numerical precision, cells with error messages, dates, legacy files, version control, templates, and updates from concurrent users that overwrite data.
VLOOKUP and HLOOKUP
Pivot tables are not always necessary. VLOOKUP is the standard function used to locate a row. The goal is to arrange the spreadsheet columns so that the value being returned is immediately to the right of the search term. Common problems are the #N/A, #REF, #VALUE, and #NAME errors. Best practices when utilizing VLOOKUP is to avoid entering numbers or dates as textual, sorting the first column, using wildcards, and not using erroneous character such as trailing spaces.
HLOOKUP is used to return the vertical value in a column underneath the search term. One particular problem area is when the search returns a different value than expected because the exact term was not found.
The MATCH and INDEX functions are alternatives to VLOOKUP and HLOOKUP, such as when the position of an item is needed instead of the value itself. The INDEX function has a unique feature that distinguishes between returning the value or reference to a cell.
Excel Versions
Excel is prone to hackers gaining complete control of a computer or using a compromised spreadsheet as the conduit for a data breach, which requires close attention to how the data is accessed.
Expertise includes the limitation and interoperability concerns for each Excel version, including the current Office 365 online subscription packages and Excel 2016.