If you work with a lot of numbers in Excel, it’s a good practice to highlight negative numbers in red. This makes it easier to read the data. There are various techniques you can use to highlight negative numbers in red in Excel: Using Conditional Formatting Using Inbuilt Number Formatting Using Custom Number Formatting Let’s explore each of these techniques in detail. Highlight Negative Numbers in Red – Using Conditional Formatting Excel Conditional formatting rules are applied to a cell based on the value it holds....

Excel has a lot of useful in-built charts and you can also combine and create some amazing combination charts as well. Excel charts are a great way to show your data visually and are often the most used ones when you have to present it to your manager/clients. While your charts may be in Excel, it’s not necessary that it’s the best way to show them to your clients/managers. Often, it would be required to show these charts in a PowerPoint presentation or in an MS Word document of PDFs....

You can easily sort a data set by using the sort icons in the ribbon or the sort dialog box. Then why do you need to know how to do this using VBA? Knowing how to sort data using VBA can be helpful when included as a part of your code. For example, suppose you get a data set daily/weekly that you need to format and sort in a specific order....

When to use Excel IS Function IS function can be used when you want check a specific value and get either TRUE or FALSE based on the outcome. What it Returns It returns TRUE if the condition is met and FALSE if it is not met. Syntax =ISBLANK(value) =ISERR(value) =ISERROR(value) =ISLOGICAL(value) =ISNA(value) =ISNONTEXT(value) =ISNUMBER(value) =ISREF(value) =ISTEXT(value) Input Arguments value – the value that you want to test. It could be a blank (empty cell), error, logical value, text, number, or reference value, or a name referring to any of these....

First, let’s see the usual way of doing it. Suppose, you have a data set as shown below and you want to insert blank cells in C2:C4, in such a way that the data in C2:C4 should shift one column to the right The usual way of doing this is to first select C2:C4, then right click and Select Insert. In the Insert dialogue box, select Shift Cells Right. While this isn’t exactly a very long way to do this, there is a better and quicker way to do it....

Excel Custom Number formatting is the clothing for data in excel cells. You can dress these the way you want. All you need is a bit of know-how of how Excel Custom Number Format works. With custom number formatting in Excel, you can change the way the values in the cells show up, but at the same time keeping the original value intact. For example, I can make a date value show up in different Date formats, while the underlying value (the number that represents the date) would remain the same....

Now before we move forward, let’s first look at a few reasons why you may end up with a workbook that has numbers stored as text. Now, let’s see how to tackle such cases. Convert Text to Numbers in Excel In this tutorial, you’ll learn how to convert text to numbers in Excel. The method you need to use depends on how the number has been converted into text. Here are the ones that are covered in this tutorial....

An Excel drop down list is a useful feature when you’re creating data entry forms or Excel Dashboards. It shows a list of items as a drop down in a cell, and the user can make a selection from the drop down. This could be useful when you have a list of names, products, or regions that you often need to enter in a set of cells. Below is an example of an Excel drop down list:...

My first thought was to do this using VBA (if possible), but the answer is so easy, it surprised me. Creating Subcategory in Drop Down List in Excel All you need to do is to introduce a couple of spaces before the items/names of the subcategory. Now when you use this list (with spaces) to create a drop-down list, it will automatically show the indentation. Here is how to do it:...

But if you can’t see any tabs and are wondering where has it disappeared, worry not. There are some possible reasons that may have been the cause of missing tabs in your Excel workbook. In this article, I will show you a couple of methods you can use to restore the missing tabs in your Excel Workbook. If you can’t see any of the tab names, it is most likely because of a setting that needs to be changed....

Counting is one of the most common tasks people do in Excel. It’s one of the metric that is often used to summarize the data. For example, count sales done by Bob, or sales more than 500K or quantity of Product X sold. Excel has a variety of count functions, and in most cases, these inbuilt Excel functions would suffice. Below are the count functions in Excel: COUNT – To count the number of cells that have numbers in it....

A Pivot Table Slicer enables you to filter the data when you select one or more than one options in the Slicer box (as shown below). In the example above, Slicer is the orange box on the right, and you can easily filter the Pivot Table by simply clicking on the region button in the Slicer. Let’s get started. Click here do Download the sample data and follow along....

In this tutorial, I will show you how to create a drop-down filter in Excel so that you can extract data based on the selection from the drop-down. As shown in the pic below, I have a created a drop-down list with country names. As soon as I select any country from the drop-down, the data for that country gets extracted to the right. Note that as soon as I select India from the drop-down filter, all the records for India are extracted....

For example, you may not be able to choose the state unless you have selected the country first. This construct ensures that you do not miss out on filling some of the data. Something as shown below A colleague wanted a similar construct in Excel, and the only thing that came to my mind was Data Validation. How to Restrict Data Entry in a Cell in based on Another Cell In this tutorial, I will show you how to restrict entries in a cell based on a formula....

Early this week, I was going through our annual holiday calendar to check for the next holiday, and plan a vacation if possible. And that gave me the idea for this Excel holiday calendar template. In this tutorial, I will show you how to calculate the date of a given holiday (such as Labor Day). And you can also get an awesome Excel holiday list template (free download) at the end of this blog....

She wanted to know how to apply bold font format to a specific part of a string within a cell. For example, apply the bold format to only the word ‘Hello’ from ‘Hello World’. And she wanted to do this for hundreds of cell at once. Since there is no inbuilt functionality in Excel that can do that, I created a simple macro that uses the Excel VBA InStr function (you will see how to do this in Example 4 in this tutorial)....

A lot of times, you may encounter a data set where only one cell is filled with data and the cells below it are blank till the next value. Something as shown below: While this format works for some people, the problem with this sort of data is that you cannot use it to create Pivot Tables or use it in calculations. And this has an easy fix! In this tutorial, I will show you how to quickly fill down cells in Excel until the next filled value....

While this does no harm in most cases, it’s good to have a proper understanding of what a formula is and what a function is in Excel. And, of course, it is going to give you a lot of opportunities to correct others and brag about your own Excel knowledge (if you’re into that kind of fun) In this article, I will explain the difference between formula and function in Excel (with examples)...

For example, you may want to get a consistent look in your data set as shown below: In this tutorial, you’ll learn various ways to add leading zeroes in Excel (i.e., add a 0 in front of a number): Converting the Format to Text Using Custom Number Formatting Using Text Function Using REPT/LEN functions Using VBA Each of these methods has some merits and drawbacks (covered in each section). Let’s see how each of these works....

You must have seen it on hundreds of web pages where you are asked to select an option by clicking on a small round shape next to the text. As soon as you select it, it gets a black dot in it (an indication that is marked). The same thing can also be created in Excel. In this tutorial, I will refer to it as the Radio Button (as my Computer teacher taught me)....