Posts

While it works as expected in most of the cases, in some cases, it can be a little counterproductive. One such area is when Excel decides to round-off numbers (arghhh). In this tutorial, I will show you how to take control and stop Excel from rounding numbers. Why Excel Rounds Off Numbers? Here are some possible reasons why Excel might be rounding off numbers: Now the table look at each of these scenarios and how you can stop Excel from rounding numbers in each case...

There are two special functions, AND and OR, that make this easy to do. Let’s take a look. In this first worksheet, we have a list of employees. Let’s assume that you need to group these employees into two groups. Group A includes employees in Sales and Marketing and group B includes employees in Fulfillment, Support, or Engineering. While this could be done using nested IF statements, an easier way to calculate these groups is to use the IF function together with the OR function....

Syntax ISERROR(Value) Value (required) – This is the value that is checked for an error. Example In this example we test a few values some of which are errors. Only the errors result in TRUE.

where “data” is the named range B5:B16. The formula is then copied to rows below D5 to output the desired list of most frequent numbers. Note: this is an array formula and must be entered with control + shift + enter. Working from the inside out: In cell D5, no filtering occurs and the output of each step above looks like this: In cell D6, with 93 already in D5, the output looks like this:...

where “supplier” is the named range C5:C15, and “client” is the named range B5:B15. Note: this is an array formula and must be entered with control + shift + enter, except in Excel 365. Since the lookup value is an array with 10 values, MATCH returns an array of 10 results: Each item in this array represents the first position at which a supplier name appears in the data. This array is fed into the IF function, which is used to filter results for Client A only: IF returns the filtered array to the MODE function: Notice only positions associated with Client A remain in the array....

Pareto charts highlight the biggest factors in a set of data. Following the idea of 80/20 analysis, they try to show which (approximately) 20% of the categories contribute 80% of the data being measured. They are often used in process improvement to show which factors deserve the most attention. Pros Quickly highlights most important data Excel automatically builds histogram and adds Pareto line Cons Less common chart type may be difficult to ready for many people Built-in Pareto Chart is not as easy to customize as a standard combo chart...

Fields The pivot table shown is based on three fields: Color, Size, and Qty. The Color field is configured as a Rows field, and the Size field is configured as a Columns field. The Color field is also configured as a Value field. The Color field is configured to summarize by count in the Values area: Because the colors are text values, the pivot table automatically performs a count instead of a sum....

In the example shown, the formula in D6 is as follows: First, when you have a formula like this where one function (in this case ROUND) wraps around all others, it’s often helpful to work from the outside in. So, at the core, this formula is rounding the value in B6 using the ROUND function: Where x is the number of significant digits required. The tricky part of this formula is calculate x....

After you’ve created a chart, you have several options for selecting and customizing individual chart elements. First, you can use the Chart Elements menu on the Format tab of the ribbon. After you select a chart, this menu will contain a list of the main chart elements. You can use this menu to navigate to the chart title, the plot area, the data series, and so on. Once an element is selected, you’ll see the name displayed here....

where holidays is the named range E5:E6. Because the days argument is set to 1, the formula returns the “next working day” on each new row. Holidays are provided as the named range E5:E6, which contains dates. Notice December 24 and 25 are excluded from the list since these days are holidays. All dates are formatted with the custom number format " ddd d-mmm-yyyy". Custom weekends If you need take into account custom weekends (i....

In this worksheet, we have several thousand rows of data. Let me show you some shortcuts that will help you move around this data quickly. Every Excel worksheet has an active cell, and you’ll see the address of the active cell displayed in the name box, to the left of the formula bar. If you want to scroll the active cell into focus, you can use the shortcut Control + Backspace on Windows, or Command + Delete on the Mac....

which converts the amount in column B from US Dollars (USD) to the currency indicated in column D, where the named range “xtable” refers to G5:H10. The core of this formula is the VLOOKUP function, configured like this: Here, the lookup value comes from column D, table array is the named range “xtable”, column index is 2, and match type is set to exact match. In this configuration, VLOOKUP finds the currency in the table, and retrieves the conversion rate from column H....

Note: you can also use Flash Fill in Excel 2013 and above, and the “Text to columns” feature in earlier versions of Excel. Both approaches are simpler than the formulas described below. However, for a formula-based solution, read on. 50 ft x 200 ft 153 ft x 324 ft Etc. In a spreadsheet, it’s a lot more convenient to have actual numbers so that you can use them in calculations as you wish....

where “data” is the named range C5:E9. The INDEX function has a special and non-obvious behavior: when the row number argument is supplied as zero or null, INDEX retrieves all values in the column referenced by the column number argument. Likewise, when the column number is supplied as zero or nothing, INDEX retrieves all values in the row referenced by the row number argument: In the example for formula, we supply the named range “data” for array, and we pick up the column number from H2....

where color is the named range C5:C16. As the formula is copied down, the COUNTIF function returns a count for each value in column E in the range C5:C16. COUNTIF function The COUNTIF function takes two arguments: a range of cells to count, and the criteria to use for counting. For example, to count cells equal to “red” in a range, we could use COUNTIF like this: In this example, we want a count for 3 colors, so we have manually created a small table that lists all colors in column E....

While you don’t have to care about the active cell when you’re working on the worksheet, it’s an important thing to know when working with VBA in Excel. Proper use of the active cell in Excel VBA can help you write better code. In this tutorial, I first explained what is an active cell, and then show you some examples of how to use an active cell in VBA in Excel....

Let’s take a look. As we looked at in a previous lesson, the Font group on the ribbon provides access to fonts, font sizes, font color, and basic font styles. The Font tab of the Format Cells dialog box contains all of these options and several more. You can access the Font tab of the Format Cells dialog box using the arrow at the bottom of the font group or using the keyboard shortcut Control-Shift-F....

You can think of conditional formatting as automatic formatting that is triggered by conditions that you define. For example, you can use conditional formatting to automatically change the color of cells that contain values greater than or less than certain values. Conditional formatting is a great way to visually highlight important information in a worksheet. Let’s take a look. A common use case of conditional formatting is to highlight values in a set of data....

If you want to apply superscript or subscript format to the text in a cell, you may not find anything for it in the ribbon. This is one of those things where you need to go a level deep to make it happen. In this tutorial, you’ll learn how to apply the superscript and subscript format in Excel. Superscript Format in Excel A superscript format is needed when you want to have 1st, 2nd, 3rd with the alphabets in the superscript format....

And once you are loaded with all the basics of Microsoft Excel, it’s important for you to master the functions. You must have heard a number of times that the power of Excel lies in its inbuilt functions and flexibility to create formulas as per your need and requirement. One thing which I love the most about Microsoft TEAM that they keep on introducing new functions from time to time....