Posts

Excel CONCATENATE function (or the ampersand (&) operator) Excel TEXTJOIN function (new function in Excel if you have Office 365) In its basic form, CONCATENATE function can join 2 or more characters of strings. For example: =CONCATENATE(“Good”,”Morning”) will give you the result as GoodMorning =CONCATENATE(“Good”,” “, “Morning”) will give you the result as Good Morning =CONCATENATE(A1&A2) will give you the result as GoodMorning (where A1 has the text ‘Good’ in it and A2 has the text ‘Morning’....

How to Count Unique Values in Excel Let’s say we have a data set as shown below: For the purpose of this tutorial, I will name the range A2:A10 as NAMES. Going forward we will use this named range in the formulas. In this data set, there is a repetition in the NAMES range. To get the count of unique names from this dataset (A2:A10), we can use a combination of COUNTIF and SUMPRODUCT functions as shown below: =SUMPRODUCT(1/COUNTIF(NAMES,NAMES))...

Using my substandard secondary skills, I was somehow able to find enough data points to create an awesome Excel dashboard. There are many wonderful people/forums/wikis that helped me compile this dataset (credits at the end). Here is how the final Dashboard looks like: Give me the Dashboard right away (~17 MB) Learn to Create World Class Dashboards in Excel. Join the Excel Dashboard Course. Data Points used to create this dashboard:...

Last month, one of my colleagues got a data set in Excel, and he was banging his head to clean it. Since I was the only one in the office at that wee hour, he asked me if I could help. I used a simple technique using Find and Replace in Excel, and his data was all clean and polished. He thanked me, packed up, and left office. He thanked me, packed up, and left office....

And one such example of customization is the ease with which you can add a secondary axis to Excel charts. But before I get into the mechanics of adding a secondary axis, let me take a step back and explain why it’s needed. Why Add a Secondary Axis in Excel Charts? Let me try and explain this by using an example. Suppose you have the following data set of Sales and Profit margin of a company for six years....

And for that to work, you need to assign a macro to a button first. In this tutorial, I will show you a couple of ways to insert a button in Excel and then assign a macro to that button (or shape). Once done, as soon as a user clicks on the button, the macro VBA code would be executed. For the purpose of this tutorial, I will be using the below VBA macro code (which simply selects cell A1 in the active sheet and enters the text “Good Morning” in it and colors it red)....

If you are working with Excel spreadsheets, it could mean a lot of time saving and efficiency. In this tutorial, you’ll learn how to create Named Ranges in Excel and how to use it to save time. Named Ranges in Excel – An Introduction If someone has to call me or refer to me, they will use my name (instead of saying a male is staying in so and so place with so and so height and weight)....

A histogram is a common data analysis tool in the business world. It’s a column chart that shows the frequency of the occurrence of a variable in the specified range. According to Investopedia, a Histogram is a graphical representation, similar to a bar chart in structure, that organizes a group of data points into user-specified ranges. The histogram condenses a data series into an easily interpreted visual by taking many data points and grouping them into logical ranges or bins....

A comma is commonly used when working with both text and numbers in Excel. With text, a comma can be used as a separator (such as a separator between the first and last name or address) or as a part of regular grammar. And with numbers, it often used to make the numbers more readable (a thousand separators is the most common use of comma with numbers). While comma does help make the data a lot more readable, sometimes, you may want to remove all the commas from the data (or remove specific instances of the comma)....

Once you have more than a couple of worksheets, you need to manually arrange these. How easy would it be had there been a way to quickly sort the worksheets alphabetically in Excel. While there is no inbuilt feature way to do this, it can be done (easily) using VBA. In this tutorial, I will give you the code and the exact steps you need to follow to sort worksheets in Excel....

Flash Fill is an amazing tool that was added in Excel 2013 and is available in all version after that. You can use Flash Fill in Excel to make data entry easy. It’s a smart tool that tries to identify patterns based on your data entry and does that for you. Some simple examples of using Flash Fill could be to get the first name from the full name, get name initials, format phone numbers, etc....

A few days ago, I was working with a data set that included a list of companies in Excel along with their logos. I wanted to place the logo of each company in the cell adjacent to its name and lock it in such a way that when I resize the cell, the logo should resize as well. I also wanted the logos to get filtered when I filter the name of the companies....

The ability to merge cells is one such feature. While I don’t use this option myself, I still find myself unmerging cells (mostly in the workbooks shared by other people). In this tutorial, I will show you some ways to quickly unmerge cells in Excel. While I don’t think people are going to stop merging cells anytime soon, I hope these simple ways to unmerge cells will save you some time and frustration....

Before I come to this month’s template, I have a favour to ask. If you have any ideas for Excel Templates, do share it with me (email me or leave a comment below). It will help me create stuff that is useful for you and my other readers. Also, if you are already using a template, and would like to share it with others (and get yourself featured on my blog), just email me or leave a comment below....

A proper understanding of how to copy-paste multiple cells (that are adjacent or non-adjacent) would really help you be a lot more efficient while working with Microsoft Excel. In this tutorial, I will show you different scenarios where you can copy and paste multiple cells in Excel. If you have been using Excel for some time now, I’m quite sure you would know some of these already, but there’s a good chance you’d end up learning something new....

The work was simple. There were many folders on the network drive and each folder had hundreds of files in it. I had to follow these three steps: Sounds simple right? It was – Simple and a huge waste of time. What took me three days could have been done in a few minutes if I knew the right techniques. In this tutorial, I will show you different ways to make this entire process super fast and super easy (with and without VBA)....

While there is no built-in option in Excel to convert an entire Excel file to Word, you can easily copy and paste the data from an Excel document to Word. In this tutorial, I will show you multiple ways to copy data from an Excel Worksheet into a Word document. Copy Paste Excel Table as Plain Text in MS Word If all you want is to copy and paste a table from Excel to Word, you can do that with a simple copy paste....

Below is the written tutorial, in case you prefer reading over watching the video. In Excel, there are two kinds of tick marks (✓) that you can insert – a check mark and a checkbox. And no… these are not the same. Let me explain. Check Mark Vs Check Box While a check mark and a checkbox may look somewhat similar, these two are very different in the way it can be inserted and used in Excel....

While the number in the back end would still remain the same, Excel changes the way it shows you that number. Sometimes that’s what you want, and sometimes you may want to convert the scientific notation back to regular numbers. In this short tutorial, I’m going to show you a couple of ways to convert numbers that are shown in the scientific notation back into being shown as regular numbers....

This could especially be useful in project planning, where some days could be for a specific task (such as having a project meeting or sending the progress report), or where you need to know what days the working days are and what days are the weekends. In this tutorial, I will show you a couple of ways you can use to convert dates into the day of the week and get its name in Excel....