When to use Excel SUMIF Function SUMIF function can be used when you want to add the values in a range if the specified criteria is met. What it Returns It returns a number that represents the sum of all the numbers for which the specified criteria is met. Syntax =SUMIF(range, criteria, [sum_range]) Input Arguments range – the range of cells against which the criteria is evaluated. It could be numbers, text, arrays, or references that contain numbers....

Excel chart data labels are quite boring. While there is not much you can do with these, a bit of excel trickery can add some glamor to it. In this post, I will show you how to show trends in chart labels in a bar chart. Something as shown below: Suppose I have the data as shown below: If I plot 2013 data only, it won’t tell us how much the sales of a product have increased/decreased over the last year....

If you need a new data point that can be obtained by using existing data points in the Pivot Table, you don’t need to go back and add it in the source data. Instead, you can use a Pivot Table Calculated Field to do this. Download the dataset and follow along. What is a Pivot Table Calculated Field? Let’s start with a basic example of a Pivot Table. Suppose you have a dataset of retailers and you create a Pivot Table as shown below:...

For example, below I have an example where I have student’s scores and I have used conditional formatting to highlight all the scores that are above 80. If you’re interested in learning all the amazing things you can do with conditional formatting, check out this tutorial where I show some useful conditional formatting examples. Once you have set the conditional formatting rules for a cell or range of cells, you can easily copy it to other cells on the same sheet or other sheets....

This is made possible by the Workbooks.Open method, which takes the file location as the argument and opens that Excel file. You can do a lot more with the Workbooks.Open method, such as opening all the files in a given folder, opening files as read-only, opening files and then saving them with a different name, etc. In this tutorial, I will show you how to use VBA to open Excel files and all the amazing things you can do with it....

When you work with data in Excel, there is a handy feature that allows you to freeze the top row and header columns (or even lock multiple top rows or left columns). So when you scroll down, the headers are always visible. But that’s not the case when you print your reports. By default, a printed report would only have the header row at the top of the first printed page, and then rest all the other pages won’t have it....

A lot of times, we need to work with text data in Excel. It could be Names, Address, Email ids, or other kinds of text strings. Often, there is a need to combine cells in Excel that contain the text data. Your data could be in adjacent cells (rows/columns), or it could be far off in the same worksheet or even a different worksheet. How to Combine Cells in Excel In this tutorial, you’ll learn how to Combine Cells in Excel in different scenarios:...

When to use Excel TRIM Function Excel TRIM Function is best suited for situations when you need to clean the dataset by removing leading, trailing, and double spaces. What it Returns It returns the text string where all the leading, trailing, and double spaces have been removed. Syntax =TRIM(text) Input Arguments Here, ‘text’ is the text string from which you want to remove the extra spaces. This could either be entered manually (within double quotes) or can be a cell reference that contains the text string....

Once you have a GST number, you need to file your tax return every month (in India). So every month, my Charted Accountant would reach out to me asking for sales invoices so that he can file for the GST. In my case, there only a handful of invoices to be created, as I have only a few sources of income. However, since this is additional work, I wanted to get this done as quickly as possible....

In this blog post, I will show you a super way to create a dynamic target line in an Excel chart, that can help you track your performance over the months. Something as shown below: The target line is controlled by the scroll bar, and as if the target is met (or exceeded) in any of the months, it gets highlighted in green. Creating a Dynamic Target Line in Excel Bar Chart There are 3 parts to this chart: The Bar Chart I have data as shown below:...

When to use Excel HLOOKUP Function Excel HLOOKUP function is best suited for situations when you are looking for a matching data point in a row, and when the matching data point is found, you go down that column and fetch a value from a cell which is specified number of rows below the top row. What it Returns It returns the specified matching value. Syntax =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])...

A few ways of running a macro includes using the macro dialog box, assigning the macro to a button, using a shortcut, etc. Apart from these user-initiated macro executions, you can also use VBA events to run the macro. Excel VBA Events – Introduction Let me first explain what is an event in VBA. An event is an action that can trigger the execution of the specified macro. For example, when you open a new workbook, it’s an event....

A very common example of this is when you have names in two columns and you want to check if the names are exactly the same or what names are missing in one column. Comparing text in Excel is fairly easy and can be achieved with simple formulas. In this tutorial, I will show you how to compare text in Excel using simple arithmetic operators or the EXACT function. I will also cover how you can compare text in two columns to find out the missing text entries....

Notice that in the pic above, Morning is in the second row in the same cell. You may want to insert a line break in Excel when you have multiple parts of a text string that you want to show in separate lines. A good example of this could be when you have an address and you want to show each part of the address in a separate line (as shown below)....

Many Excel users often end up creating multiple Pivot tables in the same worksheet, which is not a very organized way of doing things. But thanks to a readily available in-built option in Excel, you can move your existing Pivot table from the current worksheet to a different worksheet with a few clicks. In this short tutorial, I will show you the best ways to quickly move a Pivot table in Excel....

In this article, you’ll learn all about Excel Sparklines and see some useful examples of it. What are Sparklines? Sparklines are tiny charts that reside in a cell in Excel. These charts are used to show a trend over time or the variation in the dataset. You can use these sparklines to make your bland data look better by adding this layer of visual analysis. While Sparklines are tiny charts, they have limited functionality (as compared with regular charts in Excel)....

In this tutorial, I will show you 2 methods to do this. And none of it would take more than a minute. Method 1 – Using Text to Column This is the easiest way if the data has a pattern. For example, in email id, there would always be a username, followed by ‘@’, and ends with the domain name. The trick is to extract the text before the @ sign....

A lot of times, I convert his queries into Excel tutorials on this site, as it could be helpful for my other readers as well. This is also one such tutorial. My friend called me last week with the following issue: There is address data in a column in Excel, and I want to identify/filter cells the where the address has duplicate text strings (words) in it. Here is the similar dataset in which he wanted to filter cells that have a duplicate text string in it (the ones with red arrows):...

A simple example of this could be to analyze how many students scored marks between 40 and 50 and how many got marks between 50 and 60 and so on.. Another example could be to create a frequency distribution of age of a group of people. This would help in identifying how many people fall in the 30-40 age group and how many in 40-50 age group and so on....

When to use Excel AVERAGE Function AVERAGE function can be used when you want to get the average (arithmetic mean) of the specified arguments. What it Returns It returns a number that represents the average (arithmetic mean) of the arguments. Syntax =AVERAGE(number1, [number2], …) Input Arguments number1 – the first number, cell reference, or range for which you want the average. [number2] – (Optional) the second number, cell reference or range for which you want the average....