Excel is an extremely versatile and capable tool for presenting, analyzing, and visualizing data among other functions. It has a large range in complexity where it can be as simple as a spreadsheet to keep track of items or as complex as a graphical interface for a full fledge application. It is convenient and it requires minimum setup since it comes as part of the Microsoft Office Bundle which is used by a large percentage of companies.
During my first undergraduate which was in Mechanical Engineering, we used MATLAB, LABVIEW, and Solidworks for assignments but we rarely had to use Excel. Once I graduated and started working as a Manufacturing Engineer, I found out that the preferred format to handle data across not only my department but the rest of the company was Excel.
Throughout my career, I’ve leveled up my Excel skills. First, I focused on the graphing and formatting to be able to present data in a format that is easily digestible. The second level was getting the hang of all the different formulas which ranged from simple sum and concatenation to vlookups and array formulas. The third level is what led me into Computer Science, Automation and User Defined Formulas with VBA. This level has the highest potential because it can automate reporting and analysis that in many companies has been done manually for decades. I still struggle with this level and with having to work with VBA.
I think the following example summarizes my challenges and frustrations. As a side project at work, I wanted to create a program that could generate a Bill of Materials Tree flowchart with pictures from a Bill of Materials excel spreadsheet and picture files because I needed to make one but wanted to automate it for the future. My first instinct was to try to use VBA to read the data from the Excel file, which some logic calculate and extract the data that I needed, then with this data open a Visio file where I would drop and connect shapes with data in a tree-like layout. I believe this is possible to do with VBA but I was not able to do it. I automated about half of it then had to do the other half manually. The documentation for VBA can be confusing for me and I could not find many relevant examples. I put this specific project in the backburner after this. Fast-forward 18 months later, I need to make another giant flowchart. At this point, I start researching if there is another way to do this. I ended up implementing the exact functionality that I wanted by using python with a library for a software called graphviz. It takes in my spreadsheet and pictures and it outputs a giant flowchart with all the info I need with an efficient layout. My frustration is that I cannot share this program with other people without having to explain to them that they are going to need python (I tried to convert it to an exe but it is not working well with graphviz) and graphviz which can be pretty intimidating compared with receiving an excel file with a button on one of the tabs.
I still use Excel for most data tasks at work which I need to share or present. However I find myself gravitating towards python and other tools for more complex tasks. I love the convenience and capabilities of Excel on the lower end but I struggle completing higher end complex tasks using VBA.
