Why becoming a pro at Excel will benefit you
You’re likely familiar with Excel. You might have suffered through it during a data analysis course in the past, or used it to make pretty charts for some marketing report or another. It seems simple enough, right? You get your data set, you put it in a cell, you use a formula that will then manipulate the numbers however you want. You click and drag, maybe copy and paste, and there. You’re done. All you need now is to insert a colorful pie chart or something.
But Excel is more than just dumping
data into cells and making pie charts. It offers an incredibly wide range of features that can streamline data-oriented tasks and make your workload (and your life) much easier. If you attended the alumni meeting last week, you were able to hear firsthand by former MIS students/working professionals talk about just how large of a role Excel has played in their daily lives.
Excel might seem intimidating at first, especially given all of its various formulas and charts and cell references. Regardless, it’s worth taking the time to learn. And it will definitely, absolutely, 100% look good on your resume. So let’s start with some of the most important features you can familiarize yourself with, and discuss just how they can change your understanding of Excel for the better.
Features to Learn
Pivot Tables
Pivot tables are commonly used to analyze large datasets without spending countless painful hours creating and editing your own tables.
Let’s say you work at a bookshop and you have sales reports for all books sold in a given month. You have a breakdown of book sales by type (fantasy, sci fi, etc), by month, and by average sales price. By setting up a pivot table based on this information, you can easily decide what you want to summarize.
For example, you can sort by type of book, then by month, and this criteria can then answer the question of “How many fantasy novels did we sell in April?”
Okay, so it’s not the coolest thing in the world, but it’s still worth spending the time to learn. The speed at which you can retrieve and filter for information is what makes pivot tables so helpful.
Dashboards
Dashboards are extremely useful for conveying your data in a more visual manner. You can store numerous pivot tables, charts, graphs, metrics, and other important details all in one place, which is great for presenting ideas and getting quick status updates for projects.
Here’s a quick example for how it works: let’s say you work for Sony Entertainment and you want to pitch the upcoming shiny new Playstation 5 console to investors. You construct a dashboard that contains all relevant information regarding the PS5, such as hardware specifications, expected units sold per quarter, and expected sales per region (eg, North America, Japan, etc).
You can take all of this data, compile it into one singular dashboard, and make it more clean, orderly, and presentable for upper management. Plus, if you make any changes to the data, all you have to do is hit refresh and all your charts, tables, etc will automatically be updated.
Macros
Macros are used to automate actions in Excel. Essentially it allows you to replicate things that you would normally take the time to do otherwise. And it’s cross-platform; certain macros can work across spreadsheets and applications such as Word and PowerPoint.
To give an example: you work in a business where you have an invoice sheet for the invoices you give to customers, and you have to copy and paste all of their information (name, address, purchase history, etc) into a spreadsheet. Macros can make Excel do the work for you.
In short, they’re important to learn because they save you time and eliminate any redundant tasks.
Conditional Formatting
This tool is extremely useful for highlighting important information in a given spreadsheet. It allows you to apply formats to a cell or range of cells. That formatting will then change depending on whatever value that cell or formula contains.
For example, let’s say you want the text of a cell to appear red only if its value is less than 0. If the cell meets that condition, and is indeed less than 0, then that cell’s text will be red.
There are numerous conditions you can add to your cells using Conditional Formatting (make them bold, change their color, etc) so definitely take the time to play around with it. Go to (Home>Styles>Conditional Formatting>Manage Rules) to start.
VLOOKUP
VLOOKUP stands for “vertical lookup.” It’s a function that allows you to search for specific information in your spreadsheet. It might seem pretty basic, but it’s extremely useful when you need to quickly find things in a massive pool of data.
Say you have a table containing data on computer parts, and you want to know the price of a motherboard. VLOOKUP will look for the value of the motherboard’s cell and return the matching value in a different column - in this case, the column containing its price. If it’s a little confusing at first, visit
(http://www.gcflearnfree.org/excel-tips/how-to-use-excels-vlookup-function/1/)
to for a quick and simple tutorial.
Other Useful Tips
If you’re completely lost with Excel, try to familiarize yourself with the most basic functions, such as SUM, IF, AVERAGE, etc. You definitely don’t need to know every single function, though - it all depends on what you’re trying to accomplish with your data. Knowing Excel is a valuable asset to have, especially if you’re interested in becoming a data or business intelligence analyst.
Tutorials to Consider
Excel Exposure (https://excelexposure.com/)
Excel Easy (http://www.excel-easy.com/)
Chandoo (http://chandoo.org/)
Wise Owl (https://www.youtube.com/user/WiseOwlTutorials)
Classes to Take
CSUSM (http://el.online.csusm.edu/class/microsoft-excel-series/) Through Extended Learning, CSUSM offers a Microsoft Excel series class, which includes introduction, intermediate, and advanced courses.
Udemy (https://www.udemy.com) Udemy provides online Excel classes that range from beginner to advanced.
Palomar College (http://www.palomar.edu/)
MiraCosta College (http://www.miracosta.cc.ca.us/) MiraCosta offers a course called Microsoft Excel for Business (CSIT 28).
