Miscellaneous Articles: Excel and Google Sheets

Have you been searching around for a clean and effective way to get the value of the last non-empty cell in Excel? Fortunately, there is a simple way to do this using the LOOKUP function. Here is an explanation as well as an example.  Find value of the last non-empty...

With a helper column, we can use a combination of the RANK and COUNTIF Functions to dynamically sort data that contains only numeric values. The steps below will walk through the process. Figure 1: Basic Numeric Sort Formula Syntax =COUNTIF(range,criteria) COUNTIF count cells with numbers, dates and certain text that...

With the combination of SUMPRODUCT and COUNTIF function Excel allows us to check if there are exact matchable values in the list. This step by step tutorial will assist all levels of Excel users in checking if the list contains duplicates. Figure 1. Check duplicates in the list Syntax of...

Many times, we need to write abbreviations of names, places, and things. This can be done easily by using inbuilt functions of Excel. In order to determine two-letter abbreviations from full state names (i.e., Los Angeles to LA), you can use a simple formula using VLOOKUP. VLOOKUP function to abbreviate...

A byte is a unit of memory size and the decimal equivalent of one gigabyte is 1,000,000 bytes.  Other units of computer storage and memory include kilobytes, megabytes, terabytes, petabytes, and many more.   This step by step tutorial will assist all levels of Excel users in normalizing size units into...

Excel allows us to create a Multiplication table formula, using the simple multiplication and correct cell fixing. This step by step tutorial will assist all levels of Excel users in creating a multiplication table. Figure 1. Multiplication table formula Syntax of the Multiplication Table Formula =$A1 * B$1 As we...

In excel, it is possible to list the most occurring numbers based on four Excel functions that include the IF, MODE, MATCH and ISNUMBER functions. This article provides a guide on how we can use these functions to list the most frequently occurring numbers in excel. Figure 1: How to...

Ratio is a way of comparing or relating one thing with other. Finding the ratio between two numbers come handy in many aspects of data analysis. We can use the GCD function in Excel to find the ratio between two numbers. In this tutorial, we will learn how to find...

Excel allows a user to write a fixed value every n months in a defined column using several functions: IF, MOD and DATEDIF. This step by step tutorial will assist all levels of Excel users to assign a certain value every n months from a defined starting date. Figure 1....

Excel allows us to build a hyperlink with a name, using the HYPERLINK and VLOOKUP functions. This step by step tutorial will assist all levels of Excel users in creating a hyperlink from name and URL. Figure 1. The result of the HYPERLINK and VLOOKUP functions Syntax of the VLOOKUP...