Posts

We also have some nice laminated shortcut cards.

The WEEKNUM function accepts two arguments, serial_num and return_type. The serial_num argument must have a valid Excel date. The return_type argument controls what day of the week begins a new week number. Return_type is optional and defaults to 1, which sets new week numbers to start on Sunday. When return_type is set to 2, week numbers begin on Monday. With a return_type of 1-17, week number 1 in a given year is assigned to the week that contains January 1....

A few days ago, a colleague came up with this problem. He had a list of URLs as shown below, and he needed to extract all the characters after the last forward slash (“/”). So for example, from https://example.com/archive/January he had to extract ‘January’. It would have been really easy has there been only one forward slash in the URLs. What he had was a huge list of thousands on URLs of varying length and a varying number of forward-slashes....

Note: this is an array formula that must be entered with Control + Shift + Enter. If you’re entering this on the worksheet (and not inside another formula), make a selection that includes more than one row, enter the formula, and confirm with Control + Shift + Enter. This is formula will continue to generate relative numbers even when the range is moved. However, it’s not a good choice if rows need to be sorted, deleted, or added, because the array formula will prevent changes....

In case you prefer reading written instruction instead, below is the tutorial. Conditional Formatting allows you to format a cell (or a range of cells) based on the value in it. But sometimes, instead of just getting the cell highlighted, you may want to highlight the entire row (or column) based on the value in one cell. To give you an example, below I have a dataset where I have highlighted all the rows where the name of the Sales Rep is Bob....

In this tutorial, you’ll learn different ways to calculate the CAGR in Excel: Using Operators Using the POWER function. Using RATE function. Using the IRR Function. But before we dive into how to calculate CAGR in Excel, let’s first understand what it means (feel free to skip this section if you already know what it is). What is CAGR? CAGR or the Compound Annual Growth Rate tells us the growth rate at which our investments have grown on an annual basis....

The WORKDAY function returns a date in the future or past that takes into account weekends and, optionally, holidays. You can use the WORKDAY function to calculate things like ship dates, delivery dates, and completion dates that need to take into account working and non-working days. Now, to review, date calculations can be really simple. To add one day to the date in B5, I can put “1” in C5, and then just use the formula: =B5 + C5 And this gives us December 23rd, as expected....

Once you have a chart in Excel, you can easily change from one chart type to another. Just select the chart and click “change chart type” on the Design tab of the ribbon. Excel will open the change chart type window, where you can browse and select other chart types. Notice that Excel builds a preview for each type using the data you’ve specified in the existing chart. If you Hover over the image with your mouse, you’ll see a larger preview....

One of the common tasks for people working with text data is to extract a substring in Excel (i.e., get psrt of the text from a cell). Unfortunately, there is no substring function in Excel that can do this easily. However, this could still be done using text formulas as well as some other in-built Excel features. Let’s first have a look at some of the text functions we will be using in this tutorial....

But this tutorial is not about that range. A ‘Range’ is also a mathematical term that refers to the range in a data set (i.e., the range between the minimum and the maximum value in a given dataset) In this tutorial, I will show you really simple ways to calculate the range in Excel. What is a Range? In a given data set, the range of that data set would be the spread of values in that data set....

Copy and paste this table into cell A1 in Excel First to find the position of the first numeric character, we can use this formula. This will find the position of the first instance of one of the elements of the array {0,1,2,3,4,5,6,7,8,9} (i.e. the first number) within cell A2 (our text data). The &”0123456789″ part ensures the FIND function will at least find a number if A2 does not actually have a number in it and will allow the formula to calculate without resulting in a #VALUE error....

Empty array An empty array can trigger a #CALC! error, and this is the most common reason you may see a #CALC! error in a worksheet, especially when using the FILTER function. This is because FILTER returns a #CALC! error when no values meet criteria – in other words, FILTER returns an empty array. For example, in the screen below, the FILTER function is set up to filter the source data in B5:D11....

Descriptive Statistic quickly summarizes your data and gives you a few data points that you can use to quickly understand the entire data set. While you can also calculate each of these statistical values individually, using the descriptive statistics option in Excel quickly gives you all this data in one single place (and it’s a lot faster than using different formulas to calculate different values). In this short tutorial, I will show you how to get Descriptive Statistics in Excel....

Loop through all sheets in an active workbook In this snippet of code we loop through all the sheets in the active workbook. We could use this “shell” code to do something like get all the sheet names in a workbook or perform some action on each sheet etc. Loop through all open workbooks In this snippet of code we loop through all currently open workbooks. Loop through all files in a given folder In this snippet of code we loop through all files in a given folder....

Let’s take a look. You might inherit a worksheet that comes from someone else that contains a lot of existing formatting, including heavy use of borders and fills. Rather than try to work with what’s there, piece by piece, it’s often easier and faster to just clear all borders and fills and start fresh. Both borders and fills can be removed using menus on the ribbon. First, select the cells you’d like to change....

And one of those options allows you to sort your data based on the color of the cell. For example, in the below dataset, you can sort by color to get the names of all the students who scored above 80 together at the top and all the students who scored less than 35 together at the bottom. With the sorting feature in Excel, you can sort based on the color in the cell....

Get your copy of the example workbook with the above link. There are many ways you could transform a table into a single column but this post will explore the two ways that make the most sense. Here you have a table with 3 columns and 4 rows. If you wanted to transform this into a single column of data you could start in row 1 column 1 and move down the rows until you reach the last row then move onto row 1 column 2 and start the pattern again until you cover the whole table like in the picture above....

When to use Excel LEFT Function LEFT function can be used to extract text from left of the string. What it Returns It returns the specified number of characters from the left of the string. Syntax =LEFT(text, [num_chars]) Input Arguments text – The text string that contains the characters you want to extract. [num_chars] – (optional) the number of characters to extract from the left of the text string. If you omit this, is defaults to 1....

The effect of showing “Closed” in light gray is accomplished with a conditional formatting rule. Display nothing if cell is blank To display nothing if a cell is blank, you can replace the “value if false” argument in the IF function with an empty string ("") like this: Alternative with ISBLANK Excel contains a function made to test for blank cells called ISBLANK. To use the ISBLANK, you can revise the formula as follows:...

where values in B5:B9 are sorted in descending order. Context Suppose you have a product that is sold in rolls of 100 feet, and orders are allowed in whole rolls only. For example, if you need 200 feet of material, you need two rolls total, and if you need 275 feet, you’ll need to buy three rolls. In this case, you want the formula to return the “next highest” tier whenever you cross over an even multiple of 100....