Let’s take a look. The easiest way to create a custom format in Excel is to first apply a format that is similar to the format you want to create. For example, suppose we want to create a custom format that shows positive numbers with a positive sign and negative numbers with a negative sign. In addition, we want this format to have a single decimal place and to display negative numbers in red....

I often find it quite frustrating when Excel automatically creates a hyperlink as soon as I insert an email id or a URL in a cell and hit enter. Now, when I try to select a cell (or double-click on it to get into the edit mode), it automatically opens the link in a new browser window. Arghhh! In such cases, there is a need to remove these hyperlinks from the worksheet....

One of the best things about the new dynamic array formula engine in Excel is that it’s much easier to see and visualize arrays. Let’s take a look at a few examples. The first way to see arrays is to enter formulas or expressions directly on the worksheet. For example, here we have some numbers in the range B5:B14. In cell E5, I have a formula that squares and then sums the values in the range....

You can easily sort your data alphabetically, based on the value in the cells, or by cell and font color. You can also do multi-level column sorting (i.e., sorting by column A and then by column B) as well as sorting rows (from left to right). And if that was not enough, Excel also allows you to create your own custom lists and sort based on that too (how cool is that)....

Can’t this also be done using comments in cells?? – Well Yes! Yes, you can use cell comments as well. The only drawback with comments is that it is displayed only when you hover the mouse over the cell. On the other hand, you can use Input Message in Excel to display a message whenever a cell is selected (using either mouse or keyboard). Something as shown below: Now whenever you either click on the cell or select it using the keyboard, it would display the message....

Excel contains a special function for repeating text named REPT which stands for “repeat.” The REPT function takes two arguments: the text to repeat, and the number of times to repeat the text. So, if I enter an upper case “A” and the number “3,” we’ll get a triple A. If I expose these arguments on the worksheet, I can quickly add a few more examples. In each case, Excel simply repeats the text you provide as many times as you specify....

In the example shown, the formula in C5 is: This formula generates a working hyperlink to the first blank cell in column C. Basically, we are giving match a “big number” it will never find in approximate match mode. In this mode match will “step back” the last numeric value. Note: this works in this case because all values in C are numeric, and there are no blanks. For other situations (text values, etc....

Syntax NOW() There are no arguments for this function. Example In this example we return today’s date and time.

where value (H4) and data (B5:E104) are named ranges. The VLOOKUP function supports wildcards, which makes it possible to perform a partial match on a lookup value. For instance, you can use VLOOKUP to retrieve values from a table based on typing in only part of a lookup value. To use wildcards with VLOOKUP, you must specify exact match mode by providing FALSE or 0 (zero) for the last argument called range_lookup....

Fields The source data contains three fields: Date, Sales, and Color. Only two fields are used to create the pivot table: Date and Color. The Color field has been added as a Row field to group data by color. The Color field has also been added as a Value field, and renamed “Count”: The Date field has been added as a Column field and grouped by month: Helper column alternative As an alternative to automatic date grouping, you can add a helper column to the source data, and use a formula to extract the year....

Fields The source data contains three fields: Date, Sales, and Color. A fourth field, Group is created by the grouping process: Before grouping, the original pivot table looks like this: Manual grouping is done by selecting the cells that make up a group. The control key must be held down to allow non-contiguous selections. With cells selected, right-click and select Group from the menu: Repeat the process with the second group of items, Gold and Silver....

Once you’ve removed extra spaces, you can copy the cells with formulas and paste special elsewhere as “values” to get the final text. Video: How to clean text with TRIM and CLEAN TRIM with CLEAN If you also need to remove line breaks from cells, you can add the CLEAN function like so: The CLEAN function removes a range of non-printing characters, including line breaks, and returns “cleaned” text. The TRIM function then takes over to remove extra spaces and returns the final text....

where “start” is the named range F6, “n” is F4, and “value” is F5. If the date is not greater than the start date, the formula returns zero. If the date is greater than or equal to the start date, the IF function runs this snippet: Inside MOD, the DATEDIF function is used to get the number of months between the start date and the date in B4. When the date in B4 equals the start date, DATEDIF returns zero....

In the example shown, the formula in cell D7 is This tells Excel to take the value in B7 (1999.9985) and round it to the number of digits in cell C7 (2) with a result of 1999.99 Notice that even though the number in the 3rd position to the right of the decimal is 8, it is still rounded down. In the table, the ROUNDDOWN function is used to round the same number (1999....

While there is no inbuilt method to do this specifically, there are some Excel features and formulas you can use to get this done. In this tutorial, I will show you 4 simple and easy ways to separate text and numbers in Excel. Let’s get to it! Separate Text and Numbers Using Flash Fill Below I have the employee data in column A, where the first few alphabets indicate the department of the employee and the numbers after it indicates the employee number....

I have a spreadsheet that contains music data. The table contains columns for Genre, Artist, Album, Year, Song, and Time, but only the first value is filled in, much like an outline. I want to run this data through a pivot table to analyze the music in in different ways, but because the data isn’t well structured… most cells are blank, so the counts in the pivot table summary are off....

First, just to recap, number formats control how a number is displayed, but they have no effect on the number’s value. You can find a list of available number formats on the home tab of the ribbon. Or you can use Control + 1 (command + 1 on a Mac) to see a list of number formats there. Seven of these number formats have dedicated shortcuts, and they all begin with Control + Shift....

To find something in Excel, you can use Control + F on Windows and Command + F on a Mac. Control F also works on a Mac. Once you’ve found something, you can press Return or Enter to “find again”. If you hold down the shift key, you can move through matches in the opposite direction. After you run a find, and the Find dialog is closed. You can repeat the last search with the keyboard shortcut Shift F4 on Windows, and Command G on a Mac....

Spell Check in Excel is often ignored given that Excel users often work with numbers as compared with text. But it is still one of the important checks one should have in place. As compared to MS Word or PowerPoint, where you can visually see a red underline below the word that has been misspelled, nothing of that sorts happen in Excel. Imagine a disastrous spelling error glaring out of your worksheet when you sent it to your client....

The result is $18,550, the sum of Amounts in the range C5:C16 when the date in B5:B16 is greater than 15-Oct-2022. Note: for SUMIFS to work correctly, the worksheet must use valid Excel dates. All dates in Excel have a numeric value underneath, and this is what allows SUMIFS to apply the logical criteria described below. SUMIFS function The SUMIFS function sums cells in a range that meet one or more conditions, referred to as criteria....