When to use Excel CONCATENATE Function CONCATENATE function can be used when you want to join 2 or more characters or strings. What it Returns It returns the joined text. Syntax =CONCATENATE(text1, [text2], …) Input Arguments text1 – the first item to join. The item can be a text value, number, or cell reference. [text2], .. – Additional text items to join. It can handle up to 255 items, up to a total of 8,192 characters....

MVP is an award that is given by Microsoft to individuals that share the passion for its technology (Excel in my case), and who actively share their knowledge with the broader community. This could be via blogs, forums, social media, etc.. This is awarded for 12 months and is assessed every year and re-awarded if the guidelines are met. Read more about Microsoft’s MVP program. It has been 13 months since I started Trump Excel, and this is a great encouragement to keep on learning and sharing excel tips....

Calculating the percentage as a proportion of a specified value (for example, if you eat 4 out of 5 mangoes, what percentage of mangoes have you eaten). Calculating the percentage change (YoY or MoM). This is usually used in sales reporting where the manager would want to know what’s the sales growth Year on Year, or Quarter on Quarter. In this tutorial, I will show you the formula to calculate percentages in Excel as well as to format the cell so that the numbers show up as percentages (and not decimals)....

You may need to convert formulas to Values in Excel if you work with a lot of dynamic or volatile formulas. For example, if you use Excel TODAY function to insert the current date in a cell, and you leave it as a formula, it will update itself if you open the workbook the next day. If this is not what you want, then you need to convert the formula into a static value....

For example, suppose you want to highlight all the cells that have an error in it, you can easily create an Excel add-in that will highlight errors with a click of a button. Something as shown below (the macro has been added to the Quick Access Toolbar to run it with a single click): Similarly, you may want to create a custom Excel function and use it in all the Excel workbooks, instead of copy pasting the code again and again....

In this short tutorial, I will show you how to quickly remove the division error in Excel (using some easy formulas). I will also show you how you can find all the cells in a worksheet or workbook that contain the DIV errors. What is a #DIV/0! Error in Excel? In Excel, you will get a DIV error when you have a formula where there is a division and the divisor is 0....

However, when working with large complex formulas, typing or editing the formula in the cell becomes a bit hard. In such cases are usually do this in the formula bar. A formula bar is a bar below the ribbon and above the working area of the worksheet (as seen in the image below). Whenever you select a cell that has a formula, that formula is shown in the formula bar....

Here is an example of a waffle chart (shown below): In the above example, there are three waffle charts for the three KPIs. Each waffle chart is a grid of 100 boxes (10X10) where each box represents 1%. The colored boxes indicate the extent to which the goal was achieved with 100% being the overall goal. What do I like in a Waffle Chart? A waffle chart looks cool and can jazz up your dashboard....

For example, you may want to track activities such as when was a particular expense incurred, what time did the sale invoice was created, when was the data entry done in a cell, when was the report last updated, etc. Let’s get started. Keyboard Shortcut to Insert Date and Timestamp in Excel If you have to insert the date and timestamp in a few cells in Excel, doing it manually could be faster and more efficient....

For example, as shown below, there are two groups (A and B) with 5 items in each group. Each item has a score in column C. Now there are two rankings done with this data set. Column D has the overall rankings and Column E has the group-wise rankings. Now there are two rankings done with this data set. Column D has the overall rankings and Column E has the group-wise rankings....

And it could be a nightmare if, at that point in time, Microsoft Excel decides not to open (arghhhhh!). Unfortunately, this unexpected behavior is quite common where Excel refuses to open one of your important Excel files, or fails to open the Excel application altogether. So, if you are suffering from the ‘Microsoft-Excel-won’t-open’ syndrome, this article is just what you need. In this tutorial, I will cover the reasons why Excel refuses to open and the possible solutions that can help you in this situation....

What it Returns It returns a logical argument which is the reverse of the logical argument used within the NOT function. For example, =NOT(TRUE) returns FALSE and =NOT(FALSE) returns TRUE. Syntax =NOT(logical) Input Arguments logical – A value or expression that can be evaluated to TRUE or FALSE. Additional Notes: You can check expression with NOT function that evaluates to TRUE or FALSE. For example, =NOT(1+1=2) would return FALSE....

Embedding a Youtube video in Excel could be helpful if you want the user to go through some instructions in the video before using the spreadsheet/model/dashboard. I sometimes use it during excel training session with live examples. In this blog post, I will show you how you can embed youtube video in Excel. Step 1 – Modify Youtube URL Step 2 – Embed Youtube Video in Excel Caution: Do not save in compatibility mode....

This prompt tells you that there is a circular reference in your worksheet and this can lead or an incorrect calculation by the formulas. It also asks you to address this circular reference issue and sort it. In this tutorial, I will cover all that you need to know about circular reference, and well as how to find and remove circular references in Excel. So let’s get started! What is Circular Reference in Excel?...

The team that has built Excel has put in a lot of thinking behind it and they continuously work on improving the user experience. But there are still somethings that I find irritating and often need to change the default settings. And one such thing is dotted lines in the worksheet in Excel. In this tutorial, I will show you the possible reasons why these dotted lines appear and how to remove these dotted lines....

I often use scatter chart with many data points. One of the most irritating things is to spot the data point in Excel chart. Excel does not give you a way to display the names of the data points. While there are many add-ins available to do this, I will show you a super cool (without add-in) workaround to spot the data point you are looking for. Something as shown below: This technique instantly gives you a way to identify the company’s position in the scatter chart....

If you have seen Matrix, there is no way you wouldn’t remember the falling code sequence. I thought of creating this in Excel, but someone beat me to it. It has already been created and is available here. Nitin Mehta, who created this, used a couple of Excel functions, conditional formatting, and a scroll bar to create this effect. I have modified it to remove the scroll bar and have used a VBA code instead....

Just like any object in VBA, worksheets have different properties and methods associated with it that you can use while automating your work with VBA in Excel. In this tutorial, I will cover ‘Worksheets’ in detail and also show you some practical examples. So let’s get started. If you’re interested in learning VBA the easy way, check out my Online Excel VBA Training. Difference between Worksheets and Sheets in VBA In VBA, you have two collections that can be a bit confusing at times....

Something, as shown below, could then have been possible: Until the time it is not made available by the Microsoft Office team, this tip can help you link the maximum value for a scroll bar to a cell. Adjust Scroll Bar Maximum Value in Excel Note that since the file has a macro, you need to save the file in either .xls or .xlsm format. Once you are done setting this up, and IF the scroll bar is not working, follow these steps:...

Excel Advanced Filter is one of the most underrated and under-utilized features that I have come across. If you work with Excel, I am sure you have used (or at least heard about the regular excel filter). It quickly filters a data set based on selection, specified text, number or other such criteria. In this guide, I will show you some cool stuff you can do using the Excel advanced filter....