Miscellaneous Articles: Excel and Google Sheets

Excel allows us to filter values in an array with the IF, ISNUMBER and MATCH functions. This step by step tutorial will assist all levels of Excel users in filtering in or out some specific values in a data set.   Figure 1. Final result: Filter values in array Final...

While working with Excel, we are able to display pre-sorted values in a certain order by using the INDEX, MATCH and ROWS functions.  This step by step tutorial will assist all levels of Excel users in displaying sorted values with the aid of a helper column.   Figure 1. Final...

We can use the LOOKUP function and the TEXT function to get the last entry by Month and Year in a data. The steps below will walk through the process. Figure 1: How to Get Last entry by Month and Year General Formula =LOOKUP(2,1/(TEXT(dates,"mmyy")=TEXT(A1,"mmyy")),values) Formula =LOOKUP(2,1/(TEXT($B$4:$B$11,"mmyy")=TEXT(E6,"mmyy")),$C$4:$C$11) Setting up the Data...

Figure 1. of Date Associated with Last Entry in Excel. In order to determine a calendar date corresponding with the last data entry in our Excel sheet, we can used the Excel LOOKUP Function. This article will step through the process. Generic Formula =LOOKUP(2,1/(row<>""),header) Where row is the total range...

Excel offers some great tools for mathematical calculations. One of these is to calculate the cube root of a number. To find the cube root of a number, we can use the caret operator (^) or the POWER function. In this tutorial, we will learn how to calculate the cube...

While working with Excel, we are able to create pivot tables and obtain specific data such as subtotals and grand totals by using the GETPIVOTDATA function.  This step by step tutorial will assist all levels of Excel users in getting pivot table subtotals grouped by date.   Figure 1. Final...

To get the first entry by month and year in Excel we can use the combination of INDEX, MATCH and TEXT functions. A formula should be defined as array and dates should be sorted out in the ascending order. This step by step tutorial will assist all levels of Excel...

We can use an array formula that is based on IF and MODE function to calculate a conditional mode with one or more criteria. This post looks at how we can use these two functions to calculate conditional mode with criteria. Figure 1: How to calculate conditional mode with criteria...

If we want to change the orientation of a table, that is to change the rows to columns, we can use the TRANSPOSE function. This post provides a clear guide on how you can flip the orientation of an excel table. Figure 1: How to flip table rows to columns...

Excel offers many tips and tricks to help its users. One of them is to create a hyperlink to the first match. We can use the HYPERLINK, CELL, INDEX and MATCH functions to create a hyperlink to the first match. It extracts the first match in a lookup and creates...