Posts

Excel VLOOKUP function, in its basic form, can look for one lookup value and return the corresponding value from the specified row. But often there is a need to use the Excel VLOOKUP with multiple criteria. How to Use VLOOKUP with Multiple Criteria Suppose you have a data with students name, exam type, and the Math score (as shown below): Using the VLOOKUP function to get the Math score for each student for respective exam levels could be a challenge....

Today there is no excel tip in this message. Just a plain and simple THANK YOU!! It is you who makes this initiative special, and I hope what I do makes you better and more successful in your professional and personal work. I want to take this opportunity to share what we have achieved so far. Here are some key stats, and given that Trump Excel is only one year old, these look pretty staggering to me (especially as I never thought that the response would be so awesome)...

And if you want to automate your work in Excel using VBA, you need to know how to work with cells and ranges using VBA. There are a lot of different things you can do with ranges in VBA (such as select, copy, move, edit, etc.). So to cover this topic, I will break this tutorial into sections and show you how to work with cells and ranges in Excel VBA using examples....

If you work with data and create good looking client-ready reports, it will pay off to learn some Excel formatting shortcuts that you can use regularly and save time. In this tutorial, I am sharing my top five Excel formatting shortcuts that I almost every day. Watch Video – Excel Formatting Shortcuts If you prefer reading instead of the video, here you go! 5 Powerful Excel Formatting Shortcuts I often get a lot of data download from colleagues or files from databases that have no formatting to it look raw and ugly....

Excel Data Filter feature gives you many ways to filter the data – based on text, value or date. Suppose you have the data as shown below: To activate Excel data filter for this data, select the entire data and click on the Filter button in the Data tab in Excel ribbon (keyboard shortcut – Control + Shift + L) Once you apply filter to the data, you will see a filter icon in each of the header cells of your data When you click on this filter icon, you can access all the filter and sorting options available for that column of the data....

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

Note: When using the completed workbook, you will need to change the folder reference in the query to wherever you save the sample files. From the Query Editor, go to View > Advanced Editor and change the folder path. If you’ve ever come across a situation where you’ve had multiple files of data with each file having data spread across multiple sheets then you’ll want to read on. This post is going to explore how to use the From Folder Power Query to import multiple files with multiple sheets in each file and aggregate the data into one table....

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

For this you’re going to need to be familiar with what we did in the previous post as this post takes off where that one left off. So have a read of this first. How To Import All Files In A Folder With Power Query Step 1: Create a query to get the named range. Firstly create a named range to reference. I have called my named range FilePath and copied in a new folder location....

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

Get The Completed Workbook You could do this in a number of different ways but these are the two that make the most sense given a column of data comprised of small blocks of related data like in the example. In this example every three rows of the column relate to one person. Option 1 To create a table where each column contains related data we can use this formula....

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 column is a vertical group of cells in your spreadsheet. Columns help you to organize your data and a column will usually contain the same type of data across all the cells in the column. Inserting columns for new data is a very common task in Excel. Columns can be used to add extra information to your spreadsheet with data entry or calculations. Inserting columns is easy to do in Excel and there are many ways to get it done....