A step chart can be useful when you want to show the changes that occur at irregular intervals. For example, the price rise in milk products, petrol, tax rate, interest rates, etc. Let’s take the example of a Petrol hike in India. It can happen any day (as decided by the government) and the value remains constant between these changes. In such a case, a step chart is the right way to visualize such data points....

The best part about using a Pivot Table is that even if you don’t know anything in Excel, you can still do pretty awesome things with it with a very basic understanding of it. Let’s get started. Click here to download the sample data and follow along. What is a Pivot Table and Why Should You Care? A Pivot Table is a tool in Microsoft Excel that allows you to quickly summarize huge datasets (with a few clicks)....

Download Example File Watch Video – Using Solver in Excel One Variable Data Table in Excel. Two Variable Data Table in Excel. Scenario Manager in Excel. Goal Seek in Excel. Solver in Excel is an add-in that allows you to get an optimum solution when there are many variables and constraints. You can consider it to be an advanced version of Goal Seek. Solver add-in is disabled in Excel by default....

Suppose you have a template as shown below, and you have to enter data in excel in all the cells highlighted in red, in the order specified by the arrows. While this seems easy enough, it could be nerve-wracking when you have many such non-contiguous cells and you are burning your eyes by spotting and filing these cells one by one. Quick Way to Entering Data in Excel in a Specific Order: Select all the cells where you need to enter the data (press control and then select one by one)....

Thermometer chart in Excel could be a good way to represent data when you have the actual value and the target value. A few scenarios when where it can be used is when analyzing sales performance of regions or sales rep, or employee satisfaction ratings vs the target value. In this tutorial, I will show you the exact steps you need to follow to create a thermometer chart in Excel....

Having outliers is quite common in all kinds of data, and it’s important to identify and treat these outliers to make sure that your analysis is correct and more meaningful. In this tutorial, I’ll show you how to find outliers in Excel, and some of the techniques that I have used in my work to handle these outliers. What are Outliers and Why is it Important to Find these? An outlier is a data point that is way beyond the other data points in the data set....

In Excel, a checkbox is an interactive tool that can be used to select or deselect an option. You must have seen it in many web form available online. You can use a checkbox in Excel to create interactive checklists, dynamic charts, and dashboards. This Excel tutorial covers the following topics: How to Get the Developer Tab in Excel Ribbon. How to Insert a Checkbox in Excel. Examples of Using Checkboxes in Excel....

In huge data sets, it is practically impossible (or highly inefficient) to do this manually. Thankfully, there is a way to select blank cells in Excel in one go. Select Blank Cells in Excel Here is how you can Select blank cells in Excel: Select Visible Cells in Excel How to Select Non-adjacent cells in Excel? How to Deselect Cells in Excel How to Move Rows and Columns in Excel....

It makes it easy for a user to manage data if it is in a tabular format. Just by converting a data range into a table, you are able to use a variety of features that will make your work super easy (I cover these features in detail below in this article). Creating an Excel Table If you have the data in the tabular format in a worksheet, here are the steps to convert it into an Excel table...

This also means that most of the time, we work with cells and ranges, worksheets, workbooks, and other objects which are a part of the Excel application. But VBA is a lot more powerful and can also be used to work with stuff outside of Excel. In this tutorial, I will show you how to use VBA FileSystemObject (FSO) to work with files and folders on your system or network drives....

Excel has a Quick Access Toolbar that gives you instant access to your favorite/important options. However, not many people use the QAT efficiently. Last month I have asked 15 excel users about it, and only 7 were using it on frequent days. And, others were busy navigating Excel ribbon for their stuff. In this article, I will share my top 5 QAT options that help me stay productive and save time....

While you can do this easily from the worksheet itself, sometimes you may want to use the VBA route to delete rows in Excel. These could be deleting a specific row, multiple rows in the selection, deleting alternate rows or those that have a specific value in it. In this tutorial, I will show you how to delete rows in Excel using VBA (multiple scenarios). So let’s get started!...

To begin with, if you try and sort cells that have been merged, it will refuse to do so and show you an error that says – To do this, all the merged cells need to be of the same size. OHHH BUMMER! First of all, this warning does not specify that the issue is because of merged cells, but even if you know that there would be a huge task to unmerge these cells for sorting to work (especially if you have a huge data set)....

One of the common tasks many people have to do is to compare data in the same sheet. This is especially useful for people who work with large data that requires a lot of back and forth in the same worksheet. Excel has a split-screen feature that divides your window into horizontal or vertical panes, and then you can scroll these separately. This way, you can view row # 1 and Row # 500 in the same window (without scrolling back and forth) I will show you how the split-screen feature works in excel and why you should be using it....

When to use Excel MOD Function MOD function can be used when you want to get the remainder when one number is divided by another number. What it Returns It returns a numerical value that represents the remainder when one number is divided by another. Syntax =MOD(number, divisor) Input Arguments number – A numeric value for which you want to find the remainder. divisor – A number with which you want to divide the number argument....

This is why almost all Excel experts recommend saving your work frequently (now Excel even has an autosave feature where it saves files automatically). While this is good practice, I recommend going even further and saving a new copy of your Excel file every day or every week (depending on how frequently you work on it and how important it is). And a fast way of saving a copy of your existing file is by using the ‘Save As’ option in Excel....

When you copy and paste formulas in Excel, it automatically adjusts the cell references. For example, suppose I have the formula =A1+A2 in cell B1. When I copy the cell B1 and paste it in B2, the formula automatically becomes =A2+A3. This happens as Excel automatically adjusts the references to make sure the rows and columns now refer to the adjusted rows and columns. Note: This adjustment happens when you’re using relative references or mixed references....

He asked me if it was possible to have a drop-down list that shows numbers based on the two specified numbers. Something as shown below: Note that the drop-down start from the number in column A and goes up to the number in Column B. For example, the drop down in D2 shows numbers from 1 to 10, and the one in D3 shows numbers from 5 to 20, and so on....

While Conditional Formatting is great, it’s also volatile – which means that if you have a lot of conditional formatting rules applied to large data sets, it can slow down your Excel file. So, I also have to make sure that I remove conditional formatting from datasets where I don’t need it anymore. In this short tutorial, I will show you a couple of easy ways you can use to remove conditional formatting from a selected range of cells, the entire worksheet, or the workbook....

Excel Pivot Table is a great tool. It only takes a few clicks to create a Pivot table and you can easily analyze huge datasets without worrying about human errors. One of the default settings in Pivot Tables is to show a blank cell when there is no data for the given row/column label. In this tutorial, you’ll learn how to replace blank cells with zeroes or any other value in Excel Pivot Tables....