The definition of skill level in Microsoft Excel is still agreed by many to be relative- I inclusive. Yet, work organizations keep seeking ways to determine the level of skill of job candidates in the application of Microsoft Excel. In this post, I attempt to define levels of skill and the content of these levels of skill in Microsoft Excel.
You know when you're waiting for the bus, you get none for ages and then loads come along at once—that's the case with Excel 2016, which unveils six new chart types, each with their own specialist uses. Here are my favorites: Pareto chart (support.office.com) 5. Histograms and Pareto charts. So if you need a retirement fund of at least Rs. 25 Lakhs, you can choose 1 of the above marked options. Automatically Lookup Values and Calculate Dates. It is tedious to input data manually all the time.
Levels of Skill in Microsoft Excel
I propose four levels – the basic Level, the intermediate Level, the expert Level and the Advanced Level. It is important to point out here that in my opinion, these levels are in a continuum and therefore, anyone claiming any of these levels implies progressive skill acquisition through the continuum. Put other ways, the intermediate level users must have acquired all the skills a basic level user should have and use. So is the expert level user who must have been through all it takes to be a basic user and an intermediate user.
The Basic Level
What should define the basic level of skill in Microsoft Excel? When you first open Microsoft Excel, there are those tasks that you can do just by simply responding to the user friendly interface of the application. Especially for persons who may have used such application as Microsoft Word. The Clipboard, Font, Alignment and Number groups in the HOME tab are similar to those found in Microsoft Word. With these tools, a user can present static data on a spreadsheet and afterwards claims that s/he uses Microsoft Excel appreciably well.
On a standard note, I propose that the basic skill level for Microsoft Excel should be an in-depth knowledge of three ribbons Tabs and their tools – The HOME Tab, The PAGE LAYOUT Tab and the VIEW Tab. The only permissible exceptions (just my opinion) are using formulas in conditional formats and the ARRANGE group in the Page layout tab. On functions, I accept as default, the list of functions you get when you click on the drop-down button of the auto-sum icon – SUM, AVERAGE, COUNT NUMBERS, MAX and MIN. Basic users should know how to apply these functions in formulas within spreadsheets. Additionally, an understanding of the spreadsheet inter-face and the ability to do subtractions, multiplications and division are inclusive in the calculation skills that a basic uses should have for use. Armed with these, the basic user can do a lot in number computation, data presentation beyond static data presentation and printing. This level should score at most three on an Excel Skill- Scale of one to ten.
The Intermediate Level
The intermediate level is the basic level plus more. The more here is defined by depth. The intermediate Microsoft Excel user, driven by what can be done with Excel functions learned in the basic level, delves into the tools in the FORMULA tab and the function library therein. S/he learns and knows how to use the function wizard to enter function arguments/inputs. The function wizard enables basic users to understand, through use and overtime, two things: (1) The arguments/inputs that some difficult- to- comprehend functions require e.g Lookup functions and (2) The order at which arguments/inputs are entered. The intermediate Level is a level where more on TEXT Functions, DATE Functions, LOOKUP functions and MATH functions should be learned and used. Formula Auditing is also a group in the FORMULA Tab that should be learned and used in the Intermediate level of Excel usage. This prepares users for auditing more complex use of functions in formulas such as those found in complex/compound formulas – formulas that have two or more functions.
It is worthy of note here that an Excel FUNCTION and an Excel FORMULA are not the same thing. A function is a named program that process inputs and returns a result (output) within a spreadsheet cell. An Excel formula is a user defined cell entry that has at least one Excel function (could be two or more functions) to derive a result. In other words, functions are the building blocks of formulas in Excel.
The intermediate level of Excel skills should be where the user learns how Excel functions 'think' or how they 'behave' when you use them in a cell and copy/paste/drag or move them across sheets. Such depth of understanding will come handy when implementing such things as absolute and relative referencing and when the user starts entering formulas without the formula wizard. This level may include introductory use of such tools as PivotTable, charting and recording macros for repetitive tasks.
On a scale of one to ten, the intermediate level falls on or between four and five.
Expert Level
An Expert is one who knows so much, over time and through practice that s/he can act as a teacher, an implementation-lead and hand-on knowledge worker. Cricut expression 2 design studio software. How will this be possible if s/he know nothing of the basic and intermediate levels of Microsoft Excel usage?
This user level incorporates more. S/he must become a data specialist and therefore must delve deeper in the use of Excel tools in general. Here, s/he adds the DATA Tab tools to all previously learned and being used. Connecting to external data, Consolidating data, using PivotTables and Subtotals beyond the introductory level, Charting data and formatting Charts (The INSERT tab) are skills the Expert must have and use.
Armed with an understanding of how Excel and Excel functions think, the Expert is able to create compound and complex formulas (such as array formulas) in other to achieve much with less. The Expert is marked with depth of spreadsheet knowledge and efficiency in its use. With this disposition, the expert is able to build spreadsheet models, dashboards and automated templates for use by lower level users of Excel. On a scale of one to ten, the Expert level falls on or between a six or a seven.
Advanced Level
In my opinion, The advanced level is one in which a user acquires the skill to extend the default tools/offerings in Microsoft Excel in other to carry out more customized data processing and presentation. This Level is beyond mere macro recording. It is deep in VBA (Visual Basic for Applications) programming. The advanced level user must have the ability to write VBA codes in the form of sub procedures, user defined functions and further build entry forms for data inputs and updates. This level of Microsoft Excel can be called the developer level and that calls for the DEVELOPER Tab. All tools in the developer tab are necessary for the advance level user of Excel. The advanced level user now combines Basic, intermediate, expert and advanced level to deliver, almost often, customized spreadsheet applications.
The advanced level, on a scale of one to ten is an eight `or nine. The knowledge and use of Excel never seem to End. So a 10/10 is reserved for any who possess infinite knowledge of Excel.
https://salesgoo.weebly.com/blackmagic-design-fusion-studio-16-1-0-download-free.html. Keyboard Shortcuts
Note that I did not include keyboard shortcuts in my attempt to define Excel skill levels. This is because; again in my opinion keyboard shortcuts are not unique to Excel. All office applications have keyboard shortcuts and there are there as options to mouse clicks. Excel users can achieve any level of skill in Excel using either mouse clicks or keyboard shortcuts or a combination of both.
What Do You Know About Excel Worksheets
If your organization has upgraded to Office 2016 or Office 365, you now have access to Excel 2016. Now what? Are there any new features? The answer is yes, especially if you need business intelligence tools and new ways to present your data.
Don't miss out on new Excel 2016 features. Here are 10 I'm most excited about:
1. The 'Tell Me' Box
The 'tell me' box is one of the most obvious new arrivals to the Excel party and, quite frankly, it's a shame it wasn't around in the 2007 edition when the menu ribbon was initially reduced. (Remember how hard it was to find our favorite tools?) The 'tell me' box is essentially a search function that lists functions and operations matching your search terms. It's a handy shortcut to get you where you want to go! There's also smart look-up which will use the internet to search relevant articles for your search term.
2. Forecasting
Excel 2016 has seen the addition of 'one-click' statistical forecasting for data that has a time element associated with it. As long as you have a decent amount of data it will be able to forecast past your last data point, as well as show levels of confidence at each end of the spectrum. What's also great about the forecasting function is that if Excel can see trends due to seasonality, it will factor them into the forecast. Pretty smart! Once you've made your forecast you'll be able to display it as either a line or bar graph, but be aware that the data used to create your graph of choice will not link to the chart. If you make changes to the data these will not automatically be reflected in the chart.
3. Search field (PivotTables)
PivotTables have been revitalized with a search bar that makes life easier when dealing with large data sets with numerous fields. Just search for the field you're looking for and it pops up for you to select. https://heregfil123.weebly.com/printer-tools-software.html.
4. Date Grouping (PivotTables)
Previously, Excel PivotTables would always register dates individually—your charts would have hundreds of fields along the date axis. Now dates are automatically grouped into Years, Quarters and Months. If you want to delve deeper you simply use the + option next to the date segment to expand the next level of grouping. I think you'll find it's much more manageable.
New charts
The people at Microsoft claim that Excel has not seen the addition of a new chart type since the 1997 edition. You know when you're waiting for the bus, you get none for ages and then loads come along at once—that's the case with Excel 2016, which unveils six new chart types, each with their own specialist uses. Here are my favorites:
Pareto chart (support.office.com)
5. Histograms and Pareto charts
Things To Know About Excel
Histograms show you frequencies (e.g. how much of one product has sold against another). They're not based on categories but on values that are allocated into different bins. If you want to alter this to show categories you can do that. Pareto charts go one step further by sorting these frequencies and adding a cumulative percentage line to give you a trend through the data.
Sunburst Chart in Office 2016 for Windows (support.office.com)
6. Sunburst charts
These show values by hierarchy. A good use for sunburst charts would be to analyze sales of a company and break it down by salesperson, customers and products purchased. The chart will plot the sales people at the highest level and size their sections by the sales they have made. The next level will be the customers and the total amount they have spent with their respective salesperson. The final level shows the products bought by each customer. Sunburst charts allow you to go to dozens of levels so you can really drill down into your data.
Waterfall chart in Office 2016 for Windows (support.office.com)
7. Waterfall charts
Waterfall charts are great for showing movement from an opening position to a closing position and are therefore ideal for plotting financial data, such as cash flow over a period of time. They show your opening balance, the progression through the various stages of trading and then stop at your closing balance.
8. 3D data map
Perhaps the most impressive looking of all the new visualizations in Excel 2016, the 3D data map was actually an add-in for the 2013 version but is now a fully integrated option. This chart is perfect for analyzing data with a global span (e.g. a company sales report where the company operates with a global client base). Obviously you need locational data to use this type of chart and you can then add other fields to build your picture. The chart will give you a map of the world with bars or columns in the locations that have fields attached. What's more, if you set this against a particular period of time the chart will allow you to record a simulation video showing the change in values over that period. It's great for presenting sales growth to the board or your team.
What Do You Know About Excel Formulas
9. PowerPivot
PowerPivot is still technically an add-in, and not new to Excel, but it's a great tool to use to bring data into Excel. In fact it allows you to import greater levels of data (we're talking hundreds of millions of fields here). PowerPivot even has its own function language, Data Analysis Expression or DAX, and this is where the new features lie. If you feel like you've exhausted all of Excel's functions, have a go with PowerPivots.
10. Get & Transform
Another permanent fixture that started life as the Power Query add-in, Get & Transform helps you import data from various data sources. There are still the standard options of importing from a Comma Separated Values (CSV) file or an Excel workbook but now you can also choose options such as Facebook, Salesforce and other programs and packages. Once you've imported your data it will give you the chance to tidy the data into a useable format. You can also apply the Get & Transform tools to data already in Excel.
To accelerate your Excel 2016 productivity, take a hands-on course from Global Knowledge. Our introductory and intermediate courses are personalized by version, allowing you to choose the one you will use. Our advanced and Power BI courses are specific to version 2016, allowing you to fully exploit its exciting new features.
Related webinars
Everything To Know About Excel
Advanced Functions of Excel Formulas: Part 1
Advanced Functions of Excel Formulas: Part 2
Related courses
Microsoft Excel- Level 1/ Intro (2010, 2013, 2016)
Microsoft Excel - Level 2 / Med (2010, 2013, 2016)
Microsoft Excel 2016 - Level 3 / Advanced
Microsoft Excel 2016 - Data Analysis with Pivot Tables
Power Pivot and Power Query for Excel 2016
About the author
Important Things To Know In Excel
Denis has been a Microsoft Certified Trainer for over 10 years, teaching Office courses such as Word, Excel, PowerPoint, Access, Outlook and OneNote, as well as Visual Basic programming. He rounds out his application training skills with experience in team building, conflict resolution, time management, and proposal and report writing.
Subscribe
Never miss another article. Sign up for our newsletter.
