Developing Excel skills is a powerful way to start analyzing data like the professionals do. There are so many compelling reasons to learn how to use Excel! Show
Whether youâre navigating toward a future career as a data analyst, you want a new skill to leverage at work, or you want to turn your household budget into an impressive well-oiled machine, learning how to analyze data in Excel with Excel formulas and tools will definitely ramp up the efficiency of your life. Here to let us in on his favorite Microsoft Excel skills is Excel professional Adam Lacey, managing director of Excel with Business. In this special guest post, he shares 19 basic, intermediate, and advanced Excel functions and formulas in Excel that you should knowâwith videos so you can follow along! If youâre wondering how to become proficient in Excel, these top Excel functions to know will put you on the right path. Take it away, Adam! Disclosure: Iâm a proud affiliate for some of the resources mentioned in this article. If you buy a product through my links on this page, I may get a small commission for referring you. Thanks! Why Excel Skills Are Important in the Job MarketOver 30 years after its creation, Microsoft Excel remains the go-to software for the majority of people looking to do some quick data analysis. Sure, there is bigger, better, more complex software available for data analysis these days, not to mention the free, web-based Google Sheets, but nothing has the broad appeal and powerful simplicity of analyzing data in Excel.
Start coding now Stop waiting and start learning! Get my 10 tips on teaching yourself how to code.
Success! Now check your email to confirm your subscription. Numbers rarely lie, which is why making data-driven decisions is so important. Companies need skilled professionals who are able to turn those raw numbers into actionable insights. Of course, it would take thousands of hours to sift through it all manually and make connections with no tools to help. Thatâs why learning how to become proficient in Excel and other data programs is the first step for those looking to enter this highly sought-after tech career. The right data analysis skillsâincluding advanced Excel functionsâcan turn raw information into sound business strategy. Excel skills can come in handy for a variety of different jobs â not just for data analysts. Like marketing, sales, HR, accounting, admin assistants, the list goes on! Regardless of your technical experience, understanding data is crucial. So whatâs one of the simplest and most accessible ways to start? By mastering basic Excel skills, then leveling up to intermediate Excel skills and advanced Excel formulas. What Can You Do With Excel Skills?There are tons of different things you can do with Excel. Itâs a super powerful tool that has a range of applications. Examples include:
The best Excel functions for your needs depend on what youâre using them for. The best Excel skills for business use might be different from the most useful Excel functions for your personal tracking needs. You donât necessarily need to know how to become an Excel expert just to make a grocery expense tracker. (Although once you start learning Excel skills, you might get addicted!) Different Types of Excel SkillsSo what exactly do we mean by Excel skills? The two most common types of Excel skills are referred to as functions and formulas. A formula in Excel is an expression that returns a specific result, such as adding, taking the average, etc. Formulas in Excel must begin with an equal sign (=). A function is a formula with a name and purpose; for example, one of the most useful Excel functions is âSUM,â which adds numbers together. 3 Basic Excel Skills to Start WithOver the last decade, weâve taught over a million students valuable spreadsheet skills, and weâve learned a thing or two ourselves. The biggest lesson is that you only need a little Excel training and a handful of Excel basic skills and formulas to start getting brilliant returns from it. So we got all the Excel experts we know, together, looked at the data we had, and came up with The Definitive 100 Most Useful Excel Tips. Here are some very basic Excel skills that will get you doing more with Excel than just listing things. Excel Skill #1: SUMThe SUM function helps you add individual values, cell references or ranges or a mix of all three. If you need to add a column or row of numbers, Excel can do the math for you. All you have to do is select a cell next to the numbers you want to sum, click AutoSum on the Home tab, press Enter, and youâre done. Excel Skill #2: COUNTYou can use the COUNT function in Excel to get the number of entries in a number field that is in a range of numbers. For example, if you want to count how many numbers are in a column, you can use a formula like A1:A20: =COUNT(A1:A20). If there are 10 numbers in that range, the result is 10. Excel Skill #3: AVERAGESimilar to the SUM function, AVERAGE in Excel gives you the average of a range of numbers. You can do this by clicking a cell below the numbers for which you want to find the average. On the Home tab, in the Editing group, click the arrow next to AutoSum, click Average, and then press Enter.
Click To Tweet Weâve picked out the top 11 intermediate Excel skills that will help you supercharge your data analysis abilities and spreadsheet skills, preparing you for a fantastic career in tech. Weâve even included a difficulty rating and how long each Excel skill takes to master, so you can pick the best Excel functions and formulas for you. Excel Skill #4: PivotTablesđ Difficulty: 3 / 5 â° Mastery: 4 hours At 4 hours to proficiency, PivotTables is one of the more time-intensive and most useful Excel functions to master, but itâs worth it. You can use them to sort, count, total, or average data stored in one large spreadsheet and display them in a new table, cut however you want. đ Where to find it: Launch a PivotTable from the âTablesâ section of the Insert tab once you have a table of data. The versatility of PivotTables is what makes this Excel knowledge so powerful. You simply drag and drop the relevant column data to create the table format you want. A PivotTable will automatically group matching data, giving you quick summaries from a giant table. For example, if you had a table full of sales data and each line was a product and an amount, in a couple of clicks you could display all the data summed and group by product (something that would take much longer just using formulas or functions). (As a warning, make sure your data is clean first!) If you are analyzing data with Excel, then PivotTables are THE most valuable Excel skill you can learn. The LinkedIn Learning course featured in the video is Excel 2016 Essential Training. Iâd recommend taking the updated version, Excel 2021 Essential Training. âïž Back to top Excel Skill #5: Flash Fillđ Difficulty: 2 / 5 â° Mastery: 30 minutes Excel developed a mind of its own in 2013, which is perfectly illustrated by this Excel professional feature. Say you have two columns of names and you need to construct email addresses from them all. With Flash Fill, you can just do it for the first row, and Excel will work out what you mean and do it for the rest (see image and video below). Pre-2013, this was possible but relied on a combination of functions (FIND, LEFT, &, etc). This way is much faster. If you learn how to use Excel tools like this, you WILL impress people even though itâs so easy! đ Where to find it: If Flash Fill is turned on (File Options Advanced) it should just start working as you type. Or, get it going manually by clicking Data > Flash Fill, or Ctrl-E. đ©âđ» Where to learn more about Flash Fill: Complete Guide â Flash Fill in MS Excel on Udemy âïž Back to top Excel Skill #6: Filtersđ Difficulty: 2 / 5 â° Mastery: 1 hour As you become proficient in Excel, youâll probably want the ability to explore data in a table quickly. Filtering effectively hides data that is not of interest to you. Usually thereâs a value, e.g. âBlue cars,â that youâre looking for, and Filters will bring up those and hide the rest. But in more modern versions of Excel, you can now also filter on number values (e.g. is greater than, top 10%, etc), and cell color. Filtering becomes an even more useful Excel function when you need to filter more than one column in combination, e.g. both colors and vehicles, to find your blue car. Alt D F F is the shortcut (easier than it soundsâgive it a go, pressing the keys in order rather than all together). Conditional Formatting and Sorting serve related purposes. Sorting involves re-arranging your spreadsheet, which is intrusive and may not be desirable. Conditional formatting brings visualization. Filtering is fast and effective. Choose well. Knowing when to use each is a key part of Excel proficiency. đ Where to find it: Apply filters by going to âSort & Filterâ in the Data tab. The LinkedIn Learning course featured in the video is Excel: Filtering Data for Beginners. âïž Back to top Excel Skill #7: Conditional Formattingđ Difficulty: 3 / 5 â° Mastery: 3 hours Conditional Formatting is a useful Excel skill that can highlight or change the color/appearance of cells given a certain condition. For example, you might want to highlight any negative numbers in red, or cells that contain a certain word. Conditional formatting makes this a breeze and makes colorful sense of data in a noisy world. Conditional Formatting can be sophisticated. But even the simplest color changes can be hugely beneficial. Suppose you have volumes sold by sales staff each month. Just three clicks can reveal the top 10% of salespeople by performance and tee up an important business conversation. đ Where to find it: On the Home tab in the âStylesâ section. đ©âđ» Want to learn more about conditional formatting in Excel? Check out this conditional formatting tutorial on Skillshare. (You can even try it for free!) âïž Back to top Excel Skill #8: COUNTIFđ Difficulty: 2 / 5 â° Mastery: 15 minutes Next on the list of Excel functions to know, COUNTIF counts cells with certain properties. For example, you want to find out how often a certain entry appears in a list. COUNTIF will look at the list and count it if it matches your chosen criteria. Itâs one of the top Excel functions, very simple to learn, and one youâll use over and over again when analyzing data. How it works: The syntax for COUNTIF is =COUNTIF(range [range of numbers you are looking at], criteria [criteria you are searching against] đ©âđ» Want to learn to use COUNTIF? Check out Excel For Beginners: VLOOKUP, INDEX, MATCH, SUMIFS, COUNTIFS on Udemy. âïž Back to top Excel Skill #9: Chartsđ Difficulty: 3 / 5 â° Mastery: 3 hours What Excel skills are employers looking for? If your role will involve any sort of data visualization, Charts will probably be one. There are more than 20 chart types in Excel. Most people get by with Bar, Column, Pie, Line, and Scatter charts. With Bar, Column, Pie, and Line charts, you just need a single series of numbers to generate a chart. With a Scatter, you need two sets of corresponding data to compare (e.g. height vs. weight). Charts are one of the most effective ways to display the data analysis youâve conducted. Words and tables tell a story, but an image tells a thousand words; thatâs why charts are one of the most useful Excel functions for data analysis. đ Where to find it: Start exploring charts from the âChartsâ section of the Insert tab. đ©âđ» Want to learn more about Excel charts? Check out this Excel Charts and Visualization course on Mammoth Interactive. âïž Back to top Excel Skill #10: SUMIFđ Difficulty: 3 / 5 â° Mastery: 15 minutes SUMIF adds cells with certain properties. Like COUNTIF, these properties include: being a certain number or word (most useful), being above/below certain values, not equaling a value (<>), etc. Just like COUNTIF, SUMIF is incredibly useful when you want to pull out summary information from large datasets. In the example image below, we are adding together the sales that match the category Cola. How it works: The syntax for SUMIF is: =SUMIF(range, [the range of cells to look at], criteria [the criteria determining which cells to add], sum range [the cells to add together] đ©âđ» Want to learn to use SUMIF? Check out Excel ForBeginners: VLOOKUP, INDEX, MATCH, SUMIFS, COUNTIFS on Udemy. âïž Back to top Excel Skill #11: IFERRORđ Difficulty: 2 / 5 â° Mastery: 30 minutes Errors (#VALUE!, #####, #DIV/0!, #REF!, etc) look ugly and can stop calculations from working (e.g. summing over a range of values with a single #DIV/0!). You can avoid it by using =IFERROR(). You can wrap any formula in IFERROR to remove those ugly error codes. For example: =IFERROR(VLOOKUP(B14,C6:D15,2,FALSE),ââ) If this above VLOOKUP was returning an error, it would now return a blank cell. In Excel functions, quotation marks will return the information inside them, so if you put nothing between them (ââ) then it returns an empty cellâgenius. đ©âđ» Learn about IFERROR in this Microsoft tutorial âïž Back to top Excel Skill #12: Slicersđ Difficulty: 2 / 5 â° Mastery: 45 minutes PivotTable slicers do the same thing as Filtersâthey enable you to show certain data and hide other data as required. But rather than dull drop-down menus, slicers offer nice big friendly buttons to make the whole user experience nicer and easier. As well as fast filtering, slicers also tell you what the current filtering state is so you know whatâs currently in and out of the PivotTable report. đ Where to find it: Add a slicer to your PivotTable from the âFiltersâ section of the Insert tab. The LinkedIn Learning course featured in the video is Excel 2013 Essential Training. Iâd recommend taking the updated version, Excel 2021 Essential Training. âïž Back to top Excel Skill #13: Power Pivotsđ Difficulty: 4 / 5 â° Mastery: 3 hours This is a powerful Excel function to know: it brings a lot more firepower to PivotTables (e.g. COUNTROWS) and more processing power to deal with much larger data sets. Power Pivot connects your PivotTables with external databases and can be refreshed on cue. For example, say you had millions of rows of dataâtoo much for Excel to handleâyou could have these saved in an Access or SQL database. Using Power Pivot, you can pull this data into Excel and then run PivotTables and Charts straight off that data. You can also create relationships between multiple tables, so if you have some data in one database and other data in another, no problem. Bring them together in Power Pivot and display your analysis using PivotTables and Charts. This is one of those Excel skills for business that is mostly for Excel experts and BI professionals. If you want to get serious about data analysis, then you need to spend some time learning Excel skills like Power Pivot. đ Where to find it: Access it from the Data tab under âData Toolsâ. đ©âđ» Where to learn more about Power Pivot: Complete Introduction to Excel Power Pivot on Udemy âïž Back to top Excel Skill #14: Sparklineđ Difficulty: 2 / 5 â° Mastery: 15 minutes A Sparkline is a tiny chart in a worksheet cell that provides a visual representation of the data selected. Sparklines are useful for showing useful Excel functions in a series of values, such as seasonal increases or decreases, economic cycles, or to highlight maximum and minimum values. Sparklines can be displayed as lines or columns and can also represent any negative values. Position a Sparkline near its data for greatest impact. đ Where to find it: Add your first Sparkline to your table from the âSparklinesâ section of the Insert tab. đ©âđ» Where to learn more about Sparkline: Sparklines tutorial video on Coursera. âïž Back to top The 14 Excel basic skills above are extremely useful, but theyâre only scratching the surface of what this powerful program can do. Looking to progress to more advanced Excel functions? Start with these 5 advanced Excel formulas and skills.
Start coding now Stop waiting and start learning! Get my 10 tips on teaching yourself how to code.
Success! Now check your email to confirm your subscription. Excel Skill #15: INDEX MATCHThis advanced Excel formula is essentially the combination of two functions in Excel. You can use it to look up a value in a big table of data and return a corresponding value in that table. This is an advanced alternative to the VLOOKUP or HLOOKUP formulas (which have several drawbacks and limitations). Itâs more flexible and therefore more powerful than VLOOKUPs. đ©âđ» Where to learn it: Index Match video on Coursera Excel Skill #16: Goal SeekGoal Seek answers âwhat ifâ scenarios. It involves changing values in cells to see how those changes will affect the outcome of formulas on the worksheet. If you know the result that you want from a formula, but are not sure what input value the formula needs to get that result, thatâs where the Goal Seek advanced Excel feature comes in. Get the course mentioned in the video below here: Learning Excel What-If Analysis via LinkedIn Learning Excel Skill #17: Macros in Excel VBAIf you have tasks in Excel that you need to do repeatedly, you can use your Excel proficiency to record a macro to automate those tasks. This helps you cut down on annoying or time-consuming busywork so you can focus on more important things! A macro is an action or a set of actions that you can run as many times as you want. When you create a macro, you are recording your mouse clicks and keystrokes. The macro can then be saved and run whenever it is needed. Course mentioned in the video: Recording and Managing Excel Macros via Pluralsight Excel Skill #18: INDIRECTINDIRECT is an advanced Excel function used to convert a text string into a valid reference. For instance, you can use it to convert a reference assembled as text (e.g. âSheet1!A1â) into a value reference. So Excel knows to find the value from the cell in A1 rather than treat it as text. đ©âđ» Where to learn it: Basic to Advance Excel Series3 on Udemy Excel Skill #19: Get External Data (from Web)Data that you want to use in Excel might not always be stored in another Excel workbook. Sometimes that data may exist externally, e.g. in an access file, in a database, or maybe on the web. This data can be imported into Excel easily using the âGet External Dataâ utility. The main benefit of connecting to external data is that you can periodically analyze it in Excel without having to repeatedly copy it, which can be time-consuming and error-prone. Learn more about the course featured in this video. âïž Back to top So, weâve covered 19 of the best Excel functionsâbut there are still so many more to learn for proficiency in Excel. If youâre interested in seeing the Microsoft Excel skills that didnât make this list, see the full 100 here! Where to Learn More About ExcelTo really learn how to get good at Excel and become an Excel expert, you need to turn to books and courses! đ„ïž Online Excel CoursesThese ten courses will teach you the best Excel functions for your needs, whether youâre just after basic Excel knowledge or you want to know how to become proficient in Excel.
đ Excel Books on AmazonNote: All the books listed below are available on Amazon. As an Amazon Associate, I earn from qualifying purchases. Frequently Asked Questions About Excel SkillsIf youâre ready to learn how to get better at Excel, thatâs excellent (pun intended)! Letâs finish up by covering a few final questions about being an Excel professional. đ§ Is Excel hard to learn?It depends on which skills youâre learning! Obviously, basic Excel skills will be faster and easier to learn than Excel advanced functions. But in general, itâs not too difficult to learn Excel functions, especially with plenty of practice. â Which Excel skills are most important?Understanding the basics as well as the most common and useful Excel functions is generally the most important, such as the 19 skills mentioned in this post. The advanced Excel skills youâll need to know will differ from employer to employer as well as the type of project youâre working on. đ How to list Excel skills on a resume?Are you learning Excel skills for business reasons? There are a few different ways to display your Excel experience on your resume. First, you can simply list âExcelâ in your skill section. You can also say âAdvanced Excelâ or âIntermediate Excelâ depending on your skill level. Another great way to list Excel experience on your resume is to include it in your work history section. Explain how you specifically used Excel in a previous role or project and what impact it had (e.g. did you save the company time or money?). It can be helpful for employers to see how you used Excel in context to show, rather than just tell them that you know Excel. đ©âđŒ What Excel skills are employers looking for?It depends on the employer and role. Skills like PivotTables, VLOOKUP, basic macros, data validation, and graphs/charts are common across a lot of roles. If youâre unsure, try looking up a job youâre interested in and see if the job description lists out any specific Excel skills youâll need to know. đ How to improve/practice your Excel skills?There are lots of ways to practice and improve your Excel skills. Try taking a course or tutorial to learn how to use Excel. Then, start working on a project youâre interested in to see if you can apply the skill to your specific use case. Youâll learn a lot by simply doing. Once you start mastering the power of spreadsheets skills, youâll wonder how you ever got through life without them. If you want to work in a data-related career, getting proficient in Excel is the perfect first step! And if youâre still keen to learn more data analysis skills after Excel? There are plenty of courses that teach the ins and outs of data, like the courses from DataCamp. About the Author Adam Lacey is Managing Director of Excel with Business, an online training company that has helped improve Excel and Microsoft Office skills for over 1 million people. |