which outputs the 7 unique values seen in D5:D11. UNIQUE is a dynamic function. If any data in B5:B16 changes, the output from UNIQUE will update immediately. Dynamic source range UNIQUE won’t automatically adjust the source range if data is added or deleted. To feed UNIQUE a dynamic range that will automatically resize as needed, you can use an Excel Table, or create a dynamic named range with a formula....
24 Excel Tricks for Day-to-day Work In this blog post, let me unveil 24 Excel tricks that will save you a lot of time and energy. If you work with excel, sooner or later you get into trouble with something that seems impossible. Here are some quick fixes that will make it possible. Excel Trick #1 – Copy-Paste Formulas without Changing References It happens all the time. You try to copy a cell with a formula to another cell and it gets all mixed up....
One way of getting the VBA code is to record the macro and take the code it generates. However, that code by macro recorder is often full of code that is not really needed. Also macro recorder has some limitations. So it pays to have a collection of useful VBA macro codes that you can have in your back pocket and use it when needed. While writing an Excel VBA macro code may take some time initially, once it’s done, you can keep it available as a reference and use it whenever you need it next....
Power Pivot is a tool available in Excel that will help you analyze and summarize large amounts of data. But you will first need to install the add-in to use it. This post will show you the different methods you can use to install Power Pivot in Excel. What is Power Pivot in Excel? Power Pivot is an Excel add-in that lets users perform powerful data analysis. It allows users to connect to multiple data tables, define relationships between these tables, combine them into a single data model, and then create calculations to perform complex analyses....
Where “states” is the named range G5:H55. VLOOKUP is configured to get the lookup value from column C. The table array is the named range “states”, the column index is 2, to retrieve the abbreviation from the second column). The final argument, range_lookup, has been set to zero (FALSE) to force an exact match. VLOOKUP locates the matching entry in the “states” table, and returns the corresponding 2-letter abbreviation....
Let’s take a look. Here we have a simple table that summarizes sales by salesperson over a four-month period. What we’re going to do is use the INDIRECT function to make a dynamic reference to a named range that corresponds to each month. This is a useful technique when you want to provide an interactive way to change a reference on a worksheet. First, let’s set up a few formulas to get the basics working....
The first argument in the CHOOSEROWS function is array. Array can be a range, or an array from another formula. Additional arguments are in the form row_num1, row_num2, row_num3, etc. Each number represents a specific row to extract from the array, and should be supplied as a whole number. Basic usage To get rows 1 and 3 from an array, you can use CHOOSEROWS like this: To get the same two rows in reverse order: CHOOSEROWS will return a #VALUE!...
Examples The formulas below use the CONVERT function to convert yards to meters, Celsius to Fahrenheit, gallons to liters, and square meters to square yards: Measurement units The tables below show the units available to the CONVERT function in each category. In all cases, Unit can be used for either from_unit or to_unit in the same category. Note: the CONVERT function is case-sensitive. Metric prefixes The prefixes shown in the table below can be used with metric units by prepending the abbreviation to the unit....
In 2010, my roommate got me addicted to watching football. We would keenly follow all the matches in English Premier League and La Liga. I even played fantasy football with a couple of football junkies. I still follow these matches and I still suck at playing it. 🙂 But I am better at making dashboards, and in this tutorial, I will show you how to create an Excel Dashboard that analyses English Premier League games of the 2014-2015 season....
In general, the DATE function is the safest way to create a date in an Excel formula, because year, month, and day values are numeric and unambiguous, in contrast to text representations of dates which can be misinterpreted. Note: to move an existing date forward or backward in time, see the EDATE and EOMONTH. Example #1 - hard-coded numbers For example, you can use the DATE function to create the dates January 1, 1999, and June 1, 2010 with the following syntax:...
The first argument, data_field, names a value field to query. The second argument, pivot_table, is a reference to any cell in an existing pivot table. Additional arguments are supplied in field/item pairs that act like filters to limit the data retrieved based on the structure of the pivot table. For example, you might supply the field “Region” with the item “East” to limit sales data to Sales in the East Region....
The ISTEXT function takes one argument, value, which can be a cell reference, a formula, or a hardcoded value. Typically, value is entered as a cell reference like A1. When value is text, the ISTEXT function will return TRUE. If value is any other value, ISTEXT will return FALSE. Examples The ISTEXT function returns TRUE if value is text: If cell A1 contains the number 100, ISTEXT returns FALSE: If a cell contains a formula, ISTEXT checks the result of the formula: Note: the ampersand (&) is the concatenation operator in Excel....
The MATCH function can perform exact and approximate matches and supports wildcards (* ?) for partial matches. There are 3 separate match modes (set by the match_type argument), as described below. Note: the MATCH function will always returns the first match. If you need to return the last match (reverse search) see the XMATCH function. If you want to return all matches, see the FILTER function. MATCH only supports one-dimensional arrays or ranges, either vertical and horizontal....
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.
You can also use Enter in Windows, and fn + Return on a Mac to paste from the clipboard. 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.
Notes: (1) May not work in Excel 2010 on Vista or Windows 7. Discussion and fix is here: http://superuser.com/questions/183197/whats-the-keyboard-shortcut-to-unhide-a-column-in-excel-2010 (2) May not work in Excel 2007. See http://www.techrepublic.com/blog/microsoft-office/office-solution-keyboard-shortcuts-for-hiding-and-unhiding-columns-and-rows/ (3) In Windows 10, there is a conflict with a keyboard switching shortcut. You can resolve by un-assigning the keyboard layout hot key. You may need to un-assign the input language as well. This is a tricky setting to find. You are looking for Advanced Keyboard Settings, under Region and Language....
Now, if you’re thinking that there must be an inbuilt feature to do this in Excel, I’m afraid you’d be disappointed. While there are multiple ways you can flip the data in Excel, there is no inbuilt feature. But you can easily do this using simple a sorting trick, formulas, or VBA. In this tutorial, I will show you how to flip the data in rows, columns, and tables in Excel....
In the example shown the formula in E5:K6 is: Note: this is a multi-cell array formula and must be entered with Control + Shift + Enter. In the example shown, we are transposing a table that is 2 columns by 7 rows (14 cells), to a table that is 7 columns by 2 rows (14 cells). Note that this function creates a dynamic link between the source and target. Any change in to data in the source table will be reflected in the target table....
the LOOKUP function with the TEXT function. In the example shown, the formula in F5 is: where “entry” is the named range C5:C13, “date” is the named range B5:B13, and E5 contains a valid date. This is an array formula, and must be entered with control + shift + enter. Working from the inside out, the expression: uses the TEXT function to generate an array of strings in the format “mmyy”: {“0117”;“0117”;“0117”;“0217”;“0217”;“0217”;“0317”;“0317”;“0317”} which are compared a single string based on the value in E5, “0117”....
