Posts

In the example shown, the UNIQUE function is used to extract unique values from a range of data. UNIQUE is only available in Excel 365, and was introduced in 2020. When the workbook is opened in an older version of Excel, the __xlfn prefix appears. Note that the original result is still displayed. However, the formula will not update to show a new result if data changes. Solution or workaround If you open the same workbook in a version of Excel that contains the missing function(s), the _xlfn prefix will be disappear and the function will calculate normally....

In the example shown, the formula in cell F5 is: where data is a named range for B5:D10 {2,3,4} If you want only the first column number, you can use the MIN function to extract just the first column number, which will be the lowest number in the array. Simple version Entered in a single cell, the COLUMN function will display only the first column number, even when it returns an array....

The results in column E are decimal values with the percentage number format applied. Converting this to an Excel formula with cell references, the formula in E5 becomes: As the formula is copied down, it returns the original for each item in the table, based on the percentages shown in column D. Formatting percentages in Excel In mathematics, a percentage is a number expressed as a fraction of 100. For example, 95% is read as “ninety-five percent” and is equivalent to 95/100 or 0....

The trick is to build a custom table that will map values to all the groups you need. In the example shown, the formula in F5 is: VLOOKUP simply looks up the value and returns the group name from the 2nd column in the table. Both columns in the table can contain any values that you need, and the table doesn’t need to be sorted since VLOOKUP is using exact match....

Conditional formatting is evaluated relative to every cell it is applied to, starting with the active cell in the selection, which is cell B5 in this case. To highlight the matching row, we use this logical expression: The reference to B5 is mixed, with the column locked and row unlocked, so that only values in column B (widths) are compared to the value in K6 (width). In the example shown, this logical expression will return TRUE for every cell in a row where width is 200, based on an approximate match of the value in K6 (width, 275) against all values in K6:B11 (widths)....

Example Generic Formula What It Does This formula will add leading zeros to a given number up to a specified number of total characters in the result. How It Works REPT(“0”,N) will create a text string with N zeros. In our example, REPT(“0”,6) results in a text string of 6 zeros “000000“. The TEXT(Number,”000000″) function will then format any given number as a text string with up to 6 characters for leading zeros....

1. Search for existing solutions Try a search first to see if you can find an existing solution you can use or adapt. You can save a lot of time by finding a path others have already travelled. 2. State your goal clearly and briefly Be clear about what you want. For example: I need to sum sales by month I need to count orders on Wednesdays or Mondays I need C1 to be blank unless there are numbers in A1 and B1 I need a date 10 days in the future excluding weekends...

Simple tasks like listing out all sheets, sorting sheets alphabetically, sorting sheets by colour, hiding and unhiding sheets are all commonly done tasks in a workbook. Unforntunately, the options in Excel to do this are mostly manual. These are some common sheet tasks that we can automate with a bit of VBA code! Sorting Sheets Alphabetically You can easily reorder your sheets by drag and drop or you can right click and use the Move or Copy menu....

These could be files the that you open every day in the morning (such as tracking sheet or timesheet), or some project related files that you need to open as soon as you start Excel. While you always have the option to open these files manually, it’s just one of those small time savings things you can do by automating the process. And this automation doesn’t need any VBA code or complex steps....

People who work with a lot of worksheet tabs find it useful to give a specific color to a sheet tab. This could be useful in showing the grouping of similar tabs (for example, all tabs for a specific year or about a specific product can be shown in the same color). It’s also useful if you want to highlight one specific tab by giving it a different color (such as a summary tab or dashboard tab)....

This ConcatenateIf function will concatenate a range of values based on a given criteria and separate them with a text delimiter of your choice. Example In this example we have a list of email addresses with a Y or N in the next column depending on if the person is planning to attend the national dinosaur convention. Now we want to get a comma separated list of the email addresses for those who plan to attend....

This means that while you may see a date such as “10 January 2020” or “01/10/2020” in a cell, in the back-end, Excel is storing that as a number. This is useful as it also allows users to easily add/subtract date and time in Excel. In Microsoft Excel for Windows, “01 Jan 1900” is stored as 1, “02 Jan 1900” is stored as 2, and so on. Below, both columns have the same numbers, but Column A shows numbers and Column B shows the date that’s represented by that number....

And sometimes, you can come across dates that are formatted as text (or look like dates but are not in the acceptable date formats, so Excel treat them as text strings). Since dates are stored as numbers in the backend in Excel, it allows you to format dates in different ways as well as use them in calculations. But when a date is stored as a text string in Excel, you won’t be able to use it in calculations or format it like a date....

Let’s take a look. The LEN function takes just one argument: the text you want to count. If I supply the address B5, which contains the text “Susan,” I’ll get a result of five since there are five letters in the name. The same formula will return the number eight for the text “New York.” That’s because LEN also counts space characters. The abbreviation NY returns two, as you’d expect....

Let’s take a look. Here we have annual sales figures for a small company. We’ll use this data to build a basic column chart. The first step in creating a chart in Excel is to prepare the data. Charting works best if the data is structured in a simple grid that doesn’t include blank rows or columns. First, select the data you’d like to chart. If your data includes totals, don’t include these in your selection....

Unlike INDIRECT and OFFSET, INDEX is a non-volatile function. This means that INDEX will not recalculate whenever a change is made to a worksheet. This makes INDEX ideal for professional models and for worksheets that contain a large amount of data. However, INDEX is a complex function that takes time to understand. To keep things simple, we’ll build pieces of the final INDEX function step by step on the worksheet and then create a named range after we have the formula ready to go....

Gantt Chart is a simple yet powerful project management tool that can be used for creating a schedule or tracking the progress. Let’s say Bruce Wayne (Batman) wants a new Batsuit and instructs Alfred (the butler and his confidante) to get it made. Alfred, who is an avid project management student, comes up with a simple Gantt Chart in Excel to get the plan ready. And this is what he shows Mr....

Let’s take a look. Here we have a workbook with five weeks of test scores for a group of students, and a summary sheet that’s been set up to hold test scores for all five weeks. What we need to do is create formulas on the Summary sheet that pull in the correct values from the other sheets. Referencing cells on other sheets is very similar to referencing cells on the same sheet....

When you first open a worksheet you didn’t create yourself, it may not be clear exactly where the formulas are. Of course, you can just start selecting cells, while watching the formula bar, but there are several faster ways to find all formulas at once. First, you can toggle the visibility of formulas on or off using the keyboard shortcut Control + Grave Accent. This key is just below the Escape key on US keyboards....

Let’s take a look. Sometimes you need to apply formatting to only a few characters or words. It doesn’t work to select a cell and apply the formatting, because that will affect all of the content in the cell. There are two ways to apply this kind of formatting. The first way is to select a cell and apply formatting in the formula bar. Start by selecting the text you want to format and select the format you like....