Excel Tutorials

The XLOOKUP function is a relatively new addition to Excel and replaces the VLOOKUP and HLOOKUP functions, although you can still use those. There are extra features as well as being easier to use and simpler to understand. It gives you an option for what to do if there is no match found and unlock the other lookup functions mentioned it can look to the left of the array or above it which was not possible before. Gary Schwartz from http://www.jargonfreehelp.com shows us how. Other tutorials mentioned in this video: Absolute and Relative Referencing (using the $ signs): https://youtu.be/Q5K5u4085yk

You may be familiar with the SUM function for totaling up a set of figures but there are some other similar functions such as AVERAGE for working out, you guessed it, the average. Here Gary Schwartz from http://www.jargonfreehelp.com shows us how. Other related functions: AVERAGEIF: https://youtu.be/_1R1vRGe4Tk

What if you want to work out the average on some figures but you only want to calculate it based on certain criteria. For that you need the AVERAGEIF() function. Here Gary Schwartz from http://www.jargonfreehelp.com shows us how. Related functions: AVERAGE: https://youtu.be/Sch7BqkPruQ This video, like many on my channel, has been created using Camtasia https://techsmith.z6rjha.net/zaqjq0 For more tutorials see my website http://www.jargonfreehelp.com I am an Amazon an Techsmith affiliate and might earn when purchasing from any of these links. However, this does keep my free tutorials to you going.

A great way to organise your worksheets in Excel is to colour code the tab of the worksheet, this can help you quickly identify particular sheets and make working that much quicker. It helps too if you are sharing and collaborating on an Excel workbook. Here Gary Schwartz from http://www.jargonfreehelp.com shows us how.

If you have built up a list of data in Excel there may be a chance that there are duplicates of the same entry or perhaps someone has given you a list that you know has duplicates. Thankfully removing duplicates in an Excel list is straightforward. In more recent versions this is very easy, in older versions it is still easy but done differently. Here Gary Schwartz from http://www.jargonfreehelp.com shows us how.

You may have come across the VLOOKUP function in Excel, it is a very useful function and one of the most used in Excel. There is also HLOOKUP which is similar but is used when you have your data in columns rather than rows. The HLOOKUP function takes a value and looks for it in the first row of an array and then returns the corresponding value from another specified row. Here Gary Schwartz from http://www.jargonfreehelp.com shows us how. Related videos: VLOOKUP tutorial: http://youtu.be/oeQ_It-v3to Combining HLOOKUP and VLOOKUP functions https://youtu.be/NU8GM6Etn9I

Excel has some useful database functions that allow you to set criteria so that you can filter information in a list or database so that you can sum, average, and get highest and lowest values. These are done using the functions DSUM, DAVERAGE, DMAX and DMIN. Gary Schwartz from http://www.jargonfreehelp.com shows us how. This video, like many on my channel, has been created using Camtasia https://techsmith.z6rjha.net/zaqjq0 For more tutorials see my website http://www.jargonfreehelp.com I am an Amazon an Techsmith affiliate and might earn when purchasing from any of these links. However, this does keep my free tutorials to you going.

If you need to make a copy of a worksheet in Excel then here are two easy ways to do it. This works for both the Windows and Mac versions of Excel. Gary Schwartz from http://www.jargonfreehelp.com shows us how.

If you want to display a worksheet in Excel you have the choice of making gridlines and headings visible or invisible. You can simply choose to not display the gridlines and/or the headings. Here Gary Schwartz from http://www.jargonfreehelp.com shows us how.

Just want to enter a series of days that are only weekdays and include the weekends then this is how you do this using the Autofill feature in Excel. If you want to know how to enter the same day and different months or years then see the link below. Here Gary Schwartz from http://www.jargonfreehelp.com shows us how. How to enter the same day but different months and years https://youtu.be/-_c61UE4WJo This video, like many on my channel, has been created using Camtasia https://techsmith.z6rjha.net/zaqjq0 For more tutorials see my website http://www.jargonfreehelp.com I am an Amazon an Techsmith affiliate and might earn when purchasing from any of these links. However, this does keep my free tutorials to you going.

Need to enter the same day bit a different month or year in Excel then this is how you can do it. It works the same on all versions including Windows and Mac. It uses the Autofill feature but you can choose different options including just filling in weekdays. Gary Schwartz from http://www.jargonfreehelp.com shows us how.

If you want to speed up processes in Excel then you'll want to know how to record macros in Excel. Related videos: Where to find the developer tab:http://youtu.be/XXJHiXXkKAE Input boxes (InoutBox): http://youtu.be/q5QygD7dvds Message boxes (MsgBox): http://youtu.be/kamx7NnsCZI Gary Schwartz from http://www.jargonfreehelp.com shows us how. This video, like many on my channel, has been created using Camtasia https://techsmith.z6rjha.net/zaqjq0 For more tutorials see my website http://www.jargonfreehelp.com I am an Amazon an Techsmith affiliate and might earn when purchasing from any of these links. However, this does keep my free tutorials to you going.

If you have Excel for Windows you might be wondering where the macros have gone. They are still there but you have to make the developer tab visible to access them. If you want to record macros you will need this available. Gary Schwartz from http://www.jargonfreehelp.com shows us how. How to record a macro in Excel: https://youtu.be/Lod7Mp-VLx0

Conditional formatting is really useful tool in Excel and you can determine formatting based on formulas and other conditions in a similar way to using the IF function. You can also get it to apply formatting to a cell other then the cell that determines the formatting. This works in all versions of Excel including Windows and Mac versions. Related tutorials: Conditional formatting Part 1: https://www.youtube.com/watch?v=lWwho3T9OCM Conditional formatting Part 2: https://www.youtube.com/watch?v=Uws-DHgDtXY AND function: https://youtu.be/3qmDlpM3NyQ OR function: https://youtu.be/0X2Dm0ojwuk Gary Schwartz from http://www.jargonfreehelp.com shows us how.

If you have ever used the fill option in Excel, you know, when you want to fill a series or copy something from a cell just by clicking on the bottom right hand corner of the cell and dragging, then you probably have had it mess up your formatting. I have found this particularly annoying when it comes to borders. However there is an option to fill without formatting so that it doesn't change the formatting you already have. Whilst I was doing this recording I took the opportunity to show you how to fill just the formatting and not change the data. Related videos: Excel Fill/Autofill: https://youtu.be/2FTxjK60gKM Excel Flash-Fill: https://youtu.be/18Tirc-VVPA Excel Fill months and years: https://youtu.be/PC5SguigBYs If you want to see more then check out my website http://www.jargonfreehelp.com

If you have ever used the fill option in Excel, you know, when you want to fill a series or copy something from a cell just by clicking on the bottom right hand corner of the cell and dragging, then you probably have had it mess up your formatting. I have found this particularly annoying when it comes to borders. However there is an option to fill without formatting so that it doesn't change the formatting you already have. Whilst I was doing this recording I took the opportunity to show you how to fill just the formatting and not change the data. Related videos: Excel Fill/Autofill: https://youtu.be/2FTxjK60gKM Excel Flash-Fill: https://youtu.be/18Tirc-VVPA Excel Fill months and years: https://youtu.be/PC5SguigBYs If you want to see more then check out my website http://www.jargonfreehelp.com

If you have used the Fill feature in Excel also known as AutoFill you will know you can fill a series of dates with a click and drag of the mouse. You can also fill just weekdays and in this video show you how to fill a series increasing by month or year. Related videos: Getting started with Autofill: https://youtu.be/2FTxjK60gKM Fill Weekdays Using Autofill: https://youtu.be/sVAAWa5pPKw If you want to see more then check out my website http://www.jargonfreehelp.com This video, like many on my channel, has been created using Camtasia https://techsmith.z6rjha.net/zaqjq0 For more tutorials see my website http://www.jargonfreehelp.com I am an Amazon an Techsmith affiliate and might earn when purchasing from any of these links. However, this does keep my free tutorials to you going.

If you have used the Fill feature in Excel also known as AutoFill you will know you can fill a series of dates with a click and drag of the mouse. You can also fill just weekdays and in this video show you how to fill a series increasing by month or year. Related videos: Getting started with Autofill: https://youtu.be/2FTxjK60gKM Fill Weekdays Using Autofill: https://youtu.be/sVAAWa5pPKw If you want to see more then check out my website http://www.jargonfreehelp.com This video, like many on my channel, has been created using Camtasia https://techsmith.z6rjha.net/zaqjq0 For more tutorials see my website http://www.jargonfreehelp.com I am an Amazon an Techsmith affiliate and might earn when purchasing from any of these links. However, this does keep my free tutorials to you going.

The IF function is one of the most popular functions in Microsoft Excel and one of my favourites. It allows you create formulas that either return some text like a pass or fail or it can be used to calculate values based on certain criteria. The way the function works is the same for both Windows and Mac, how the insert function appears is different, I have the chapters below so you can jump to how you insert in on the Mac, however, using the function is covered before that, don't let that distract you. The IF function can be nested which means you can have an IF function within another IF function if you have multiple conditions to test. It can do up to seven of them, if you need more then check out my tutorial on custom functions in Excel (currently being recorded, link will appear here in a few days, stay tuned). Chapters Start: 0:00 Calculations using IF Function: 5:41 Nesting IF Function: 7:15 Insert Function on a Mac: 10:30 If you want to see more then check out my website http://www.jargonfreehelp.com

Excel has lots and lots of functions but you might find that there is not one that suits your needs. You may also have a formula you are creating using an IF function but it is either looking a bit difficult to do in the formula bar or you have run out of using the IF function, there are a limit of up to seven IFs that you can nest. A good solution is to use a custom function, these allow you to create a function, add arguments and have as many conditions as you like. You can also create your own calculation based on your needs. You can then share the workbook so that others can use it on their workbooks and you can use it across many workbooks too rather than having to copy the formula or functions across. If you want to have the functions, or macros, open up so they are always available you can do this using the Addin feature. To see how to do that check out this video https://youtu.be/MB5p7MAfQTQ Chapters 0:00 Start 1:00 Enabling content 3:28 Displaying the Developer tab (Windows version) 3:53 Displaying the Developer tab (Mac version) 10:16 Sample 2, calculating commission 14:27 Saving as a macro enabled workbook If you want to see more then check out my website http://www.jargonfreehelp.com

You might want to use add-ins if you have created your own macro or custom function and you want it to always be available when you open up Excel. Or maybe it is from someone or somewhere else that you have got them from. By using Add-ins you can make this and other tools available whenever you open Excel. This can be very useful if you are creating your own functions and macros and you want them available to your team, throughout your company, collaborators or just your friends. If you want to know how to create a custom function check out this video https://youtu.be/-i_xEVQ5l6Y If you want to see more then check out my website http://www.jargonfreehelp.com

This is one of those things in Excel that's almost magic. You basically teach Flash-fill to sense a pattern and it does the rest for you. So if you need to split text like names into first and last name or something else even if tricky this is for you. You'll see what I mean in this short video. I used to have to do this kind of thing with multiple functions, macros and VBA (Visual Basic for Applications) which was time consuming. This works it out for you. If you want to see more check out my channel or website http://www.jargonfreehelp.com

Microsoft Excel is great for creating lists and sometimes you might have duplicates, perhaps someone else has entered some in or you have imported them from somewhere else. If you want to tidy up your data and remove the duplicated then it is really easy to do in Excel. Here in this video I show you how easy and powerful this feature is. If you want to see more of my videos check out my channel or website http://www.jargonfreehelp.com

If you have data spread out over more than one workbook or worksheet you might want to combine it into one. This could be from different people entering data into different spreadsheets or from surveys. Excel has a useful feature called consolidate data that easily allows you to gather this information into one place. Related videos: How to remove duplicates in Excel: https://youtu.be/wo5M_82mBkg How to use Flash-fill in Excel: https://youtu.be/18Tirc-VVPA If you want to see more tutorials on Excel or other programs then check out my YouTube channel or visit my website http://www.jargonfreehelp.com

If you have been using Excel you have probably come across the MAX and MIN functions for finding the highest or lowest values in a range. But what if you want to find the second largest or third and so on, or what if you want to find the second smallest and so on. Well for that you have the LARGE and SMALL function. Here in this video I show you how to use both of these and a quick reminder of how MAX and MIN work. If you want to see more then check out my website http://www.jargonfreehelp.com

If you need to find the highest or lowest numbers in a range or ranges then you can use these two popular functions in Excel, MAX and MIN. They are really easy to use and in this video I show you how. There are some other useful functions that are similar for finding the second largest or small, or perhaps the 3rd, 4th, 5th....largest or smallest. They are the LARGE and SMALL functions and the link is below. I also mentioned the IF function in this video and that links is also below. Related videos: Excel: Using the LARGE and SMALL function: https://youtu.be/D2mjt82ppio Excel: Using the IF function: https://youtu.be/8OC8bDJFdDs If you want to see more then check out my website http://www.jargonfreehelp.com

If you have used the filter in Excel you know you can filter by date, number values and text. But you can also filter by colour. That is the highlight or background colour or the font colour. This is great if you have highlighted something in your list and you just want to see those, or perhaps you don't want to see them. In this video I show you just how easy it is. This works on both the Windows and Mac version. This video, like many on my channel, has been created using Camtasia https://techsmith.z6rjha.net/zaqjq0 For more tutorials see my website http://www.jargonfreehelp.com I am an Amazon an Techsmith affiliate and might earn when purchasing from any of these links. However, this does keep my free tutorials to you going.

Excel has a built-in feature that allows you to look up currency rates and other information such as % change, high, low and more. You can refresh to get the latest data. However, you need to be aware this may not be the same as websites or financial institutions and may also not be up to date. But you might find it useful. Please do not use this for doing up to the minute trades. What I have used it for is to get an idea of what something should be if purchased from the USA, not including taxes. I use this to see if it might be worth purchasing from overseas. It does use standard three letter currency codes, here is a link to the IBAN website should you need to find a code. Currency Codes: https://www.iban.com/currency-codes Tutorials mentioned: XLOOKUP tutorial: https://youtu.be/AB4ZwQ41lEU Absolute and Relative referencing tutorial: https://youtu.be/Q5K5u4085yk This video, like many on my channel, has been created using Camtasia https://techsmith.z6rjha.net/zaqjq0 For more tutorials see my website http://www.jargonfreehelp.com I am an Amazon an Techsmith affiliate and might earn when purchasing from any of these links. However, this does keep my free tutorials to you going.

If you have created a chart in Excel you might want to use it in a PowerPoint presentation or in a Word document. The great thing is that you can copy and paste your chart into either of these and if you change the data in Excel then it will automatically update in either of these, or in fact both if you have used the same chart in Word and PowerPoint. Here in this video I show you how. I have another video that shows you how to turn the chart into a picture this can then also be used in either of these but will not update, but you get the same formatting options you have for other pictures you have inserted such as glow, reflection, 3D etc. You can also use the picture in an email, social media or on a website. Here is the link to it https://youtu.be/rDqgo4obvWc This video, like many on my channel, has been created using Camtasia https://techsmith.z6rjha.net/zaqjq0 For more tutorials see my website http://www.jargonfreehelp.com I am an Amazon an Techsmith affiliate and might earn when purchasing from any of these links. However, this does keep my free tutorials to you going.

If you have created a chart in Excel you might want to save it as a picture or just have it in your worksheet, Word document or PowerPoint document as a picture. You can also save it so you can email it, use in social media, save on your website or use anywhere else you can add a picture like into a video. The thing about the picture is that it won't update like your chart will so if you do make a change you will have to replace it. So why have it as a picture, well you have different formatting options such as glow, 3D, reflections, bevels etc. These can look nice in your presentations. If you want to copy your chart over to Word or PowerPoint then check out this video I made earlier https://youtu.be/WpndOC3c09k This video, like many on my channel, has been created using Camtasia https://techsmith.z6rjha.net/zaqjq0 For more tutorials see my website http://www.jargonfreehelp.com I am an Amazon an Techsmith affiliate and might earn when purchasing from any of these links. However, this does keep my free tutorials to you going.