With a simple number formatting trick, you can create and Excel chart as shown below: You can see the negatives values in chart data labels are highlighted in Red and all the values have either an upward or downward arrow with it. To create this, right-click on the chart data labels and select Format Data Labels (shortcut – Control + 1). In the Format Data Labels task-pane, select Number, and make the formatting as shown below:...
For example, you can show the actual sales values versus the target sales values, or the satisfaction rating achieved versus the target rating. There can be multiple ways to create a chart in Excel that shows the data with Actual Value and the Target Value. Here are the two representations that I prefer: In the chart on the left, the target values are shown as the wide gray bar and achieved/actual values are shown as the narrow blue bar....
When to use Excel TRUE Function TRUE function returns the logical value TRUE. It does not take any input arguments. What it Returns It returns the logical value TRUE. Syntax =TRUE() Input Arguments It does not take any input arguments. Additional Notes TRUE function is best used when you are evaluating a condition, and if is TRUE, you simply want to get the TRUE value in the cell. For example: =IF(AND(A1>0,A1<100),TRUE(),”Reject”) In this example, if the condition is not met, it simply returns TRUE as the result....
The difference between a novice and an expert VBA programmer is that the expert programmers know how to effectively handle and use errors. In this tutorial, I will show you various ways you can use to handle errors effectively in Excel VBA. Before we get into VBA error handling, let’s first understand the different types of errors you are likely to encounter when programming in Excel VBA. Types of VBA Errors in Excel There are four types of errors in Excel VBA: Let’s quickly understand what these errors are and when you’re likely to encounter these....
Another similar construct that allows you to check for multiple conditions is the SELECT CASE statement. Select Case is useful when you have three or more conditions that you want to check. You can also use this with two conditions (but I feel If Then Else is easier to use in those cases). A simple example where the Select Case statement is useful when you want to get the grade of a student based on the marks he/she has scored (covered as an example later in this tutorial)....
For example, you may have a need to convert data in centimeters/millimeters to inches or feet. In this short tutorial, I will show you how to use a simple formula to convert a value in one length measurement unit to another. So let’s get started! CONVERT Formula in Excel Before I show you how to convert data from one unit to another, let me quickly introduce the formula that is going to help us do that....
As you’ll go through this tutorial, you’ll see there are different data filter options available based on the data type. Types of Filters in a Pivot Table Here is a demo of the types of filters available in a Pivot Table. Let’s look at these filters one by one: Report Filter: This filter allows you to drill down into a subset of the overall dataset. For example, if you have retail sales data, you can analyze data for each region by selecting one or more than regions (yes, it allows multiple selections as well)....
If you have a dataset and you want to transpose it in Excel (which means converting rows into columns and columns into rows), doing it manually is a complete NO! Transpose Data using Paste Special Paste Special can do a lot of amazing things, and one such thing is to transpose data in Excel. Suppose you have a dataset as shown below: This data has the regions in a column and quarters in a row....
When to use Excel AVERAGEIF Function Excel AVERAGEIF function can be used when you want to get the average (arithmetic mean) of all the values in a range of cells that meet a given criteria. What it Returns It returns a numerical value that represents the average (arithmetic mean) of the values in a range of cells that meets the given criteria. Syntax =AVERAGEIF(range, criteria, [average_range]) Input Arguments range – the range of cells against which the criteria is evaluated....
One such function is the VBA DIR function. While by itself, it may seem like a simple function that does one specific thing. But when you combine it with some other useful elements of the VBA coding language, you can create powerful stuff (covered in the examples later in this tutorial). What Does VBA Dir Function Do? Use VBA DIR function when you want to get the name of the file or a folder, using their path name....
Worry not. Today’s prescription includes an article and a video on how to create dynamic chart titles in excel. Linking a Cell Value to the Chart Title Suppose you have the data as shown below and you have created a chart using it. If you want to change the chart title, you need to manually change it by typing the text in the box. Since the chart title is static, you would have to change it again and again whenever your data is refreshed/updated....
Something as shown below: The idea is to display the value right next to the Excel scroll bar, rather than having it somewhere in the back-end. This way the user can play with the data more effectively. Try it yourself.. Download the file Enjoyed this tutorial? You might also like these: Using Scroll Bar in Dynamic Charts. Creating Drop Down list with Search Suggestion in Excel. Creating Dynamic Filter in Excel....
Sometimes you may want to lock cells in Excel so that other people can’t make changes to it. It could be to avoid tampering of critical data or prevent people from making changes in the formulas. How to Lock Cells in Excel Before we learn how to lock cells in Excel, you need to understand how it works on a conceptual level. All cells in Excel are locked by default....
Given that Pivot Tables are so dynamic and the data in the backend can change often, you need to know the right way to use conditional formatting in a pivot table in Excel. The Wrong Way to Apply Conditional Formatting to a Pivot Table Let’s first look at the regular way of applying conditional formatting in a pivot table. Suppose you have a pivot table as shown below: In the above dataset, the date is in the rows and we have store sales data in columns....
Something as shown below: There is a dual functionality to this – you can select a country’s name from the drop-down list, or you can manually enter the data in the search box, and it will show you all the matching records. For example, when you type “I” it gives you all the country names with the alphabet I in it. Download Example File and Follow Along Watch Video – Creating a Dynamic Excel Filter Search Box...
What it Returns It returns a serial number that represents a specific date in Excel. For example, if it returns 42370, it represents the date: 01 January 2016. Syntax =DATE(year, month, day) Input Arguments year – the year to be used for the date.month – the month to be used for the date.day – the day to be used for the date. Additional Notes (boring stuff.. but important to know) The result displayed in the cell would depend on the formatting of that cell....
When working with large data sets, if you scroll down or to the right of the worksheet you would lose track of the row/column headings. In such situations, you can use the Excel Freeze Panes feature to freeze the rows or columns in your dataset – so that the headers always visible no matter where you scroll in your data. Accessing Excel Freeze Panes Options To access Excel Freeze Panes options:...
When to use Excel NETWORKDAYS Function Excel NETWORKDAYS function can be used when you want to get the number of working days between two given dates. It does not count the weekends between the specified dates (by default the weekend is Saturday and Sunday). It can also exclude any specified holidays. A typical situation where you can use this function is to calculate the benefits accrued to employees overtime....
In this tutorial, you’ll learn how to use the For Next Loop in Excel VBA. If you’re interested in learning VBA the easy way, check out my Online Excel VBA Training. Using FOR NEXT Loop in Excel VBA ‘For Next’ Loop works by running the loop the specified number of times. For example, if I ask you to add the integers from 1 to 10 manually, you would add the first two numbers, then add the third number to the result, then add the fourth number to the result, as so on....
When I get a data file from a client/colleague or I download it from a database, I do some basic checks on the data. I do this to make sure there are no missing data points, errors or duplicates that may lead to issues later. One such check is to find and highlight blank cells in Excel. There are many reasons that can result in blank cells in a dataset:...
