Through my internship with a Cincinnati Insurance company, I learned simple Microsoft Excel tools and wish to share them with you in this video. My process was pretty straight forward: learn the skills and share them. I consider my Capstone a success. Not only did I learn a bunch myself, but I was able to help some of my peers become more prepared for their future careers too.

Approximately a year later, and I have learned so much about Microsoft Excel, it’s advanced data manipulation tools, and it’s importance in the modern workplace. Excel is a tool that is often overlooked and overshadowed. My goal in this project was to share the skills I learned through my work with Great American Insurance with other young professionals and to justify the significance of learning these skills through research. I have completed all of my goals and consider this project a success.

Research published in Information Systems Education Journal, accounting.com and several other sources support my hypothesis that Excel is absolutely still necessary for everyday functions of businesses, despite it’s age. Excel is able to keep up with it’s younger, more complex competitors because of its legacy, simplicity, flexibility, and continuous updates.

This research gave me the justification I wanted in order to to push my fellow young professionals to take time to practice some simple, yet advanced data manipulation functions in Excel. I shared my Capstone project (this blog) with my classmates, particularly those in the Data Analytics Major like myself. This blog has already been proven to be helpful. One friend actually told me she used my posts to help her in her own job; my sister, who is out of college, is going to begin taking an online Excel class; I, myself now have a resource to see a few of the skills I’ve mastered over the past 9 month.

In conclusion, I am thankful my work with Great American Insurance presented me with projects which pushed me to learn data manipulation in Excel on my own. Because of my surprise in how much Excel can do, I documented some of the most helpful functions, and shared them with other students in the hope to aid them in their professional endeavors.

SUMIFS is a very basic and useful function in Excel that is just one step above a SUMIF. If you are not familiar with the SUMIF function, read about it here first.

SUMIFS is basically the same idea of SUMIF except you can have several criteria, or if statements, you want your data to follow.

Here is the structure of a SUMIFS: =SUMIFS (sum_range, range1, criteria1, [range2], [criteria2], …)

In words, the only values in sum_range which will be summed are those in which range1 passes critera1 AND range2 passes critera2 AND…

For Example: Imagine you are trying to find the number of shoes which are in a particular price range and are a certain color. Suppose you have the following data. To find the quantity of shoes which follow your attributes, you would use a SUMIFS similar to the one below.

There are 8 pairs of shoes which cost less than $40 and are Red.

SUMIFS are quite simple with a little practice and are very useful in larger data problems.

I have completed all the research regarding the importance of Microsoft Excel in the workplace, and my findings support my accusations. According to accountingtoday.com, Excel, nearly 30 years after it’s beginning, is still relevant in the workplace. It has kept up with the times by improving it’s data manipulation tools, visualizations, and versatility. Robert Half accounting firm also supports the ongoing importance of Excel in the workplace because of its simplicity of use, low cost, and practicality. The fact of the matter is that many of the day-to-day calculations businesses need to make do not need fancy, advanced software. Businesses have been using Excel for a couple decades now, and some things just don’t need to be changed.

Going forward in my Capstone Project, I need to create my presentation and gather my research into an essay. I have begun to share my project with my friends and other students in my classes. I have used my skills in excel to demonstrate to friends how to create personal budgets and track classes/ credit hours to stay on track for graduation. While these activities only brush the surface of the things Excel can do, it demonstrates it’s versatility and gets some young professionals exposed to things that Excel can do.

Sources:

https://www.accountingtoday.com/opinion/why-excel-is-still-relevant-in-2017

https://www.roberthalf.com/blog/salaries-and-skills/excel-for-finance-professionals-still-king-of-the-spreadsheets

While the regular SUM function adds up all the values in a given range, SUMIF restricts the values which you would like to sum in a given range. The Structure of a SUMIF is: =SUMIF (range, criteria, [sum_range]).

Where range is the column which has a restrictive quality, criteria is the condition for the data to follow, and sum_range is the range of values to sum IF it passes said condition.

The condition may use comparison operands (<, >, =,<> (not equals)) or strings of characters, both in quotations.

For example, consider the chart below:

To find the number of total pairs of shoes in the store we would simply use the SUM function over the entire “quantity” column. However to find the number of red pairs of shoes only, we would use SUMIF as shown below.

After yet another semester working with Excel and documenting some tools I’ve learned, I think it is even more evident that Microsoft Excel is an extremely powerful data manipulation software. I focused mainly on exploring the VLOOKUP, SUMIF, SUMIFS, and INDEX functions. While these are only a few of the skills I have acquired through my work with Great American Insurance, they are definitely among the simplest and most frequently used in my experience.

I personally believe that these four functions alone are enough for anyone to start working and manipulating data. Not only are they are common functions asked in job interviews, but the logic behind them nurtures critical thinking and problem solving skills.

Although I legally cannot go into specifics, my work with Great American this semester included updating formulas in spreadsheets, creating new spreadsheets that help organize data, and exporting and manipulating data tables from larger databases. In all of my work, I can honestly say Excel was a necessary tool.

Microsoft Excel is often overshadowed by new, fancier data manipulation, but it is undoubtedly underestimated. It is also so widely used that I firmly believe every young professional should have a basic understanding of its advanced tools, particularly the four I mentioned. I hope by sharing my experiences, I inspire others to familiarize themselves with Microsoft Excel and all the advanced functions it has to offer.

As this semester continues I intend to do more research on the importance of Microsoft Excel not only in my workplace but business all around the country. I feel strongly about what I have discovered in my work, but I need to support my findings with more factual evidence. I also need to promote my Capstone to my audience, young professionals like myself, who wish to learn some simple advanced functions in Microsoft Excel to separate themselves from others in the job market.

The INDEX function in excel returns the value of a cell at a given location (position in a row and column). It works as follows: =INDEX (array, row_num, [col_num], [area_num])

where array is the range(s)/table(s) of values to search in, row_num  is the number row (counting from the start of the array) you are looking to pull, col_num (optional) is the number of columns over from the start of the range you’d like to pull data from, and area_num (optional) is used to determine which range to use in the lookup, if more than one range is specified.

For example:

The equation to answer the second question (What is the major of the 6th person) is: =INDEX(B4:D11,6,3)

The area_num criteria is used in a situation like the following:

Now that I have 2 ranges of data, I must specify which one to used based on the data I wish to pull. The equation for the second question is : =INDEX((B4:C11, B14:C21), 6,2,1 )

The INDEX function is often paired with the MATCH function. The MATCH function returns the placement (index) of information you are looking for. When nested inside INDEX it is very powerful.

For example:

The result of the MATCH function in this example is 3 and it is used to as the row location to lookup in the INDEX function.

On a larger scale, the INDEX and INDEX(MATCH) functions are extremely helpful and powerful tools when manipulating data in Microsoft Excel.

VLOOKUPs are used to match a value you are looking for in a table, and retrieve another value which corresponds to it.

The structure of a VLOOKUP is: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Where lookup_value is the value you want the table to match (this should be the first column in the range), table_array is the whole range of values in the table, col_index_num is the number of columns to the right of the lookup value you wish the function to return (index begins at 1), and [range_lookup] is whether you want the lookup values to be an exact (FALSE) or approximant (TRUE) match. Default is false.

For example, suppose we want to find how many McDonalds are in a particular state, given the appropriate data. We can easily find that value using VLOOKUP as shown below for North Carolina.

Throughout the coming semesters, I will be working 10-15 hours a week for Great American Insurance. My Goal is to not only complete the work for the company, but to also document my progress in the things I learn and to eventually share helpful tips and tools with other young professionals.

The majority of the work I complete for Great American is Excel based. Some of it involves more complicated tools and add-ons such as Microsoft Access Database, the use of Macros, and linking to external files. However, my goal in this project is not to document every detail of my work, or even explain the most impressive and complicated projects I work on. I really want to just document the basic Microsoft Excel tools that I find to be most commonly used, and the most helpful in hopes to aid other young professionals in their future internships and careers. Despite the vast use of Excel in workplaces around the country, I don’t think it receives enough attention. Excel provides tools at our fingertips that are easy to learn and use if only one takes the time to understand them.

Throughout the coming semesters I will document various Excel functions that I personally had to learn on the job, and post about them on this page. I will give a short description of the tool and an example of how it is used. I also plan to do a little research on the importance of Microsoft Excel in the workplace. Finding other sources to validate the value in learning these tools is one way to convince those reading my posts that it’s not just at Great American that these skills will be useful.

I hope to focus my project on 3 or 4 functions. This will allow for me to go more in depth, and keep the page simple to read and understand. I am looking forward to not only documenting my work and physically seeing my progress, but also hopefully inspiring others to learn these tools on their own to get ahead of the game a little bit.

By second semester (January) I hope to have the tools that I want to focus on chosen. I will then spend winter break and the start of the second semester creating examples and blog posts about these tools which should be simple enough for anyone to understand. Once I complete the “tutorial” posts, I will do another post with evidence on the importance of Excel in the workplace. I then will share my findings by sending my Capstone project link to STEM underclassmen and anyone else who may want to expand their data manipulation knowledge.

This past summer (2019), I was fortunate enough to land an internship with an insurance company in Cincinnati. With my freshman year here at Ohio State under my belt, I thought I knew everything I needed to know to jump right into any work they threw at me.

To much of my surprise, I learned that I lacked one skill that is vital in today’s job market: Microsoft Excel. While I had used basic Excel functions in some of my past classes, I did not truly have a grasp on the analytical and data manipulation tools that it has to offer.

The summer was defiantly a learning experience, which I much enjoyed. The skills I have learned will already set me aside during interviews in my future, but I also know that there is so much more for me to discover within Excel.

This is where my second year Capstone project through the OSU STEM EE Scholars program comes into play. Being skilled in data manipulation is a necessity in today’s industry job market, but unfortunately they are skills that many students lack. The Data Quality Campaign supports this idea by stating, “Data is one of the most powerful tools to inform, engage, and create opportunities for students”. Data is everywhere, and knowing how to use and analyze it effectively and efficiently will be useful in any career. Excel is one of the most basic and easiest to learn data manipulation programs, according to Klient Solutech, a business and leadership building website.  My goal in this project is to make more students familiar with advanced data manipulation tools, particularly in Excel, so they too are more prepared for their future careers.

I am continuing the work I began this summer by working part-time and remotely for the company in Cincinnati while taking my classes here in Columbus. Because I will still be working, I will still be learning more data manipulation and Excel tools as this semester progresses.  I will be documenting the functions, add-ins, and other features that use in my day-to-day work. I will then post a brief explanation of that tool on my ePortfolio under the Capstone tab (this page) so others can understand how they are used.

To learn more about why data manipulation and Excel skills are so beneficial to college students visit these sites:

https://dataqualitycampaign.org/resource/data-can-help-every-student-excel/

http://www.klientsolutech.com/importance-of-microsoft-excel-for-students/

I cant wait to see my own progress throughout the coming semesters and to help other students too!