Posts

One of the most used ways is using the Merge & Center option in the Home tab. The issue with using Merge & Center is that it can merge the cells, but not the text within these cells (i.e., you lose some data when you merge the cells). Let’s say we have a data set as shown below: If I select cell A1 and B1 and use the Merge & Center option, it will keep the text from the left-most cell (A1 in this case) and but you will lose the data from all other cells....

Here we have a table that contains five test scores for a group of students and an average score in Column I. How can we rank these students from highest to lowest scores? Well, one option is to sort the students by average score in descending order. Next, you can enter a “1” for the rank of the first student, “2” for the rank of the second student, and then just double-click the fill handle to copy that down....

Using VBA Macros in Excel can be a huge time saver. You can automate a lot of repetitive tasks and create new functions and functionalities in Excel with simple VBA macro codes. But in some cases, you may want to remove all the macros from an Excel workbook (or delete specific macros only). This may be the case when you get a workbook from someone else and you want to make it macro-free, or when you’re sending a file with macros to someone and the receipt doesn’t need these in the workbook....

While there is no way to do this using inbuilt functionalities in Excel, it can easily be done using VBA. Something as shown below: In this tutorial, I will give you the VBA code and show you the exact steps to get this done in Excel. Click here to download the example file and follow along. Let me first give you the VBA code that will select every third row in the dataset that you have selected....

Let’s take a look. Let’s quickly build a pivot table that shows total sales and order count by product. As usual, products are listed in alphabetical order by default. To sort a pivot table by value, just select a value in the column and sort as you would any Excel Table. We can do the same thing with Orders. Let’s sort orders in descending order. As always, we can hover over the sort icon to see the currently applied sort options....

Get the example workbook with the above link to follow along. In this example, you have a table of sales figures each in a separate tab named Jan through Dec. Each sheet is the same format with the table in the same position within each sheet. If you wanted to create a Total sheet and have a table in it that sums up each of the tables in the Jan to Dec sheets, then you could use the above formula and copy it across the whole table....

When to use Excel COUNT Function COUNT function can be used to count the number of cells that contain numbers. What it Returns It returns a number that represents the number of cells that contain numbers. Syntax =COUNT(value1, [value2], …) Input Arguments value1 – the first item, cell reference, or range within which you want to count numbers. [value2], … – (optional) up to 255 additional items, cell references, or ranges within which you want to count numbers....

When to use Excel COUNTA Function COUNTA function can be used when you want to count all the cells in a range that are not empty. What it Returns It returns a number that represents the number of cells that are not empty. Syntax =COUNTA(value1, [value2], …) Input Arguments value1 – the first item, cell reference, or range within which you want to count the number of cells that are not empty....

Let’s take a look. Here’s the VLOOKUP commission example we’ve looked at previously. Let me quickly run through the example again to recap. To look up the correct commission value in this table with VLOOKUP, we give VLOOKUP the value to look up and the table array to use. In this case, I won’t use a named range, but I will convert the table to an absolute reference so that I can copy the formula down....

Let’s take a look. By default, the Quick Access Toolbar sits above the ribbon at the upper left of your screen. It displays a small set of icons that represent common, useful commands that you’d like to keep handy. To customize the Quick Access Toolbar, click the small arrow to the right of the toolbar. In this menu, you’ll see the currently enabled icons appear with a checkmark. In this case we see Save, Undo, and Redo....

In the example shown, the formula in E5 is: Last relative position, not row on worksheet When building advanced formulas that create dynamic ranges, it’s often necessary to figure out the last location of data in a list. Depending on the data, this could be the last row with data, the last column with data, or the intersection of both. Note: we want the last relative position inside a given range, not the row number on the worksheet:...

where quarter (C4:F4) and data (C5:F16) are named ranges. With a lookup value of “Q3” in cell H4, the result is all values associated with Q3, which spill into the range H5:H16. Note: this problem can also be solved with INDEX and MATCH and FILTER, as described below. Although this example shows off the simplicity of the XLOOKUP function, it can also be solved with a straightforward INDEX and MATCH formula, as described below....

Note: the LARGE function will easily return nth values, but LARGE will return duplicates when they exist in the source data. In the example shown, the formula in E6 is: Where “rng” is the named range B5:B11 Once we have the largest value established, we create another formula that simply checks all values in the named range “rng” against the “last largest value”: Note: this is an array formula and must be entered with control + shift + enter....

These formulas use the structured references available in Excel Tables. The formula in E5 subtracts the mileage in the row above from mileage in the current row to calculate distance (miles driven) since the last gas stop: The SUM formula is used only to prevent errors on the first row, where the row above contains a text value. Without SUM, the first row formula will return the #VALUE error. The SUM function however will treat the text in C4 as zero and prevent the error....

Fields The pivot table shown is based on two fields: State and Color. The State field is configured as a row field, and the Color field is a value field, as seen below. In the Pivot Table, the Color field has been renamed “Colors”, and “Summarize values by” has been set to “Distinct count”: Data model When the Pivot Table is created, the “Add this data to the Data Model” box is checked....

In the example shown, the formula in B4 is: Which returns a random number from the numbers provided. In this case, we are providing four numbers as options: 25,50,75,100, so we need to give CHOOSE a number between 1 and 4. To generate this number, we use RANDBETWEEN, a function that returns a random integer based on a lower and upper bound. Since we are only working with 4 values in CHOOSE, we supply 1 for the bottom number and 4 for the top number....

where “names” is the named range B5:B11, “rand” is the named range C5:C11, and “sort” is the named range D5:D11. The RAND function generates a random value at each row. Note: RAND is a volatile function and will generate new values with each worksheet change. The second helper column holds the numbers used to sort data, generated with a formula. The formula in D5 is: See this page for an explanation of this formula....

Because dates in Excel are just serial numbers (the first date in the standard Excel date system is January 1, 1900), you can adjust dates just adding or subtracting values. To solve this formula, Excel just adds 1 to the date in B6. The first formula therefore returns a new date of 1/31/2000, one day later than the starting date. Once the first formula is entered, it is copied down as far as needed....

In this first example, we have a big set of data that contains a lot of blank rows. If I move to the last cell, you can see that there’s over 36,000 rows of data, and, looking at the count, just over 33,000 rows actually contain data, which means over 3000 rows are blank. To quickly remove these blank rows, I can use Go To Special. First, pick a column that should always contain data....

Excel has a large number dialog boxes that you will use frequently. Some examples include Format Cells, find and replace, spelling, paste special, go to special, and many more. On a Mac, you have a more limited ability to select controls in dialog boxes, but it is possible to drive these dialogs entirely from the keyboard. First, to allow you to access all dialog controls, you need to enable a preference....