highlight123

Highlight a Sample In a Range or Text that contain certain Values

Crispo Mwangi | 14-Jan-17 | Leave a Comment

Learning how to use conditional formatting in excel can save you a lot of time when you need to visually highlight important information in a worksheet. At basic level, it can be used to highlight duplicates, values within certain threshold, Top or Bottom N items etc.  However, to get the full potential of conditional formatting, you […]

daverage-functions

DAVERAGE vs AVERAGEIF & DCOUNT vs COUNTIF

Crispo Mwangi | 4-Dec-16 | Leave a Comment

We have been exploring on database functions for the last two articles ( EXCEL DATABASE FUNCTION and EXCEL DSUM FUNCTION.) This article will examine  DAVERAGE & DCOUNT and how they compare with their equivalent AVERAGEIFS & COUNTIFS. Will the Database functions prevail in terms of speed over their IFS equivalent? DAVERAGE vs AVERAGEIFS For example using below data compute the […]

dsum

EXCEL DSUM FUNCTION

Crispo Mwangi | 9-Nov-16 | 2 Comments

If you are new to Database Functions, read this Introduction to Database Functions first before you continue. In this article we shall look into the uses of DSUM function which is one of the EFFICIENT Excel sum functions with criteria. Below are some of its uses; 2 way lookup Summarizing Results based on criteria 2 Way […]

database-functions

EXCEL DATABASE FUNCTION

Crispo Mwangi | 5-Nov-16 | Leave a Comment

Since their introduction in Excel 2007, DataBase functions have remained Overlooked & Underutilised. This is an Introduction in a  Series of articles whose aim is to demystify these Database functions. Database functions include; DSUM, DAVERAGE, DCOUNT, DCOUNTA, DMAX, DMIN, DGET, DPRODUCT, DSTDEV, DSTDEVP, DVAR, DVAR. All these functions works basically the same way and have […]

hlookup2

HLOOKUP, INDEX & MATCH OR VBA

Crispo Mwangi | 8-Oct-16 | 3 Comments

Having done a series on Verticle lookup  it is only fair to touch on Horizontal lookup. HLOOKUP function is the most popular function for this kind of lookup but it has a number of limitations and thus one may need to know a few other ways you can carry out a Horizontal lookup. For example, […]

REVERSE LOOKUP

REVERSE LOOKUP USING VBA

Crispo Mwangi | 11-Sep-16 | Leave a Comment

This is the last article in the series of reverse lookup in excel. Unless you are already versed with Reverse Lookup, consider checking below 2 articles first; Reverse Lookup & Reverse Lookup with duplicates Now using VBA we are going to lookup the date, time and Jobs assigned to Joy Bell Go to Developer→Visual BAsic→Insert […]

Reverse lookup with duplicates

REVERSE LOOKUP WITH DUPLICATES IN EXCEL

Crispo Mwangi | 7-Sep-16 | Leave a Comment

It is good to be able to do a Reverse Lookup but it is Awesome if you know how to return Multiple Items. Before you continue with the current article, Please revist the Previous Article on reverse lookup as this is a continuation. Suppose you are managing a project and you have below scheduled dates, […]

reverse LOOKUP

REVERSE LOOKUP IN EXCEL

Crispo Mwangi | 3-Sep-16 | 4 Comments

In my research and tutoring excel, I have found that many people find Reverse lookup concept to be among the top 10 complicated things in excel. This article hopes to shed more light than heat in demystifying the Reverse Lookup in excel. In the normal lookup, we use the Column and/or Row header to return the value that […]

2way lookup

2 WAY LOOKUP IN EXCEL

Crispo Mwangi | 20-Aug-16 | 2 Comments

The ability to lookup values is a MUST have skills for all Excel users. If you cannot lookup you cannot excel in data analysis. In this article, I will show you  5  ways you can do a 2-way lookup in Excel. 1. USING SUMPRODUCT For example, using below sales data lookup and sum the total quantity for ALL […]

lookup and Sum

LOOKUP and SUM IN EXCEL

Crispo Mwangi | 7-Aug-16 | 1 Comment

Array fomulas comes in handy when you want to lookup and sum large amount of data. Array formulas replaces multiple normal formulas,performs multiple calculations and returns a single value as a result. For example using  below Main sales data in Quantity and a lookup price table, Calculate total sales for customer Brosina Hoffman?   There […]