By default, the WEEKNUM function uses a scheme where week 1 begins on January 1, and week 2 begins on the next Sunday (when the return_type argument is omitted, or supplied as 1). With a return_type of 2, week 1 begins on January 1, and week 2 begins on the next Monday. See the WEEKNUM page for more information. ISO week number ISO week numbers, start on the Monday of the first week in a year with a Thursday....
which returns a count of workdays excluding the holidays in B10:B11. For example, in the screenshot shown, the formula in D6 is: This formula returns 5 since there are 5 working days between December 23 and December 27, and no holidays have been provided. Note that NETWORKDAYS includes both the start and end dates in the calculation if they are workdays. NETWORKDAYS can also exclude a custom list of holidays....
Let’s take a look. Here we have a list of movie titles that were copied in from some other system. You can see that there’s a problem with extra space characters. Not only are there extra spaces between words, there are also extra spaces at the beginning and end of some of the titles. Excel contains a special text function called TRIM that is designed to fix this problem. TRIM takes one argument: the text you want to process....
Here we have two lists. Both lists contain the same number of items, but each list is slightly different. We can use conditional formatting with a formula to quickly find and highlight the differences. First, I’m going to name each list. The first list I’ll call list1, and the second list I’ll call list2. It’s not necessary to name the lists, but it will make our formulas easier to read and undertand....
MOD(Range,2)=1 will create an array of Boolean values where TRUE will mean the number is odd and FALSE will mean the number is not odd. We then convert these TRUE and FALSE values to 1 and 0 values by multiplying the array by 1. This will give us the count of the number of odd numbers in the range. In our example MOD({1.36;-2;2;9;10;7;1.33;8},2)=1 will result in the following Boolean array....
For example, in the dataset below, I can easily spot which are the months when the sales were low (highlighted in red) as compared with other months. In the above dataset, the colors are assigned based on the value in the cell. The color scale is Green to Yellow to Red with high values getting the green color and low values getting the red color. Creating a Heat Map in Excel While you can create a heat map in Excel by manually color coding the cells....
These interactive controls (such as a check box, radio button, combo box, scroll bars) are quite useful when you are creating interactive workbooks or excel dashboards. But there is a possibility that you may not have the Developer tab available in the ribbon. If that happens, don’t panic – and keep reading how to get the developer tab. Excel Developer Tab.. Where to Find it? Don’t worry if you can’t find the Excel developer tab in the ribbon....
Let’s take a look. The most basic way to move content in Excel is to select one or more cells, then click the “cut” button on the ribbon, move to another location, and click the “paste” button on the ribbon. When you cut content, notice that Excel doesn’t immediately remove the content. Instead, the cut data is highlighted by a moving dashed line—sometimes called “marching ants.” This indicates that the cut information is on the clipboard and ready to move to a new location....
Excel Paste Special functionality can save a lot of time. One of its features is ‘Operations’, which allows us to quickly perform mathematical calculations (such as addition, subtraction, multiplication, and division) on a range of cells. In this tutorial, I will show you how to multiply in Excel using paste special operators options. These come in handy when you want to avoid applying formulas or using helper columns. Suppose you have the data set as shown below....
Let’s look at how you can use the VLOOKUP function instead. Here we have the classic problem of assigning grades to scores. Each student in the list has a set of test scores that are averaged in column G. In column H, a formula uses a series of four IF statements to determine a grade based on the average. The formula starts with low scores and works up to high scores by using the “less than” operator....
Once you have an Excel Table, it’s very easy to sort the columns. First, let’s do a quick tour of where you can find sorting controls in Excel: • On the home tab of the ribbon, you’ll find sorting options at the far right • On the Data tab, you’ll find also find 3 buttons for sorting • You can also right-click in a column and find a sort menu • Finally, when you click a filter button in a table, you’ll see sorting commands at the top....
You can specify the delimiter (such as a space, comma, or tab) and the Text to Columns would use this delimiter to split the content of the cells. Examples of this include splitting first and last names, or username and domain name in email ids. However, if you have a dataset where the delimiter is a line break (in the same cell), it gets tricky to split these multiple lines in the same cell into separate cells/columns....
In this first worksheet, we have a simple table that shows test scores for 5 sections. The total questions in each section are in column C, and the total questions that were correct are in column D. Column E contains a formula that calculates the percentage score in each section by dividing the correct answers by the total number of questions in the section. Notice we get a Divide by Zero error in column E for the last 2 sections....
Replace is a text function that allows you to quickly replace a string or a part of the string with some other text string. This can be really useful when you’re working with a large dataset and you want to replace or remove a part of the string. But the real power of the replace function can be unleashed when you use it with other formulas in Excel (as we will in the examples covered later in this tutorial)....
Once you have a slicer on a table, you can use styles to quickly change how a slicer looks. You’ll find slicer styles on the Slicer Tools ribbon, which appears whenever a slicer is selected. Just click a thumbnail image to apply a style. Notice each style has a name that appears when you hover with the cursor for a few seconds. The currently applied style is highlighted with a thick border....
In the example shown, the formula in C5 is: Working from the inside out, the logical test inside the IF function is based on the COUNTIF function: Here, COUNTIF counts cells that match the pattern “??-????-???”, but since the range is just one cell, the answer is always 1 or zero. The question mark wildcard (?) means “one character”, so COUNTIF returns the number 1 when the text consists of 11 characters with two hyphens, as described by the pattern....
It’s a great data structure for computers to read, but it’s a bit less accessible for a human to read. In this post we’ll take a look at how to import an XML file into Excel and turn it into a more friendly table format. Excel has support for XML files and it’s actually really easy to import and covert them to tables using power query. You can download the example files used in this post here....
where “inc” (G4) and “rates” (B5:D11) are named ranges, and column D is a helper column that calculates total accumulated tax at each bracket. Background and context The US Tax system is “progressive”, which means people with higher taxable income pay a higher federal tax rate. Rates are assessed in brackets defined by an upper and lower threshold. The amount of income that falls into a given bracket is taxed at the corresponding rate for that bracket....
In the example shown, the formula in F8 is: This is an array formula and must be entered with control + shift + enter. Working from the inside out, we generate the array of matching holidays using a nested IF: If the dates in B4:B12 are greater than or equal the start date in F5, and if the dates in B4:B12 are less than or equal the end date in F6, then IF returns a an array of holidays....
where “data” is the named range B5"B12. Note: this is an array formula and must be entered with control + shift + enter, except in Excel 365. Once the test is run, it returns an array or TRUE and FALSE values: With the lookup_value set to FALSE, and match_type set to zero to force and exact match, the MATCH function returns 4, the position of the first FALSE in the array....
