Posts

7 great keyboard shortcuts for selecting cells quickly.

Syntax AND(Expression1, Expression2, Expression3, … ) Expression1 (required) – This is an expression that can evaluate to either TRUE or FALSE.Expression2, Expression3 etc… (optional) – These are additional expressions you want to test. Example In this example we test two expressions. As seen, the result is only true when both expressions evaluated are true.

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

Syntax COLUMN(Range) Range (optional) – This is a single cell or range which you want the column number from. For ranges the column number of the upper left most cell will be returned. If no range is selected, the column in which the formula appears is returned. Example In this example we get the column number based on a single cell reference, no cell reference and a reference to a range of cells....

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

Character count of the full text string. This is given by the LEN(Text) part of the formula.Character count of the text string with all the characters we’re trying to count removed. This is given by the LEN(SUBSTITUTE(Text,Character,””)) part of the formula. In our example LEN(“Apples and bananas”) results in 18 since there are 18 characters in the string. In our example SUBSTITUTE(“Apples and bananas”,”a”,””) results in the text string “Apples nd bnns”, where each occurrence of “a” has been removed....

Read this post about How To Use The VBA Code You Find Online to find out how to use this VBA code. When you have this code in your worksheet, you might also want an easy and obvious way for someone to use it like adding a button to run it. You can find out how to do that in this post about How To Add A Form Control Button To Run Your VBA Code....

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’re unfamiliar with VBA, then it’s probably a good idea to read this post about How To Use The VBA Code You Find Online before continuing reading and working with the completed workbook. For this code you will need the Microsoft Outlook 16.0 Object Library enabled. In the visual basic editor, go to Tools then References and check the box next to it and press OK button to enable it....

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

MOD(Range,2)=1 will create an array of Boolean values where TRUE will mean the number is odd and FALSE will mean the number is not odd. We then multiply this Boolean array by the Range to get an array where each value is either an odd value from the Range or a 0. This is because NTRUE equals N and NFALSE equals 0 for any number N. In our example MOD({1....

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

Get The Template This is a straight forward billing invoice template with a bit of style. The template is designed to look like a ruled line note pad for a bit of fun. The invoice table includes a quantity (QTY) and Unit Price column to allow for a detailed breakdown of the billed amount. A Description for the billed items can be added to the Description tab and then can be easily added from a drop down list in the Invoice tab....

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

A barcode is a representation of data using parallel lines that vary in width and spacing. Barcodes are most commonly used for inventory management and point-of-sale systems to scan and track products. If you work with inventory or product data you might need to create scannable barcodes for the items you track. This post will show you how you can generate your own barcodes in Excel. Generate a Barcode with a Font Did you know that you can use install and use new fonts for your Excel spreadsheets?...

A random sample is a selection of records chosen from a larger population in such a way that each record has an equal chance of being chosen. The goal is to ensure that the sample reflects the population as accurately as possible. If any one group is over-sampled or under-sampled, then it can skew the results and lead to inaccurate conclusions. Random sampling is essential for statistical work because it helps to eliminate any bias....