Excel Tutorials

We all want to save time and by adding buttons that run a macro can really help in Excel. Here I am adding a button and then quickly recording the steps I want it to remember. The key thing here is adding the button. Here are some other related videos that you might find useful: Adding a background to an Excel worksheet: https://youtu.be/VsP5JQCPfYA?si=oXcY2-zavdJRwzRl Customising the ribbon: https://youtu.be/sSCHJxWZ4tU?si=0lMcgJVRMVa41o-e How to record a macro in Excel for both Windows and Mac versions: https://youtu.be/T3hpxNTB6_c?si=Duxx0lUurw0GTO14 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 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.

You can enhance the appearance of your Excel sheet by adding a background. This means you can type over an image so it is not in the way. This could be nice as a home sheet with a title or maybe you want the logo of your company on the background of al of your sheets. In this video I show you how to do this. Related videos: Add a button and macro to worksheet: https://youtu.be/1zvHoorFmJw 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 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 been using Excel you will have noticed that if you have some text that is too big for a cell that if you type something into the next cell it crops it. You haven't lost anything it is just the cell is too small. You can adjust the column width by clicking and dragging the column header. But the easy way to do this is autofit the column size and it is so easy. Here is this video I show you how to do this and also for multiple columns even if not next to each other. I also show you how to do this with row heights. This works the same on both the Windows and Mac version. Another useful video is to use Word Wrap if you want to have text on multiple lines in a cell. Here is the video for that https://youtu.be/VDOFKlr7MEc?si=S5adiU2F_2AeiFa4 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 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 extract a bit of text from a cell then you might find the TEXTBEFORE or TEXTAFTER function useful. It finds the text before or after a delimiter which can be anything but often a comma, space, hyphen, semi-colon or colon. If there is more than one delimiter you can specify which instance to use such as first, second or so on. Here are tutorials for some other recently added TEXT functions: TEXTSPLIT: https://youtu.be/-KxjK_PWBUs TEXTJOIN: https://youtu.be/2EzN9kbG6tE 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 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 want to join text together in Excel you could use concatenation or use the & sign to do the same thing. Now there is a new function called TEXTJOIN that can do this more easily and has some other functions. It also allows you to select a range to join them together: Other recently added text functions include: TEXTSPLIT: https://youtu.be/-KxjK_PWBUs TEXTBEFORE and TEXTAFTER: https://youtu.be/bDGreKqDYGA 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 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.

There are a number of ways to split text from a cell into multiple cells such as splitting someone's name into first and last name.TEXTSPLIT is the latest function to do this and it does add extra functionality. There are some other recent text functions added including: TEXTJOIN: https://youtu.be/2EzN9kbG6tE TEXTBEFORE and TEXTAFTER: https://youtu.be/bDGreKqDYGA 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 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.

One of the nice things about functions is that you fill in the blanks and it does the rest. What if you have a calculation or perhaps something with the IF function as an example and don't want to have to worry about recreating or copying the formula. Perhaps it is using different cell references and when you copy the formula you have to make adjustments. Creating your own function can make this quicker and easier. Until now you had to know VBA to create a function, and yes, that can be used across any workbook. But if you want to create your own function and use it across a workbook then you can use the LAMBDA function. Here is how it works. Other tutorials mentioned in this video: IF function: https://youtu.be/8OC8bDJFdDs 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 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 want to display numbers in a currency in Excel you can do that for probably any currency in the world. By default if you choose a currency it will display the currency of the country you have set up on your computer. However, you might want to display it in another currency or perhaps have multiple currencies. This is how you do it. If you want to see how to format in percentages check out this video https://youtu.be/1B6XQ8W5UH8 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 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 some numbers as a decimal and want to display them as a percentage then here is how you do it and also how to put it back to a normal number if you don't want it as a percentage. There is also a really quick tip on how to format if you are typing in a number you want as a percentage. 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 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.

We call know Excel is good for totalling up number but what if you to total up some times, say something on a timesheet or perhaps a bunch of durations of video clips. Well you can and it is really about entering the times in the correct format which is in hours, minutes and seconds. I show you how in this video. 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 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 want to manage a project in Microsoft Excel then it is nice to have a Gantt chart so you can visually see the timeline. What some people do is block out some cells and add a colour to represent the timeline, however, this doesn't update when you change dates or durations. But by using a stacked bar chart you can have it change as you change the date. Here in this video I show you how it works. If you are finding Excel is not doing what you really need and need something with more power then you should consider MS Project which is designed for this kind of thing. 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 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 Excel Fill weekdays: https://youtu.be/sVAAWa5pPKw 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 text that needs to be converted to a number, it could be because you imported the data, then here are two ways you can convert them. One takes advantage of the copying and pasting, and the other uses a function. In this video I show you how to do both of these. Related videos: Excel: How to convert a number to text using the TEXT() function: https://youtu.be/BrlOffTZLBw 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 some data in Excel and it is in a number format you might want to convert it so that it is in a text format. This can happen if you import data such as a telephone number. If it has imported as a number it might drop digits such as the leading zero. You can fix this using the TEXT() function. It also allows you to format it so you can add in characters such as zeros, brackets, hyphens etc. Related videos: Excel: How to convert text to a number: https://youtu.be/18PfKQ_QDjU 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.

You probably know about the AVERAGE function in Excel and you might have used the AVERAGEIF function, links to tutorials for both of those below. AVERAGE clearly gives you the average of a set of values you have selected. AVERAGEIF allows you to find the average based on a condition such as the average of sales in a given year. This is useful for one set of criteria. Well, what if you have more than one set of criteria and the criteria is in more than one range. That is where the AVERAGEIFS function comes in. This works in a similar way to SUMIFS and COUNTIFS. I have videos I've made for both of those two and they are also listed below. Related videos: AVERAGE function: https://youtu.be/Sch7BqkPruQ AVERAGEIF function: https://youtu.be/_1R1vRGe4Tk SUMIFS: https://youtu.be/4oFxgWsxAuo COUNTIFS: https://youtu.be/kDHR4-hbcME 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.

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.

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 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.

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 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 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

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

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

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

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

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

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.

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