Purpose Return value Syntax =RANDARRAY([rows],[columns],[min],[max],[integer]) rows - [optional] Row count. Default = 1. columns - [optional] Column count. Default = 1. min - [optional] Minimum value. Default = 0. max - [optional] Maximum value. Default = 1. integer - [optional] Whole numbers. Boolean, TRUE or FALSE. Default = FALSE. Using the RANDARRAY function The RANDARRAY function generates an array of random numbers between two values. The size or the array is determined by rows and columns arguments....

Purpose Return value Syntax =REDUCE([initial_value],array,function) initial_value - [optional] The initial value of the accumulator. array - The array to be reduced. function - The function or custom LAMBDA to apply. Using the REDUCE function The REDUCE function applies a custom LAMBDA function to each element in a given array and accumulates results to a single value. The REDUCE function is useful when you want to process each element in an array and return a single aggregated result....

Explanation At the core, this formula has a simple pattern like this: =SUM(first:current) Where “first” is the first cell in the Total column, and “current” is a reference to a cell in the current row of the Total column. To get the a reference to the first cell, we use INDEX like this: INDEX([Total],1) Here, the array is the entire “Total” column and row number is 1. This works because, the INDEX function returns a reference to the first cell, not the actual value....

About This Shortcut This shortcut display the Save File dialog box. About This Shortcut This shortcut display the Save As File dialog box.

About This Shortcut This shortcut selects one or more table rows in a table, when the cursor is in an Excel table. The first time you use it, the entire table row is selected. The second time, the entire worksheet row is selected. About This Shortcut This shortcut selects one or more table columns in a table, when the cursor is in an Excel table. Behavior changes as the shortcut is used more than once....

Transcript You can change the default chart type by opening the Chart Type control on the ribbon, selecting a chart type, and clicking the set default button. This setting controls the default for all new charts in all workbooks. Note that hen you just randomly select cell in a set of data, Excel will try to plot all data. If you only want to plot one series, you can hold down the control key and select just that data....

Transcript In this video, we’ll look at shortcuts for entering data. Before we start, I want to show you an option related to cursor movement in Excel. On Windows, press Alt + F, then T for Options, and A for Advanced. On a Mac, type command + comma to go to Preference, then click Edit. When you press Return, the cursor normally moves down, but note that you can specify another direction if you like, and you can even turn off cursor movement altogether, if you want the cursor to stay in the same place after pressing Enter....

Explanation The SORT function sorts a range using a given index, called sort_index . Normally, this index represents a column in the source data. However, the SORT function has an optional argument called " by_col " which allows sorting values organized in columns. To sort by column, this argument must be set to TRUE, which tells the SORT function that sort_index represents a row. In this case, we want to sort the data by Score, which appears in the second row, so we use a sort_index of 2....

Explanation In this example, the goal is to sum amounts by weekday. In other words, we want to sum amounts by Monday, Tuesday, Wednesday, and so on. Column B contains valid Excel dates formatted with a custom number format explained below. For convenience, all source data is in an Excel Table named data . The values in E5:E11 are hardcoded text values . A nice way to solve this problem is to use the SUMPRODUCT function together with the TEXT function ....

Explanation In this example, the sum range is the named range “time”, entered as an Excel time in hh:mm format. The first criteria inside SUMIFS includes dates that are greater than or equal to week date in column F: date,">="&$F5 The second criteria limits dates to one week from the original date: date,"<"&$F5+7 The last criteria, restricts data by project, by using the project identifier in row 4: project,G$4 When this formula is copied across the range G5:H7, the SUMIFS function returns a sum of time by week and project....

About This Shortcut This shortcut will toggle formulas and formula results, by enabling and disabling the Show Formulas button on the Formula tab of the Ribbon. You can use this shortcut to quickly see every formula in a worksheet, instead of the value it returns. About This Shortcut This shortcut will insert function arguments needed for a function after the function name has been typed.

Purpose Return value Syntax =TRIM(text) text - The text from which to remove extra space. Using the TRIM function The TRIM function strips extra spaces from text, leaving only a single space between words, and removing any leading or trailing space. For example: =TRIM(" A stitch in time. ") // returns "A stitch in time." The TRIM function can be used together with the CLEAN function to remove extra space and strip out other non-printing characters:...

I first saw a link to this chart on Politico . The original chart is a clustered column chart, but I though it would be interesting to show the data in a stacked bar chart instead. Stacked bar charts make it a lot easier to compare bar lengths. Here’s the original: One thing to note is the approval and disapproval ratings don’t add up to 100%. This is due to some survey respondents expressing no opinion....

Purpose Return value Syntax =VSTACK(array1,[array2],...) array1 - The first array or range to combine. array2 - [optional] The second array or range to combine. Using the VSTACK function The Excel VSTACK function combines arrays vertically into a single array. Each subsequent array is appended to the bottom of the previous array. The result from VSTACK is a single array that spills onto the worksheet into multiple cells. VSTACK works equally well for ranges on a worksheet or in-memory arrays created by a formula....

Transcript In this video, we’ll answer the question: What is an array formula? In the world of Excel formulas, the term “array formula” is probably responsible for more confusion than just about any other concept. This is because the definition of an array formula has become mixed up with the requirement to enter some array formulas in a special way, with control + shift + enter. Thankfully, in the dynamic array version of Excel, we can leave this confusion behind forever, since there is no need to handle array formulas differently from other formulas....

Explanation NETWORKDAYS is a built in function accepts a start date, end date, and (optionally) a range that contains holiday dates. In this case, the start date is Jan 10, 2018, provided as cell B5. The end date is calculated using the EOMONTH function with an offset of zero, which returns the last day of the month of the date given. A list of holidays is provided as the range E5:E14....

If you open up a spreadsheet that you did not create, you may run into the “__xlfn” prefix before certain functions. The generic meaning of xlfn is “unsupported function”. Typically, it indicates that the worksheet was created in a newer version of Excel and uses functions not yet available in the version currently running. In the example shown, the UNIQUE function is used to extract unique values from a range of data....

Explanation The goal of this example is to look up the correct astrological or zodiac sign for a given birthdate, using the table shown in B5:F15. These are based on the Western zodiac signs described here . Zodiac signs are used in horoscopes, which are a kind of forecast of a person’s future, based on the relative positions of the stars and planets at the time of birth. Using the lookup table as shown is somewhat challenging....

Formula Related Functions Count between dates by age range COUNTIFS FIND LEFT RIGHT SUMPRODUCT TEXTBEFORE Count birthdays by year SUMPRODUCT YEAR COUNTIFS BYROW UNIQUE SORT LET Count cells between dates COUNTIFS SUMPRODUCT Count cells between two numbers COUNTIFS SUMPRODUCT Count cells equal to COUNTIF SUMPRODUCT Count cells equal to case sensitive SUMPRODUCT EXACT Count cells equal to one of many things COUNTIF SUMPRODUCT ISNUMBER MATCH Count cells equal to this or that COUNTIF SUM SUMPRODUCT Count cells greater than COUNTIF COUNTIFS Count cells less than COUNTIF COUNTIFS Count cells not between two numbers COUNTIF SUMPRODUCT Count cells not equal to COUNTIF COUNTIFS Count cells not equal to many things MATCH ISNA SUMPRODUCT COUNTA COUNTIF Count cells not equal to x or y COUNTIFS SUMPRODUCT Count cells over n characters SUMPRODUCT LEN N Count cells that are blank COUNTBLANK COUNTA COUNTIF SUMPRODUCT Count cells that are not blank COUNTA COUNTBLANK COUNTIFS SUMPRODUCT Count cells that begin with COUNTIF SUMPRODUCT LEFT LEN EXACT Count cells that contain case sensitive SUMPRODUCT ISNUMBER FIND Count cells that contain either x or y SUMPRODUCT ISNUMBER FIND COUNTIF Count cells that contain errors SUMPRODUCT ISERROR ISERR ERROR....

About This Shortcut If you select just the cells you’d like to sum, SUM formula(s) are placed below the selection. If you select empty cells to the right and Excel will place SUM to the right and SUM horizontally. If you select empty cells below and to the right, Excel will add SUM formulas to cells below and to the right. About This Shortcut This shortcut will toggle formulas and formula results, by enabling and disabling the Show Formulas button on the Formula tab of the Ribbon....