There is no built in function to reverse a list of data in Excel. But this can be done in a few simple steps: In an empty column, put in the number 1 for the first Drag 1 down to create a list of numbers in increasing order (press Cntrl and drag down) Turn on…
This article is not for beginners, but serves as a memory aid for conditional formatting driven by formulas. For the basic stuff, go to http://www.contextures.com/xlcondFormat01.html For some examples of using conditional formatting, go to Excel: Alternate Row Colors / Color Banding, Column Colors and Checker Patterns In the conditional format, the formula will work as long as it returns…
There are a lot of shortcuts in Excel, which makes mastering all of them a daunting task for any beginner. But it will be much easier to just focus on the one that is most often used. Below are select list of shortcuts that most are guaranteed to use frequently. Please send us a note or reply if…
This is a little different from the previous article (How to delete duplicate entries or rows in Excel?), where duplicates are deleted but 1 will remain in the list as a unique entry. This article shows you how to delete all entries that has duplicates, so there is none left. All you need to do…
There are 2 “clean” methods to solve this problem without moving the cells: Excel’s filter function allows the user to filter out rows that are duplicates. Countif to detect any duplicates for deletion Solution 1: Click on the Data -> Advanced button Select “Filter the list, in-place” For “List Range”, select the data to be…
To delete empty easily: In the right most column, put in the formula =counta([highlight everything left of the cell]) Counta counts the # of cells that is not empty. So, if the entire row is empty, it will return 0. Drag and copy the formula down until the end of the area where you want…
What happens if you unintentionally delete a tab in Excel? There is no “undo” for recovering a deleted tab. The general solution is to get the last saved version of the file to recover the tab. This works for anything that you cannot “undo” in Excel. For example, actions from a VBA script. If your…
=sumproduct([values],[weights])/sum([weights]) A weighted average (mean) is similar to the arithmetic average (mean), except values to be average are given different weight or emphasis. As seen in the example below, the weighted average is much lower than the arithmetic average, because there the value 0 carries a very strong weight (50). The formula bar also shows how cells should be…
Numbers in Excel only align left, right or center. Numbers cannot be aligned to line up properly to right while being in the center of a cell, which could look awkward for some. See below for example: In the “Right Aligned” column, all the numbers are pushed too far on the right. In the “Center”…
Sometimes when we copy cells, we want to make an exact duplicate. This tutorial teaches you exactly how to do that. Typically, when a cell is copied, references in the formula in the new cells shifts relative to their location to the original cell unless the cell reference is locked. A simple example below: C2…