password-866981_640

Generating Strong Passwords or Encryption keys Using Excel

Crispo Mwangi | 24-Jun-16 | Leave a Comment

A strong password should be at least 16 characters long, include special characters, Numbers, uppercase letters and lowercase letters. A mix of all these characters makes it hard for the password to crack. Most people find it hard to create a strong password but with Excel RANDBETWEEN and CHAR functions I will show you how […]

Sumproduct Wild Cards

Sumproduct with Wild Cards

Crispo Mwangi | 18-Jun-16 | 4 Comments

The 3 wildcard characters (?*~) used in other excel formulas do not work with sumproduct. All the same SUMPRODUCT utilizes other functions (LEFT, RIGHT, FIND and MID) to give you the same results. Suppose the following is yearly financial transactions  showing  Cost Codes and Total Cost ►Find the Sum of the total cost for Sales department if […]

DateTime Value

How to Update Date or Time in EXCEL

Crispo Mwangi | 17-Jun-16 | Leave a Comment

To update/extract the date or Time part separately in a DateTime Value is a lot easier  if you understand that excel stores dates as sequential serial numbers and time as a fractional portion of a 24 hour day. With the above understanding, you can combine INT and MOD functions to perform calculations on or extract the […]

datetime image

How to Update Only Date or Time Portion of DateTime Value in SQL

Crispo Mwangi | 17-Jun-16 | Leave a Comment

Updating/Modifying date or time portion separately in a DateTime value can be a toothache in SQL. The following is a simple procedure on how to do it. In SQL Server use the DATEADD functions to easily perform calculations such as adding or subtracting days or Hours in a DateTime value. For example if you have […]

excel's OVERLOOKED AND UNDERUTILIZED 2

Excel’s Most Overlooked & Underutilized Function Part 2

Crispo Mwangi | 8-Jun-16 | Leave a Comment

Today we shall have an in-depth look at another underutilized yet powerful function especially when analyzing dates–WEEKDAY. I hope Part 1 of this series made you get acquainted with MOD function and it has become a favorite for you. Below are some of the uses of WEEKDAY function: Summarize by weekday or weekend Countif by […]

excel's OVERLOOKED AND UNDERUTILIZED

Excel’s Most Overlooked & Underutilized Function Part 1

Crispo Mwangi | 7-Jun-16 | 10 Comments

In Over  450 functions in excel, there are  some powerful but highly underutilized functions and some overly popular functions too. Functions like IF,SUM,COUNT,AVERAGE are well known by all excel users. Yet there are some functions like MOD, WEEKDAY, WORKDAY, CHOOSE, DATEDIF, DELTA, SUBSTITUTE, HYPERLINK are rarely used. This is part 1 of a series in which […]

Excel Custom Number Format

How to Customize Number Formats In Excel

Crispo Mwangi | 7-Jun-16 | 6 Comments

Looks matter both in life and data visualization—you never get a second chance to make a first impression. Numbers in excel constitute the largest data type and knowing how to format them either on Charts or on tables is a big plus towards quicker decision making. Below are some tips on custom formatting; Custom Number […]

Sumproduct Vs Other Functions

SUMPRODUCT—Excel’s Most Powerful Function

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

Have you ever looked for an Excel function that is extremely powerful, flexible and has multiple uses? The ONE function that will solve most of your daily array of problems? The ONE function that will open doors to advanced excel skills? Well! This is it—SUMPRODUCT. SUMPRODUCT can be used to: One way lookup  with multiple […]