10 Intermediate Excel Programming Skills to Advance Your Career


Can improving your Excel skills help to advance your career? Microsoft Excel is the most used spreadsheet application on the market today.  If you work in business, or in an office, chances are that you work with Microsoft Excel on a daily basis.  Knowing these 10 Intermediate Excel Programming Skills will set you apart from your competition.  In addition to these skills, we strongly recommend that you learn the new Dynamic Array Functions as they greatly simply Excel programming.   They will also help you to advance your career and your pay.

Here we teach you all that you need to know to take that first step, toward advancing your career.  Starting with just a few hours a week, learning advanced Excel features will quickly get you on your way to becoming the office Excel Guru. From there you can work your way to becoming an intermediate Excel programmer. Based on the 10 skills listed in this post.  All you have to do is Learn, Practice, and Apply.


If your current Level of Microsoft Excel Skills is Beginner:

Scenario A: At work, you mostly use the Excel workbooks as provided to you by your employer, as is, without ever making changes to them.  But that is often the case for general office positions, ones that don’t demand advanced Excel programming skills, but rather basic Excel “user” skills.

There is a huge difference between being an Excel user and an Excel programmer. Typically, beginner Excel skills are more common in these clerical-type positions, usually data entry-type jobs.  Jobs that don’t require writing formulas or creating charts.

Scenario B: Perhaps a large part of your next job, the job you want to get, is to make changes to existing Excel applications and to make new Excel workbooks for your new employer. If that is the case, then you need intermediate to advanced Excel programming skills to do so.

But don’t fret, with this guide, and your hard work, you will get there, and before you know it, your new Excel programming skills will get you the new higher-paying position you are seeking.


If the latter is the case (Scenario B), then you would need to know some very important features of Microsoft Excel, to take your Excel skills to the intermediate skills level, in order to get that new job.


This article will teach you what you need to know in order to advance your Excel skills to the intermediate level, QUICKLY, and if you need help, contact us as we are here to help.


Toll-Free: 877.392.3539Irvine, California: 949.612.3366Manhattan, New York: 646.205.3261




How to Advance your Career based on your Excel Programming Skills

If you want to advance your career, one of the fastest ways to do so is to become very good working with Microsoft Excel.  You don’t have to get so good in Excel that you become an expert Excel programmer, that is a career choice, but it is accepted that most employers are looking for quality applicants, applicants that are at the intermediate level in Excel, when they make their hiring choice.


Advance your Excel programming skills, and be the one that gets hired, let us help.


How do you get there?  Simple, start learning more about Microsoft Excel programming, first learn the basics, then once you are comfortable using simple formulas such as Sum, Count, Concatenate and such, switch your focus to building your intermediate programming skills in Microsoft Excel.

Intermediate skills would include Pivot Tables, Pivot Charts, Dashboards, Interactive Charts, Graphs, XLookup and SumIfs Functions, macros, and more.  In this post we will tell you what you need to learn in order to advance your career.  You can do so strictly based on your newly enhanced and now intermediate level Excel programming skills.



How will intermediate Excel programming skills help you stand out in the job market

Having intermediate Excel programming skills allows you to differentiate yourself from the competition simply because you can do more.  When you can do what others in the office cannot, you increase your value and worth to your employer and on the job market. If you do not have time to learn Excel at home, no worries, learn at work!

That is right, each day or each week, try to learn at least one new thing in Excel, and before you know it, your Excel programming skills will be at the intermediate level, right where you want to be for that next new job interview or internal promotion.

We have all heard the phrase office Excel guru in Excel?  This is the person everyone in the office goes to when they need help with Microsoft Excel. If you improve your Excel skills to the intermediate level, this will be you, others will come to you for help in Excel, and your employer will take notice.

This is the first step in getting that internal job promotion, or a new job with a new firm.  Be the go-to Excel expert in your office.




10 things to learn in Microsoft Excel to become an Intermediate Excel Programmer:

Below are the top 10 things to learn in Microsoft Excel, if you want to increase your programming skills to the intermediate level.  You don’t necessarily need to learn all of these, maybe Power Query or macros are not of interest right now, but the rest of these, you will definitely want to learn them.

  1. Advanced Excel functions. ( XLookup, SumIf, SumIfs, CountIf, CountIfs, If, etc.)
    1. ChooseCols, VStack, Filter, Unique, SortBy, Sequence, Lambda, Let, Take, Drop, PivotBy, GroupBy, PercentOf, etc.
  2. Excel Pivot Tables, Pivot Charts, Slicers, Timeline, Interactive Charts & Graphs
  3. Dashboards
  4. Macros ( VBA )
  5. Power Query
  6. Validation Controls
  7. Conditional Formatting
  8. Advanced Filters
  9. AutoFill
  10. Remove Duplicates

Click on the links above to be taken to that section in this post.


Of the 10 intermediate skills above, Excel Functions and Formulas are the most important, that is where you should start, as Excel is a formula driven application.  Start with the XLookup and SumIf, and once you know those intermediate functions, apply them to the files you currently use at work.

Practice, practice, practice; You cannot learn this simply by reading or watching a video.  One really good way to apply the Excel skills you just learned is to replace the older functions, say the VLookup, with newer functions such as the XLookup in your current work files.  That is the quickest way to make sure that you have a complete understanding of how to program the XLookup and other functions as well.

When it comes to the Microsoft Excel VLookup and the new XLookup the end result is the same, both will return the same values, the XLookup just does it more efficiently. This is where you should start. (See the images below). These are powerful Excel functions.


Image: Using XLookup function for Training purposes.

At Excel and Access, LLC we can teach you all that you need to know about the XLookup, Vlookup and Hlookup.


The Excel XLookup and Vlookup/HLookup functions work and look differently, but the end results are basically the same, except for the fact that the XLookup can do far more than the other Lookup functions ( a lot more ):



An intermediate Excel programmer can easily switch between the Vlookup, HLookup and XLookup, and you should be able to do so as well.  The information in this post will help you to do just that, and hopefully, you are able to get your dream job.



Review the basic beginner level functions and formulas first.

If you are not yet past beginner in Excel, then you need to start there, before you try to learn the intermediate functions.  Start with the basic functions, the ones you know you will use, and once you are proficient with those, then we can switch our focus to the intermediate Excel functions.

Don’t learn all of the Excel functions, with over 475 Excel functions available, there are too many to learn, and that is a waste of time, learn what you need to use, that is all.

Why not just start with the latest and greatest Excel functions such as the XLookup and skip the beginner functions?  You need to learn to walk before you can run; you want to learn the VLookup and the HLookup before you learn the XLookup, but let’s not get ahead of ourselves.

First you need to learn the basic functions, the ones you will see in most of the Excel files you open, Sum, Count, If, Mid, Network Days, Concatenate, etc. Those are the beginner level functions.  Once you know those, then we will take you to the next level with the amazing XLookup, SumIfs, CountIfs, Offset, etc.


Image of Google Search on "How many Excel formulas are there".

With over 475 Excel functions available to use, how do you know which ones work best for your particular need? Call us and we will help you with that, and with all of your Excel and Access needs. We are Excel and Access, LLC Corporation, and this is what we do.


There are over 475 functions in Excel, and Microsoft creates new ones all the time.  How many do you really need to know?  Maybe 20-30?  Depends on what you do in Excel at work.  How many of those 20-30 functions that you need to know are intermediate functions?  Maybe 10-15 max.

Our Advice: Make sure you know the basic functions before you move on to the intermediate Excel functions.



10 Intermediate Excel Programming Skills Companies are Hiring


1. Advanced Excel Functions – The Road to Intermediate Excel Skills Starts Here

If you program Excel applications, this is possibly the most important aspect of Microsoft Excel that you need to know. The advanced Excel functions separate the men from the boys; knowing these functions does make you stand out.  This is what will ultimately get a job at the intermediate skills level.  You need to be an expert with Excel functions. As such, it is the best place to start your skill enhancements.

Excel functions can be complicated, learning how to use them can be challenging, especially if you are not an Excel programmer.  But with that said, you cannot simply keep using the basic Sum, Count, Average functions, you must learn the more advanced, intermediate Excel functions.

The good news is that once you start to learn these formulas, learning new ones will be even easier.  The more you learn, the easier it gets, and the better you get.  If you get stressed out, remember, baby steps.

1A: Lookup Functions:

VLookup, HLookup, Index/Match, XLookup are used when you want to look-up one or more values from one table in one or more other tables, depending on the function used.

The Microsoft Excel VLookup used to be the most used function in Excel, but it had several limitations, and eventually Microsoft created the XLookup.  The XLookup replaces the need to use Index/Match, HLookup and the VLookup.  If you only learn one advanced function in Excel, this is the one to learn.

The VLookup/HLookup will at a value in a cell.  It will then look at table, finding the matching value, then returning the value of the designated column.  The “IfIserror” function is often used with the VLookup and the HLookup.

The XLookup replaces the VLookup and the HLookup.  The XLookup will look at a value in a cell.  It will then look at one or more tables, finding the matching item(s), then returning the value from one column, or from multiple columns ( an array ).  The XLookup function has error trapping built in, which can contain another XLookup, and so on.  This is the function you really want to know if you want to be considered to have intermediate Excel skills.

Excel Expert’s Note: You should learn how to use the VLookup before you learn to use the XLookup.  The VLookup is still used in the majority of Excel files out there, and it is more likely that you will come across the older lookups before you do the newer XLookup.  So knowing both is the smart way to go, especially for backward compatibility as Excel 2019 or older does not support the XLookup.

1B: Conditional Sum/Count/Average Functions:

These are POWERFUL, really powerful and super easy to use.  This is our second favorite set of functions; we use these daily.

If you work with budgets, forecasts, etc, these are the best Excel functions for you.


Example: Let’s say you have 100,000 rows of expense data, going back 10-years, by month and year.  Your boss asked you to tell him what the total expenses by month and year are, by Expense Category.  How long would it take you to figure that out?  If you use the SumIfs Function in Excel, you can tell him within 1-minute, 5-miuntes if you have not had your coffee.  It is that quick and easy to do, and you can see its power. Point the formulas to the data range and the function does the rest.

The difference between these functions (SumIf, SumIfs, CountIf, CountIfs) is if they are for single criteria or multiple criteria, else they work the same way.


1C: The SumIf/SumIfs Function – Intermediate Excel Functions

You could not work as a Financial Analyst, Finance Manager, or Director of Accounting, without knowing how to use the SumIfs Function, period.  It is used that much, it is that powerful.  This function gets the job done.

If you want to show a potential employer that you are worth your salt, know this function, it is super easy to use, and super hard to break.


Image of SumIfs Function Arguments in Excel.

SumIfs Function allows you to conditionally sum, with multiple criteria. Call us if you need help 877-392-3539.



1D: The CountIf/CountIfs Function

Basically, the same as the SumIf and SumIfs functions, except this counts records, based on criteria. If you are working on budgets, you will use this.  Not as much as the SumIfs functions, but you will use this. Not complicated, super easy to use.


Excel and Access, LLC logoExcel Programmers Special Note on the power of these intermediate Excel functions:

If you give me a detailed data table, and if I am allowed to add helper columns, using the XLookup with SumIfs will allow me to get all of the information I need out of that table. That is how powerful these intermediate Excel functions are.

There are other intermediate functions you will want to learn, but you will most likely not use the others as much as you use these.  The other functions are more to support these functions, such as using the IfIserror Function with the VLookup Function. 

Start with these, learn them, practice them, use them, and then see what functions will help you with other intermediate areas of Excel programming, such as Offset or Network Days.

If you are able to do this, you are well on your way to obtaining the intermediate Excel skills employers are looking for. Let us know if you need help.






2. Pivots – If you learn nothing else on this guide, Learn Pivot Tables and Pivot Charts

Pivot Tables are in intermediate skill, but if you really learn how to create the most advanced Pivot Tables, you will be at the advanced to expert skills set, these are that much in demand, and few use them well.

Opportunity to standout of the pack. Sr Management loves KPI Dashboards built on Pivot Tables and Pivot Charts, with Slicers. Great way to make connections with those with window offices.


Microsoft Excel Pivot Tables are the main tool in Excel to summarize large datasets.  Pivot Charts allow you to literally visualize the data, as it is a chart.  If you add Slicers you make filtering even easier.

For KPI reports, Pivot Charts and Pivot Tables are the way to go.  Senior Managment loves Pivot Tables, learn to use them, they might be your next boss.


Image of Excel Pivot Table.

At Excel and Access, LLC we believe Pivot Tables and Pivot Charts are as important as being able to use advanced functions. Learn to master them, obtain Microsoft Excel programming services, then master your career.


2A: Pivot Tables – Data Visualization at the Intermediate to advanced Skills Level:

For many, Pivot Tables are the diving line in Excel, much like Index/Match used to be, if you knew these you were instantly considered an expert Excel programmer. If you want to get that next job, learn these and learn them well.

Pivot Tables, Pivot Charts, Slicers, and TimeLine all work together as one.

A Pivot Table without a Pivot Chart is a missed opportunity to instantly see what the data is telling you.  Pivot, Filter, Drill-Down, understand with Pivot Charts. So much so that 85% of the time I do not look at nor interact with the Pivot Table, rather I use the Slicers to manipulate the Pivot Charts for my visual analysis.

Excel Pivot Table Training:  If you seek training on Excel Pivot Tables, expect one to two hours on the topic.  Then anticipate spending several hours playing with and creating Pivot Tables and Pivot Charts, with Slicers.  Apply that knowledge to your work files and you will be a Pivot expert before you know it.

If you want to be considered an intermediate Excel programmer, you had better know this.


Bob Umlas, Microsoft Excel MVP since 1993, One-On-One Microsoft Excel Pivot Table Training


2B: Pivot Charts – Soon you will be creating Excel Dashboards based on Excel Pivot Tables

See Image Below: All of those Pivot items from one data table.  That is a powerful Excel KPI Dashboard built on Pivot Tables and Pivot Charts, with Slicers and the TimeLine features of Excel.  You will soon be creating powerful Dashboards of your own, with your newly acquired intermediate Excel programming skills.

The following graphic is from our Excel Tracker Demo Model we built for our blog post, Excel-Lead-Tracker.


Image of sample Excel Tracker application we developed.

Example of our Work: Microsoft Excel Lead Tracker Demo Model – Data Visualization via Pivot Tables and Pivot Charts w/ Slicers.


Pivot Charts w/ Slicers, build on Pivot Tables, allow you to quickly understand your business.  There is no better way to drill down into your KPI Report than with a custom Excel Dashboards powered by Pivots. Employers greatly value the ability to generate such applications.  The ability to do so clearly shows that you have at least intermediate Excel programming skills.




3. Excel Dashboards – KPI Reports for Senior Management

See the graphic above, that is an example of an Excel Dashboard.  Dashboards are typically built by analysts for management.  The Excel Dashboard provides a simple to use graphical user interface for those that make decisions, to drilldown into their business, to see what drives it, what is and what is not performing.  These are some of the most sought-after skills in Excel programming today. These skills will set you apart from the competition.

If you build powerful Excel dashboards, you will get noticed, you will be seen as an intermediate programmer, and this can help you to move up the ladder, or to get a new job with new firm.




4. Macros (VBA) – Excel Macro Programming is one of Excel’s Most advanced skills

Macros, a.k.a. VBA ( Visual Basic for Applications ) is the name for the code that you type in Microsoft Excel, to automate and to integrate the application.  Most people just call them macros, so we will here.  Though Excel programmers will use the term VBA programming to describe Macro programming, they are all the same thing.

Examples of Macros would be a Button on a worksheet or UserForm.  You click the button with the mouse, and then the code takes over, the code can import data, manipulate the data, create new tabs, new files, send the application to users via email, save files to the network, and more.

VBA code programmed at the intermediate skills level will allow you to automate everything the user would manually do in Excel, basically replacing them by automating them out of a job.  That is power.  Knowing how to leverage that power with your Excel VBA programming skills is money.


Image of Excel VBA Coding, an example on what Excel VBA programming looks like.

Excel VBA programming allows you to automate and to integrate your various Excel workbooks with the other Microsoft Office 365 applications. An intermediate skill, a real time saver.


On one of our Excel programming projects sad to say, we literally cost an employee their job.  We automated them out of work. We saved that client a lot of money in the process.  VBA Programming is powerful.  If you have the time, and if you want to be an expert Excel programmer, get started.




5. Power Query & Power Pivot – Microsoft Power BI programming skills will definitely set you apart

Not for everyone, but definitely a show of ability. If you are able to work with Power BI, Power Query and Power Pivot you are definitely at the intermediate skills level in Excel, if not advanced.  This is more of an expert Excel programmer type task as I know many Excel programmers that do not how to use Power Query. Great opportunity to stand out from the crowd.

Power Query, based on Excel Tables, allows you to create amazing s Power Pivot Dashboards.




6. Validation Controls – Drop-Down lists to control user data entry

Drop-Down lists, where the user must make a selection from the predefined list is a great way to maintain data integrity.

Example:  CA, Calif, and California all stand for California, but don’t allow all three options in your data, as Excel sees them as three different

Build the Validation Control on a List with a Dynamic Named Range, such as in the image below.  You will see that the “Offset” function is used, that too is an intermediate level skills function.


Image of Data Validation Control, using Offset Function.

Data Validation Controls (Drop-Down Lists) in Excel are a powerful intermediate level skill, learn to use them.





7. Conditional Formatting is a powerful Data Visualization tool

A financial wordbook without Conditional Formatting is like a Pivot Table without a Pivot Chart.

Conditional Formatting is one of the more useful, visual, intermediate skills to have.  It allows you to set your file, so cells with certain values automatically change formatting, say setting the cell shading to red, for past due accounts.  What could be more useful?

Instead of looking at each and every number, maybe out of thousands, let Conditional Formatting do the work for you.  Work smarter, not harder.


Image of Excel tab with heavy use of Conditional Formatting

Conditional Formatting can instantly show you all duplicate records, but it can also show you so much more, definitely a skill to wow others. Need help? Give us a call 877-392-3539.


Note Use 10 New Skills as a Group, not alone: You can create custom formulas within the Conditional Formatting Interface, so being very good with Excel formulas is important in many areas within the Excel program, as things are related and linked and commonly used.

You can even use macros to apply Conditional Formatting, say for example to new tabs in a new workbook as it is created, all in vba. There are usually multiple ways to do things in Excel and each of these 10 skills work well with the other skills in this post; everything is related. These 10 skills are your new tools of the trade.





8. Filters/Advanced Filters

Filtering in Excel is a very common task, many beginners use Filters to some degree.  Advanced Filters are a lot more complicated, and they take longer to learn, and they are definitely an intermediate skill.

While not as important as being able to write intermediate functions, you will want to know how to do this, as it is very useful.




9. FlashFill & Text to Columns – Two very useful intermediate Excel programming skills you want to know

FlashFill and TextToColumns are huge time savers, and they reduce your frustration as well. If you use these two built-in Excel features, you do not need to write complex Excel functions, instead Excel does the heavy lifting for you.

If you are going to be working in Excel where you have the need to manipulate string data quickly and easily, these two intermediate Excel features are worth their weight in gold.  They allow you to do things in Excel that in the past required advanced formulas, or macros.  Now you can do it with a few clicks of the mouse.

FlashFill is even faster than Concatenate, plus there is no function to write, so this is a skill you want to have.


Trump Excel did the YouTube Video below. He explains this so well..  Check it out.


Trump Excel has some very good videos on intermediate Excel skills training. Click the image to see one on YouTube. I love his work.





10. Remove Duplicates

This used to be a manual process in Excel.  People used to hire Excel consulting firms to remove duplicates for them, now that is built into Excel.  It is something you will use if you have a job that requires intermediate Excel skills, because Excel often has duplicates.  Learning this while not too difficult or advanced, it is an intermediate Excel skill, and you will definitely find yourself using it, so take the time to learn it.

Before you remove duplicates, use Conditional Formatting to highlight duplicate values (See image below).  Once you delete the duplicates, using the “Remove Duplicates” button on the “Data” Ribbon, no cells will be highlighted, but as soon as a duplicate are added, Excel will Conditionally Format the duplicate data cells, and you will instantly see it.


Image of Conditional Formatting in Excel, combined with "Remove Duplicates".

Removing Duplicates in Excel has never been easier, so no excuse not to learn to use it. Add the use of Conditional Formatting to know when a “duplicate” is entered.





Editor’s Note: With the recent release of Excel’s new Dynamic Array Spill Functions, how Excel programming gets done is quickly changing.  Look at these new Dynamic Array Functions and see how you can fit them into your Excel workbooks.  See the image below where these newer functions were used to feed a custom Excel Dashboard, no VBA needed. This is as easy as it gets to work in Excel.

  1. ChooseCols Dynamic Array Function / ChooseRows Functions.
  2. VStack / HStack Functions.
  3. Take Function.
  4. Drop Function.
  5. Filter Function.
  6. Unique Function.
  7. Sort / SortBy Functions.
  8. Other newer useful functions:
    1. TextJoin
    2. Ifs
    3. Transpose
  • Microsoft Excel is in constant motion these days, and Office 365 makes it possible for Microsoft to add ne functions and new features to Excel, as they come in, no need to wait for the next release a few years down the road. Learn how to integrate the newer Array Functions with the existing functions to get the most out of Microsoft Excel.


Image of Excel Dashboard built using new Spill Array Functions.

This custom Excel Dashboard was built using Microsoft’s newest Dynamic Array Functions, the Spill Array Functions such as VStack, ChooseCols and Filter. Those were the only formulas used to create the Dashboard, and VBA was not needed. So simple, so quick, so powerful.







What to Expect after advancing your Excel programming skills:


Expect people at work to notice your intermediate Excel programming abilities

Expect people at work to come to you for help with programming their Excel files.

Expect to become the Office Excel Guru.  When others are stuck with advanced functions, Pivot Tables, etc., they will seek you out for help.  Expect your boss and those above you to notice.  Then expect your supervisors to come to you with special Excel programing projects that they need to have done.  They will appreciate what you can do, they will value your newly found skills.

They will notice.  You will have skills that others do not.  Your worth to your employer is going up, your worth on the open market is going up. You will be noticed on LinkedIn.Com for your marketable intermediate Excel programming skills, and this might be where you find your next job.



When you look for a new job, expect to be asked to take a computerized Excel skills Test

Employers are not going to take your word for it, regarding how experienced you are in programming Microsoft Excel.  You will need to prove it.  Many potential employers will have you take a one-hour, computerized Excel skills test during the job interview process.  The test will determine what you can do in Excel, and thus what your skills level is.  If you have practiced, this is your time to shine.

We know a lot about Excel skills tests as we have been hired by several corporations to develop such tests for their internal use, when hiring.   There is no way to fake your way through these tests, so it is in your best interest to actually learn all that you need to, in order to pass the test.  Unlike Excel, there is no Undo in a job interview or on a skills test.

The best way to make sure you know the material, and that you can pass the skills test is to first learn the material, then practice, practice, practice.  If you do that, you should have no worries of passing the test.


Important note, typically the Excel skills tests are not verbal, they are computer based and they actually have you do the Excel task in question.  So you really need to know what you are doing as this is for intermediate skills, not beginner.




Jobs you might get if you have intermediate Excel programming skills:

Microsoft Excel is the application of choice in finance and in business settings.  If you work in finance, accounting, marketing, sales, or in general business, Excel is your go to application.

There is the Microsoft Excel Consulting services industry centered around manipulating, managing, and reporting on client data.  In traditional business there are dozens of jobs where Microsoft Excel is the tool of choice, and those with advanced Excel programming skills get those jobs.  All of those jobs have an Excel programming component to them, though, usually, that is not the focus of the job unless you are a professional Excel programmer.

  1. Financial Analyst
  2. Business Analyst
  3. Sales Analyst
  4. Finance Manager
  5. Accountant
  6. Auditor
  7. Business Associate
  8. Office Staff
  9. Clerical
  10. Microsoft Excel Programmer



Why is Excel used so much in a business setting

Businesses manage and manipulate large amounts of detailed data.  As such most businesses use some combination of Microsoft Access, SQL Server and Azure to house and manage their data.  But at the desktop level, people in business use Microsoft Excel to get their work done.  Many spend more of their time in Excel than in any other Microsoft application.

Most people in business do not have database programming skills, and as such, they are unable to work with Microsoft’s Four Relational Databases.  But with that said, many of those working in Excel do have some Excel programming experience, at least writing a few formulas or such.  They might not be at the intermediate Excel skills level, but they are able to do some programming in Excel.  As such, Excel is the most used desktop productivity application that Microsoft offers.  If you work in business, you usually work in Excel.

Businesses have a lot of data, so much in fact that there is an actual term for it, it is called “BIG Data”, and it is something you will need to learn to deal with. Big Data takes intermediate to advanced Excel programming skills to manipulate it properly.  Excel programming for business is a large industry and Big Data is one of the things we are hired to deal with for our clients.  Big data creates challenges, using certain functions or advanced features of excel allows you to tame the beast.


Using the incorrect formulas on Big Data can greatly slow your Excel file.


In a business or office setting, intermediate Excel programming skills allow you to quickly and efficiently access your data, to manipulate it, change it, query it, basically to make sense of it.  Excel Pivot Tables are the perfect feature of Excel to deal with large amounts of data (BIG Data), providing summary results, with the ability to drill0down into the details, that is why Senior Managment loves Pivot Tables and Pivot Charts, with Slicers of course, the perfect KPI Reporting and analysis package.



What type of Excel applications can you make with intermediate Excel programming skills:

If you have intermediate Excel programming skills, being able to do most of the things on this page, plus all of the other necessary stuff, you honestly should be able to make any type of Excel application you desire. Anything from Stock Trade Analysis workbooks to a Personal Budget. That said, there are certain types of applications that really need these specific types of skills.

  1. Excel Trackers
  2. Excel Templates
  3. Financial Reporting & Analysis workbooks
  4. Budgets, Forecasts, Actual applications
  5. Financial Modeling
  6. KPI Workbooks for Presentations
  7. Custom Dashboards
  8. Anything you want to track.



Note: To be at the intermediate skills level in Excel, knowing VBA is not required

Excel and Access, LLC company logoThere are Microsoft Excel MVPs that do not work with VBA, surprising, but there are.  Knowing how to program VBA/Macros is a definite strength, and if you are shooting for the intermediate level, then this is something that you want to master.  I am not talking about recording macros, but sure, you can start there, rather I am talking about writing Excel macros that will manipulate data, open and close workbooks, etc. This is worth knowing, it will help to separate yourself from the pack.


Benefits of Intermediate Excel Skills

  1. Get more work done, in less time.
  2. Ability to manipulate large amounts of data, efficiently.
  3. Ability to create new Excel workbooks, from scratch.
  4. Be known as the office Excel guru.
  5. Advance your career





The Difference Between Intermediate and Advanced Excel programming skills.

The intermediate Excel user will write basic XLookups.  But the advanced user will take the XLookup function and will be able to do more with it.  For instance, the advanced user will be able to have the function look across several tabs, not just one, or even across several workbooks, all with one function. Having error handling code built in saves you a function.  The advanced Excel programmer will use the XLookup in place of the VLOOKUP, HLookup and Index/match, one function now does it all, it even Offsets.

An intermediate programmer is more likely to use individual functions to get result whereas the advanced programmer will often embed a number of formulas into one function, to get their result.  The more you embed, the more complicated it becomes.   Note, you can always use “Helper Columns” if you are not yet at the advanced skills level in Excel.

To transition from beginner to intermediate, make sure that you first know the basic functions (Sum, Count, Max, etc.), then learn the intermediate functions (VLookup, XLookup, SumIfs, etc.).  To transition from intermediate to advanced, take the newly learned intermediate functions, and embed them with other functions, such as Offset or Concatenate, use Dynamic Named Ranges in your functions, learn to use the function in your VBA code.

From beginner to advanced, you can get there, and you can blossom your career at the same time. Let us know how we can help.



Image: Using the Microsoft Excel XLookup is both an intermediate function and an advanced function, depending how you use it.

Our favorite Excel Function, definitely an Intermediate skill, Microsoft Excel XLookup Function- Master it; Master your Career.






Closing: If you want to get that next new job or promotion, take your Microsoft Excel programming skills to the intermediate level

It is a competition, you versus the other candidates.  It is not just the college you went to, the degrees that you have, but also what you can do.


What you can do is what ultimately matters to your future employer.


Who best knows how to do what the employer is seeking?  The one that can do more work, in less time, at a higher quality level, that is the one most likely to get the job, all other things being similar.


Think of it like this, you can keep your Excel skills as they are, and accept the 0-3% annual pay increase, or you can dramatically advance your Excel development skills and you can get 15%+ pay increases every two years, when you accept a new job.  In less than 10-years you can greatly enhance your income and your career.  Want to make more money?  get better in Excel.


Given that all of your competition already works in Excel means that you need to learn as much as possible about Excel programming as you can, if you want to compete for the job, and at the intermediate or higher levels (Advanced and Expert).

Our advice: Don’t just take the time to learn how to do these 10 items, rather after you learn, practice practice practice.  You can practice at work and get paid for it, and it is learning on files you work in, so by making yourself better, you make the files better, and your employer benefits. Taking your beginner Excel skills to the intermediate level is a win-win.

If you want to speed up learning, we suggest watching our Excel Training Videos.





Need Help Learning any of this?


We offer Our one-on-one Intermediate Excel Training

We can teach you all the skills you need to learn to get your next job

Okay, so perhaps learning how to write an XLookup, or creating Pivot Tables and Pivot Charts is a challenge for you.  But you know you need to have intermediate Excel programming skills in order to get that next job or promotion.  So what to do?  Simple sign up for an hour of remote one-on-one Excel training and we will make sure you fully understand and that you are fully able to do Excel programming at the intermediate skills level.


One-on-one Excel training is the best way to learn how to program in Microsoft Excel. Training with an Excel MVP is the best way to learn Excel programming.


If there are others in your organization that also need to up their Excel skills, we can help all of you at once, for a reduced cost.  With group training in Microsoft Excel can train 2-30 of your employees all at once, and we can teach them all intermediate skills they need to learn, either onsite or remote.  Group training is less expensive than one-on-one Excel training as the costs are spread across multiple employees.


Example Agenda for group Excel training:

Intermediate Session Part 1 (Agenda C)

  1. Organize/Clean up data:
    1. Named Ranges
    2. Validation Controls
    3. Paste Special
    4. Text to Columns
  2. Functions:
    1. Lookup Functions (Vlookup, HLookup, XLookup)
    2. Conditional Functions (SumIf, SumIfs, CountIf, CountIfs, etc.)
    3. Concatenate
    4. If Function
    5. IfError, Left, Right, Month, etc.

 Intermediate Session Part 2 (Agenda D)

  1. Data Visualization:
    1. Filters
    2. Pivot Tables
      1. Source data range
    3. Pivot Charts
    4. Slicers
    5. Charts and Graphs
  2. Misc Topics:
    1. Conditional Formatting
    2. Hyperlinks
    3. Quick Access Toolbar
    4. Macros



Example Training Video on the Microsoft Excel VLookup, Bob Umlas Microsoft Excel MVP, 1993-current year:






An Intermediate Excel Programming Skills Checklist

  1. Advanced Excel functions. ( XLookup, SumIf, SumIfs, CountIf, CountIfs, If, etc.)
  2. Excel Pivot Tables, Pivot Charts, Slicers, Timeline, Interactive Charts & Graphs
  3. Dashboards
  4. Macros ( VBA )
  5. Power Query
  6. Validation Controls
  7. Conditional Formatting
  8. Advanced Filters
  9. AutoFill
  10. Remove Duplicates


Apply as many of these 10 Intermediate Excel programming skills to increase your chances of getting that next new high paying job.   It is not about knowing everything, it is about knowing the “right things“, it is about knowing more than your top competitor.  He who has the highest skills, all other things being equal, gets the job. Good luck, and if you need help, we are here to assist.





Are Intermediate Excel skills strong enough to help you get that next job?

Yes, they can literally be the differentiator between yourself and the next candidate.  Even if they went to a better college, if you have stronger marketable skills, you have a very good chance of getting the job.

Intermediate Excel programming skills are highly sought after, and many of your competitors do not have them.  So take charge of your career and advance your skills and get that next high paying job.


How can I make sure I am at the intermediate skill level when it comes to programming Excel?

You have the list above, which is a great starting place.  Learn the majority if these, and make sure you can quickly do them, without issues.  If you are able to apply what you have learned at work, and if you are able to assist others with these topics, then you are at the intermediate skills level, and from there you can focus on learning more, and on getting better.  Practice, practice, practice.


What Excel related skills questions or exams should you expect in the job interview?

Early in the interview process expect to be asked about your Excel skills.  They may ask you if you know how to use Pivot Tables, the XLookup, or VBA/Macros.  Saying no to any of these questions could instantly take you out of the running for the job.

Later in the interview process, plan to take a computer-based test to test your Excel programming skills.


What do you do if you cannot learn intermediate Excel programming skills on your own?

Hire us for our one-on-one Excel training with an Excel MVP.  Just one or two hours with an Excel expert should be all that you need for any of these intermediate topics. We can do this either face-to-face, or remotely, using Zoom. Just you and the instructor, their is no better way to learn excel programming.


Toll-Free: 877.392.3539Irvine, California: 949.612.3366Manhattan, New York: 646.205.3261





Toll-Free: 877.392.3539Irvine, California: 949.612.3366Manhattan, New York: 646.205.3261