By Dynamic Hyperlinks I mean links that changes based on the selection (or any other user action) Suppose I have the data set as shown below: This is the back end data, and I have a summary sheet where I have drop downs where the person can select the Month. The idea is to update the hyperlink with the selections so that it takes the user to the right cell when the hyperlink is clicked....

Let’s take a look. The easiest way to add a new worksheet to a workbook is to click the Insert Worksheet tab that sits to the right of the last tab in the workbook. When you click this button, Excel will immediately add a new worksheet to the workbook to the right of the last worksheet. No matter which tab is selected, the new worksheet goes to the end of the list....

Step 1: Activate the document location tool Activate the document location quick access tool. Now you’ll be able to quickly and easily copy and paste the workbooks location and name anywhere you need to from the quick access document location tool. Tip: You can also access the customization menu easily by right clicking anywhere on the quick access toolbar and selecting Customize Quick Access Toolbar.

Here we have a simple model that shows the expense of making coffee at home vs. buying coffee in a coffee shop. Let’s take a look through the formulas in this model to see how they work. First, let’s find all the formulas. We can easily do that by using Go To Special and then selecting Formulas. When I click OK, all formulas in the worksheet are selected. To make it easier to see which cells contain formulas, I’ll go ahead and add a fill to these cells....

Let’s take a look. Like previous examples, we have a simple worksheet that highlights several cell references. Following the instructions in the table, let’s create the formulas we need to use exponents. The first formula doesn’t use any cell references. For 3 squared, we input = 3 ^ 2 which gives us 9. Excel doesn’t care about white space in formulas, so feel free to add space for better readability....

On this worksheet, I have two identical sets of order data. I’m going to walk through the process of constructing a summary of sales by item for both sets of data. With the data on the left, I’ll use standard formulas and no table. On the right, I’ll create an Excel Table, and build formulas that use structured references. To start off, I’ll add a bit of formatting to the data on the left to make it easier to see....

Table slicers have a variety of settings. To access these settings, select a slicer and click “Options” under the Slicer Tools menu in the ribbon. Options appear grouped in 5 main categories Name and settings 2. Slicer styles 3. Commands to arrange and align slicers 4. Controls for slicer buttons, and 5. Inputs to set slicer size The Slicer Tools ribbon will appear whenever you select a slicer. The slicer settings button opens a window with more settings....

Step 1: Enable the Developer tab in the Ribbon. By default the Developer tab is hidden and you will need to enable it to use in the ribbon. Step 2: Open the visual basic editor. Now you should see the Developer tab in your Excel ribbon and you can open the visual basic editor (VBE) from the ribbon. Alternatively, you can open the VBE with the Alt + F11 shortcut....

In my last article, I wrote about mouse double-click tricks. In this tutorial, I will show you some amazing things you can do in Excel by using a combination of mouse and keyboard. Shift Rows/Columns/Cells Suppose you have three cells, and you want to shift the content of cell A1 to cell A3 (as shown below), how would you do it? Usually, we go for the longer approach with a minimum of 3 steps, where you copy and paste to achieve this....

where data (B5:E15) and n (G5) are named ranges. This formula returns TRUE if a row is a “last n row”, and FALSE if not. In brief, we get the current row in the workbook, then subtract the first row number of the range plus 1. The result is an adjusted row number that begins at 1. The INDEX function is simply one way get the first cell in a given range: This is for convenience only....

The workbook contains four worksheets: one summary, and three months, “Jan”, “Feb”, and Mar". Each of the month sheets has the same structure, which looks like this: The formulas on the summary tab lookup and extract data from the month tabs, by creating a dynamic reference to the sheet name for each month, where the names for each sheet are the month names in row 4. The VLOOKUP function is used to perform the lookup....

Since C7 has no value in the screen above, the formula shows no result. In the screen below, C7 contains a number and the sum is displayed: In the example shown, we are using the IF function together with the COUNT function. The criteria is an expression based on the COUNT function, which only counts numeric values: As long as the range contains three numbers (i.e. all 3 cells are not blank) the result is TRUE and IF will run the SUM function....

The result is a decimal value that is formatted using the percentage number format. However, since we don’t have a column for the number of students absent in the table, we need to calculate this number as part of the formula: After we convert this to an Excel formula with cell references, the formula in E5 becomes: As the formula is copied down, the formula returns calculated “percent absent” for each class listed in the table....

Pivot Table Fields In the pivot table shown, there are three fields, Name, Date, and Sales. Name is a Row field, Date is a Column field grouped by month, and Sales is a Value field with the Accounting number format applied. The Date field is grouped by Month, by right-clicking on a date value and selecting “Group”. Steps Notes Author Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa....

The asterisk (*) is a wildcard for one or more characters. By concatenating asterisks before and after the value in D5, the formula will count the value as a substring. In other words, it will count the value if it appears anywhere inside any cell in the range. Any positive result means the value was found. By comparing the result with the greater than operator (>) and zero, we force a final result of TRUE or FALSE....

The result is $88.00, the sum of the Total in F5:F16 when the Color in C5:C16 is “Red” and the State in D5:D16 is “TX”. Note that the SUMIFS function is not case-sensitive. SUMIFS function The SUMIFS function sums cells in a range that meet one or more conditions, referred to as criteria. To apply criteria, the SUMIFS function supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching. The syntax for the SUMIFS function depends on the number of conditions needed....

The problem is that the formula area in a conditional formatting rule isn’t very friendly. You don’t get highlighted cell references, you don’t get function autocomplete…heck….you don’t even get screen tips. As a result, it’s hard to “see” if a formula is going to work until after you save the rule. If it doesn’t, you have to use trial and error: This isn’t much fun, and it can be really frustrating when you run into a tricky problem....

In the example shown, the formula in F6 is: The lookup value comes from cell E6, which must be a valid date. The table array is the range B6:C11, and the column index is 2, since the amounts are in the second column of the table. Finally, zero is provided for the final argument to force an exact match. The VLOOKUP function locates the date value for Sept 4, and returns the value at the same row in the second column: 12,500....

QR codes are two-dimensional barcodes that can be read by scanners and smartphones. QR codes are a popular way to link physical objects to digital information. When a QR code is scanned, it will link the user to a website, text, audio, or video file. This post will show you how you can make QR codes in Excel. Download your copy of the example workbook used in this post and follow along....

It’s no doubt the reason why many people need to calculate the square root of numbers in Excel. With such a common task, there are many ways to get it done in Excel. In this post, I’ll show you 5 ways you can use to calculate the square root of a number. What is a Square Root of a Number? The square root of a number is another number that produces the original number when multiplied by itself....