In the example shown, the array formula in B4:H4 is: On the worksheet, this must be entered as multi-cell array formula using Control + Shift + Enter This is a robust formula that will continue to generate relative numbers even when columns are inserted in front of the range. The second COLUMN function generates an array with just one item like this: which is then subtracted from the first array to yield: Finally, 1 is added to get:...
Which returns 36 hours, based on a custom schedule where 8 hours are worked Mon-Fri, 4 hours are worked on Saturday, and Monday September 3 is a holiday. Holidays are supplied as the named range G6:G8. The work schedule is entered as a text string in column D and can be changed as desired. Note: This is an array formula that must be entered with Control + Shift + Enter....
Like line charts, area charts are a good way to show trends over time. However, when plotting multiple data series, you must pay attention to the order in which the data series are plotted. Here we have some US census population data for several states. Let’s plot this data in an area chart. To start off, I’ll just select data for Arizona, and create a new area chart. Now, if I try Recommended Charts, we’ll see an area option that looks good....
To do this we will need to set up a few items in our workbook. A cell for our genre selectionA range for our list of genresRanges for each list of films within each genre We are going to let the user select from these three genres: Drama, Comedy and Horror. Now when you select a genre, the drop down list for the title selection will change depending on the genre....
Excel is the most common data tool used in businesses and personal productivity across the world. Since Excel is so widely used and available, it tends to get used frequently to store and manage data as a makeshift database. This is especially true with small businesses since there is no budget or expertise available for more suitable tools. This post will show you what a database is and the best practices you should follow if you’re going to try and use Excel as a database....
One such useful (and less-known) feature in the Custom Lists in Excel. Now, before I get to how to create and use custom lists, let me first explain what’s so great about it. Suppose you have to enter numbers the month names from Jan to Dec in a column. How would you do it? And no, doing it manually is not an option. One of the fastest ways would be to have January in a cell, February in an adjacent cell and then use the fill handle to drag and let Excel automatically fill in the rest....
To enter a date in Excel, you need to type the date in a format that Excel can recognize. When checking for a date, Excel will look for a month, a day, and a year in a variety of formats. If you don’t supply a year, Excel will use the current year. Let’s take a look. In North America, Excel defaults to a month, day, year format, so dates following this format are easily recognized....
Let’s take a look. To understand how find and replace works with formatting, let’s use the name table, and the example of replacing Ann with Anna. Before we replace any values, let’s add an orange fill to all cells that contain Ann. Now let’s open the Find and Replace dialog box and set up our Find options. Let’s look for Ann, and select “Match entire cell contents.” Let’s also add a format to match the orange fill we’ve just applied....
With a bit of VBA know-how, it can be done fairly quickly. Writing the code to loop through all the sheet objects in the active workbook and write them out to a sheet would only take a dozen lines of code. What if you needed to this for all workbooks in a given folder? Hmm, a bit more code and it’s possible. You first need to loop through all the workbooks in a folder, then for each one loop through all the sheets and write the names out to a workbook....
The regular way of doing this is: Enter 1 in cell A1. Enter 2 in cell A2. Select both the cells and drag it down using the fill handle. Sometimes it may get a bit irritating to drag the fill handle to the last cell which can be many folds below the current cell. In this tutorial, I’ll show you a faster way to fill numbers in cells without any dragging....
I often need to use this when I’m creating tutorials where I need a random set of numbers or items. It could also be useful if you’re creating a random team in your office or classroom. In this tutorial, I will show you three easy ways to quickly shuffle a list of names or items in Excel. So let’s get started! Randomize List Using SORTBY Formula Suppose you have a list of names as shown below, and you want to randomize this list....
For example, I have the dataset as shown below, and I want to get the sum of all orange and yellow color cells. Unfortunately, there is no in-built function to do this. But never say never! In this tutorial, I will show you three simple techniques you can use to sum by color in Excel. Let’s dive in! SUM Cells by Color Using Filter and SUBTOTAL Let’s start with the easiest one....
Where “Table” is an Excel Table with data in B5:D14. Note: This is an array formula and must be entered with control + shift + enter. Working from the inside out, the IF function is set up like this: Here, each value in the gender column is tested against the value in G4, “Male”. The result is an array of boolean values like this: Only scores associated with males make it into the array, female scores are translated to FALSE....
In the example shown, the formula in C5 is: The COUNTIF function takes a range and a criteria. In this case, we give COUNTIF a range equal to all rows in Sheet2. Note: an easy way to enter this range is to use the Select All button. For criteria, we use a reference to C4, which contains “John”. COUNTIF then returns 15, since there are 15 cells in Sheet2 equal to “John”....
The SEQUENCE function can be used to generate numeric sequences of all kinds. Since Excel dates are just numbers, SEQUENCE works well for generating dates. In this first worksheet, we have a couple cells set up to collect input for a start date, and days. I’ll enter the SEQUENCE function in cell E5 and configure it to use these inputs. The rows argument corresponds to the number of dates we want, so we want to use C5....
Note: this is an array formula and must be entered with control + shift + enter, except in Excel 365. Working from the inside out, the MID function is used to extract the text in B5, one character at a time. The key to this step is the use of the ROW function with the INDIRECT function here: This snippet spins up an array containing 100 numbers like this: Note: 100 represents the maximum characters to process; adjust to suit your data....
where amount (C5:C16) and date (B5:B16) are named ranges. Note: as explained below, the values in E5:E10 are valid Excel dates, formatted to display the month name only. Both solutions use the named ranges amount (C5:C16) and date (B5:B16) for convenience only, you can also use absolute references instead, or an Excel Table with structured references. Also note that the values in E5:E10 are valid Excel dates, formatted to display the month name only....
A lot of people don’t know that Excel has an options menu. The Excel Options menu contains a wealth of essential features that allow you to customize your Excel experience. The Excel Options menu will allow you to customize your ribbon, change calculation settings, change language settings, and change many other workbook and app settings. In this blog post, we’ll show you how to open the options menu so you can access the various useful features it contains....
There are many different ways to select a cell in Excel – such as using the mouse or the keyboard (or a combination of both). In this article, I would show you how to select multiple cells in Excel. These cells could all be together (contiguous) or separated (non-contiguous) While this is quite simple, I’m sure you’ll pick up a couple of new tricks to help you speed up your work and be more efficient....
Printing your Excel reports is a very common activity. But when you try to print your Excel document, you will not see any of the light gray gridlines that appear in your sheets. This is because the default print settings will exclude them. What if you want to include these gridlines on the printouts? This can help the reader to differentiate between separate pieces of data. You can change this setting so the gridlines do appear in any printed items whether it’s printing to paper or PDF files....
