Posts

In this example I’ve set up two lists and created tables from them called List1 and List2. Create a Connection Only Query for the Two Lists For each table, we’re going to create a connection only query that adds a column with the same constant value in each row. We will then use this column to join our two tables together using a merge query. Select a cell in your list and then go to the Data tab and select the From Table/Range command in the Get & Transform Data section....

Method 1: Advanced Filters The Advanced Filter has a great feature that allows you to copy a list of unique values from a range you specify to another location. Now your list of unique values will appear starting in the cell you selected. Notice that even format is copied over. Method 2: Remove Duplicates Excel has a very useful feature called remove duplicates which can be used on either the whole data set or a single column of data....

Here we have the city population data we looked at before. We used the INDEX function to retrieve information about a city with a hard-coded position value. When we supply a number, INDEX retrieves information for the city at that position in the list. Now instead of fetching information by position, let’s convert this worksheet to retrieve information by city name. I’ll do this by using the MATCH function to find the position of the city in the list and using INDEX to retrieve information at that position....

Pareto Chart is based on the Pareto principle (also known as the 80/20 rule), which is a well-known concept in project management. According to this principle, ~80% of the problems can be attributed to about ~20% of the issues (or ~80% of your results could be a direct outcome of ~20% of your efforts, and so on..). The 80/20 percentage value may vary, but the idea is that of all the issues/efforts, there a few that result in maximum impact....

You can also copy-paste formatting from one cell/range to the other. Or, you can choose to clear the formatting from cells that contain a specific value or a specific format. In this short Excel tutorial, I will show you how to quickly remove cell formatting in Excel (and some other cool things you can do). So let’s get started! Remove All Cell Formatting from the Entire worksheet This is something I do when I get the Excel workbook from a database download or a colleague....

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.

In Excel. there are text functions that allow you to quickly change the case of the text (to lower case, upper case, or proper case). Below is an example of each type of case: Excel LOWER Function – Overview LOWER function is one of the many text function in Excel. What does it do? It takes a string as the input and converts all the upper case characters in that text string to lower case....

Here we have a list of 16 properties with prices and other information. Let’s calculate some averages based on the criteria shown in column K. Note that this data already contains a number of named ranges. We have “prices,” “beds,” “size,” “listed,” and “status.” The AVERAGEIFS function can calculate an average for cells that meet multiple criteria. The first argument is average_range; this is the range that contains the values to average....

which returns “x” when B6 contains anything except “red” or “green”, and an empty string ("") otherwise. Notice the OR function is not case-sensitive. In the example shown, we want to “flag” records where the color is NOT red OR green. In other words, we want to check the colors in column B, and take a specific action if the color is any value other than “red” or “green”. In D6, the formula were using is this: In this formula, the logical test is this bit: Working from the inside out, we first use the OR function to test for “red” or “green”: OR will return TRUE if B6 is “red” or “green”, and FALSE if B6 contains any other value....

which generates a series of 12 dates, beginning with May 1, 2019, the date in C4. SEQUENCE can generate results in rows, columns, or rows and columns. In this example, we are asking sequence for an array of numbers that is 12 rows by 1 column, starting with the date in C4, and incrementing by 1. Because dates in Excel are just serial numbers, and the date in C4 is equivalent to 43586, SEQUENCE outputs an array like this: which spills into the range E5:E16....

To drag and drop in Excel, just make a selection and hover the mouse over the edge of the selection. When the cursor changes, you can drag the selection to a new location. With this method, drag and drop is equivalent to cut and paste. The content of selected cells is moved, and the selection you drag will completely overwrite the destination cells. You can hold down the shift key to drag and insert....

where data is an Excel Table in the range B5:C16. As the formula is copied down, the formula returns a sum for each day of the week in column E. Why not SUMIFS? You might wonder why we aren’t using the SUMIFS function to solve this problem? The reason is that SUMIFS is in a group of eight functions that requires a range for the criteria_range argument; it is not possible to provide an array instead....

where data is an Excel Table in the range B5:C16 and n is 3. The result is the sum of quantity for the first 3 Red values. Example formula In the example shown, the formula in cell G5, copied down, is: Notice the value for n is hardcoded as 3. This formula is a good example of nesting one function inside another. Extracting matching data Working from the inside out, the first task is to extract a list of quantities by color....

In the example shown the formula in cell E4 is: This formula uses the number of years a salesperson has been with a company to determine which commission rate table to use. In other words, if years is less than 2, table1 is used as for table_array, and, if not, table2 is used as for table_array. Alternate syntax If the lookup tables require different processing rules, then you can wrap two VLOOKUP functions inside of an IF function like so: This allows you to customize the inputs to each VLOOKUP as needed....

Entering and storing data is a common task in Excel. If this is something you’re doing, then you need to use a table. Tables are containers for your data! They help you keep all your related data together and organized. Tables have a lot of great features and work well with other tools inside and outside of Excel, so you should definitely be using them with your data. This post is going to show you all the ways you can create a table from your data in Excel....

which calculates the area of a trapezoid given the length of side a in column B, the length of side b in column C, and the height (h) in column D. where a is the length of side a, b is the length of side b, and h is the height, measured at a right angle to the base. In Excel, the same formula can be represented like this: So, for example, to calculate the area of a trapezoid where a is 3, b is 5, and h is 2, you can use a formula like this: In the example shown, the goal is to calculate the area for eleven trapezoids where a comes from column B, b is in column C, and h comes from column D....

To allow a user to enter only dates in a certain year, you can use data validation with a custom formula based on the YEAR function. In the example shown, the data validation applied to C5:C7 is: This custom validation formula simply checks the year of any date against a hard-coded year value using the YEAR function. When a user enters a value, the YEAR function extracts and compares the year to 2016: When the years match, the expression returns TRUE and validation succeeds....

This table contains a list of projects showing a sales pipeline. The last column is meant to show the current state of a given opportunity. To the right, in column G, is a list of possible stages. To illustrate how data validation works with tables, I’ll set up the last column of the table to enforce the values in column G, by providing a dropdown list. To start off, I’ll select the stage column, then click the data validation button on the Data tab of the ribbon....

If you need to insert the current date in a way that will not change, use the keyboard shortcut Ctrl + ; This shortcut will insert the current date in a cell as a value that will not automatically change. If you need to display the current date and time, use the NOW function. 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....

When assumptions in column C are changed, the estimated payment will recalculate automatically. To calculate the monthly payment with PMT, you must provide an interest rate, the number of periods, and a present value, which is the loan amount. In the example shown, the PMT function is configured like this: rate = C5/12 nper = C6*12 pv = -C9 Because mortgage rates are annual, and terms are stated in years, the arguments for rate and periods are adjusted in this example....