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…
The Problem When pasting a Picture (Enhanced Image or Windows Metafile) into Powerpoint, the picture may turn mushy, fuzzy, blurry and even unreadable when being printed. This usually only happens if you put a gif, jpg, or raster image as part of the cells that are pasted into Excel. Typically, the fuzziness is not noticeable on screen,…
3 convenient functions in Excel can change the capitalization of all texts: UPPER – Converts all text to upper case. For example =upper(“McDonalds”) will turn out to be MCDONALDS LOWER – Converts all text to lower case. For example =lower(“McDonalds”) will turn out to be mcdonalds PROPER = Converts first letter of each word to…