Posts

Learn the exact steps I take to create World Class Dashboards in Excel. Join the Excel Dashboard Course. A Key Performance Indicators (KPI) dashboard is one of the most used dashboards in business. Its primary objective is to show the performance of key KPIs and provide a comparative view of other KPIs or companies. In this tutorial, I will show you how to create a KPI dashboard in Excel. This is an improved version of a dashboard that I created last year in my previous job role as a financial/data analyst....

How It Works The shift_amount can either be positive or negative. If a negative number is provided, the bits are shifted to the right instead. Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

The GCD function takes one or more arguments called number1, number2, number3, etc. All numeric values are expected to be integers. Numbers with decimal values will be truncated to integers before a result is calculated. Each argument can be a hardcoded constant, a cell reference, or a range that contains multiple values. The GCD function can accept up to 255 arguments total. Examples To return the greatest common divisor of the numbers 60 and 36: GCD returns the number 12, since 12 is the largest factor that goes into both numbers evenly....

The OR function is used to check more than one logical condition at the same time, up to 255 conditions, supplied as arguments. Each argument (logical1, logical2, etc.) must be an expression that returns TRUE or FALSE or a value that can be evaluated as TRUE or FALSE. The arguments provided to the OR function can be constants, cell references, arrays, or logical expressions. The purpose of the OR function is to evaluate more than one logical test at the same time and return TRUE if any result is TRUE....

Examples RAND takes no arguments: Automatic recalculation The RAND function will calculate a new result each time a worksheet is edited. To stop random numbers from being updated, copy the cells that contain RAND to the clipboard, then use Paste Special > Values to convert to a static result. To get a single random number that doesn’t change when the worksheet is calculated, enter =RAND() in the formulas bar and then press F9 to convert the formula into its result....

REPT can be used to pad values to a certain length or to build a simple in-cell histogram. To pad numeric values with zeros, a custom number format may be a better option. Examples To repeat “x” five times, you can use the following formula: Inputs to REPT can be variable. In the example shown above, REPT is configured to repeat the values in column B using the count in column C....

Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

where “list” is the named range B6:B11. where “list” is a named range that corresponds to the range B6:B11. The IF function requires a logical test to return TRUE or FALSE. In this case, the COUNTIF function performs the logical test. If the value is found in list, COUNTIF returns a number directly to the IF function. This result could be any number… 1, 2, 3, etc. The IF function will evaluate any number as TRUE, causing IF to return “OK”....

where data is the named range C5:I13. The result is an array with nine sums, one for each row in the range, as seen in column K. Note: in older versions of Excel you can use the MMULT function, as explained below. With the BYROW function In Excel 365, the most straightforward way to generate subtotals for each row is with the BYROW function. The purpose of BYROW is to process data in a “by row” fashion....

B4 contains a date value for January 5, 2016. The YEAR function returns the number 2016 representing the year of the date. Note that you can use YEAR to extract the year from a day entered as text: However, using text for dates can cause unpredictable results on computers using different regional date settings. In general it’s better (and more flexible) to supply an address to a cell that already contains a valid date....

Let’s take a look. Let’s pick up where we left off and work with the drink size headings in our sample coffee menu. Holding down the Control key, we can select all of these headings at once and work with them at the same time. First, let’s left-align the headings and reduce the font size a bit. We can do all of this using the controls on the ribbon. To bold the headings, just click the bold button on the ribbon....

A really simple and often needed task is to calculate the square root in Excel. And as I said, there are multiple ways you can do that in Excel (formulas, VBA, Power Query). In this tutorial, I will show you different ways to calculate square root in Excel (and you can choose whatever method you like best). But before I get into how to calculate it, let me quickly cover what is square root (feel free to skip to the next section if I get too preachy)....

Example Generic Formula What It Does This formula will calculate the area of a circle given its radius. How It Works The area of a circle is given by PiRadius^2 where Pi is a constant approximately equal to 3.14159265. Excel has this constant built in as a function with no parameter inputs PI(). The POWER function will take any number and raise it to the power of any other number. POWER(Radius,2) will return the square of the Radius....

In this video we’ll look at three functions that allow you to easily change case of text in Excel: UPPER, LOWER, and PROPER. In this worksheet, we have two columns that contain names. Column B contains last names in uppercase text, and column C contains first names with the first letter capitalized. In column D, I’ll add a formula that capitalizes the first name using the UPPER function. The UPPER function takes just one argument: the text you want in upper case....

In such cases, it’s good to know how to manipulate text data and get the desired result. One of the most common tasks most Excel users have to do is work with a dataset of names. Often you’ll find that the first name and the last name are in separate columns, and you may have a need to combine these first and last names and get these as a combined name in a cell....

We use 1ISTEXT to return a 1 for any text strings and 0 for #N/A errors. We then SUM the result up and this is our count of cells containing a formula. In our example FORMULATEXT(Range) results in the following array. ISTEXT({“=1+2″;#N/A;”=CHOOSE(1,”Hi”,”Bye”)”;”=PI()”;”=ISODD(7)”}) will return an array with TRUE values when there is a text string and FALSE values when there is an #N/A error value. In our example this results in {TRUE;FALSE;TRUE;TRUE;TRUE}....

All charts in Excel have a chart area, which encloses all elements of the chart. The easiest way to select the chart area is to click just below the top edge of the chart. The Chart Elements menu on the Format tab of the ribbon, and the Format Task pane title will confirm the chart area is selected. Once the chart area is selected, you can make a variety of formatting changes....

This will enable you to easily show and hide different areas of the worksheet so that only relevant data is visible. In this tutorial, I will show you three methods to group columns in Excel. Note: Ensure that the worksheet does not have any hidden columns before applying any of the following methods. Method #1: Select the Columns to be Grouped and Apply the Group Command When we use this method, we first select the columns that we want to group and then apply the Group command....