SHARE
In 1996, Microsoft boasted 30 million people using Microsoft Excel worldwide. Yes, that was a long time ago, and Excel was brand spanking new, but that was a number Microsoft was proud of at the time. Today, 25 years later, there are over 750 million users. Dang! It won’t be long until Microsoft celebrates crossing the 1 billion user mark, and for good reason. Excel has become the baseline application for data management and business intelligence.
In a world where Data is King, and will continue to be, Microsoft Excel does so much to help people and organizations collect, clean, manage, analyze, and present data. There really is no other way to say it, so here’s a shot between the eyes. Knowing Microsoft Excel at a novice level is a requirement to get a job these days. Attaining an expert level understanding is a prerequisite to land or move upward to an elite job. There really isn’t a reason not to learn this program, especially considering it has been written and constantly upgraded to be learned and conquered by humans.
Microsoft Excel does a lot and can make your business and personal lives more manageable and efficient. Let’s take a look at what are commonly known as the Top 10 features in this awesome program to motivate you and secure that higher level of proficiency.
Relative vs Absolute referencing
Knowing the differences between the two is key to the fundamental success of creating and linking formulas. Should the reference be static as-is, focused on a single location? Then it needs to be absolute. Should the reference by dynamic and able to span ranges? Then it needs to be relative.
Linking formulas between worksheets and workbooks
Are you going to be working with totals that need to be carried over varying worksheets housed in the same workbook? If so you will want to have a solid understanding of 3D references. If your formulas will be carried over to different workbooks, you will want to have a solid understanding of linking workbooks, along with the importance of file locations.
Creating charts and pivot charts
Creating a chart to visually represent your data is an invaluable tool that makes it easy for everybody to understand what they are actually looking at. There are many chart types utilizing different axes dependent on each dataset you are displaying. Pivot Charts are generated from Pivot Tables enabling you to summarize large datasets visually and change the data on the fly. Charts have become increasingly popular by groups that do not necessarily crunch numbers but want to easily understand the end result.
Pivot Tables
Generating a Pivot Table from a small or massive dataset is a savvy way to both display and summarize the information you need to focus on. The data can then be “Pivoted” to re-arrange the same data and allow for focusing on different segments of the dataset. You can decide which field will be used for the aggregate, what you want to display as row data, column data, and what you would like to filter for. Each pivot can then be displayed as a Pivot Chart for maximum comprehensive output to various audiences.
Data consolidation and subtotaling
If number crunching is your thing, Data consolidation allows row or columnar data from a single sheet, workbook, or multiple workbooks including as many ranges as possible to display running totals on a new sheet. These totals can be static, (not updating any further) or linked so they will update whenever the live data changes. Subtotals allow you to group your data and view it as only the grand total, subtotals with the grand total, or all row data with subtotals at each group and a grand total.
Protecting ranges, sheets, workbooks, files
Securing our work in Excel is a bit trickier than most of us think. A simple password makes the entire file read only. A password on one sheet still allows the user to delete the sheet itself. “Passwording” the workbook only protects the structure of the workbook and allows for any edits and deletions to the sheets in the workbook. What if you want to allow users to enter some information but at the same time need some items protected? Allowing users to edit ranges on a protected sheet with the rest of the sheet and workbook protected becomes necessary.
Basic built-in aggregate functions
Excel has hundreds of functions already set up for you making it easier to perform anything from simple summation to complex correlations. Getting to know the Function Library and how to use the functions makes math second nature. There are Text functions, Statistical functions, logical functions, date functions, database functions, and the most used standard aggregate functions. That is just listing a few from a vast library available to help derive answers. Learning how to use these functions will take your spreadsheets to a new level while saving hours of work creating formulas.
Tables with quick data analysis
Creating tables in Excel is key for both expanding datasets and aesthetics of the dataset. Tables grow with your data keeping a uniformed style for ease of readability. Once your data is in a table you can with a single click perform many analyses including the Totals row, conditional formatting, charting, sparklines, and more. This is an excellent tool when you need to convey different data visualizations or outcomes instantly.
Creating and editing macros
Excel often involves redundant tasks on datasets. Macros enable you to record a set of events or a process and save those steps. You can then use the macro you have recorded to repeat the steps for you in about a nanosecond. It is a brilliant tool that saves you from doing the same thing repeatedly, as well as allowing you to record complex steps that you otherwise might not remember at a later time. Automating any repeating process saves both brainpower and time. Editing macros is done with Visual Basic. Once you are editing your code the options for automation are practically endless.
Dashboards
Excel dashboards are used to display overviews of large data tracks. They include objects such as charts, tables, and gauges to illustrate the data in a simplified format. Dashboards simplify the process of decision making by allowing you to include all the vital parts of the data in the same worksheet. Knowledge of Excel charts and tables are necessary to bring them together as a dashboard.
When you become fluent in these top 10 features of Microsoft, you will have the skillset to proudly boast you are more than comfortable with Excel, along with the confidence to get the project or position you are competing for. Having the upper hand with Excel is often the deciding factor when eliminating candidates. Bottom line, get to know Excel beyond the basic features.
