Excel

World Map with Indicators

The icons show the relative size of the population of the top 10 countries on the world map.  It looks cool and informative on a dashboard.  Click the above picture or here to see how to do it.

World Map with Bar Chart

Combining map with bar charts is an excellent way of showing product performance in different geographical locations.  Click the above picture or here to see how to do it.

US Map with Smiley Faces

This example uses Smiley Faces as the statistical indicators to show the relative performance of the US States.  Click the above picture or here to see how to do it.

US Thermal Map

The color of the state indicate the relative performance of such state.  The darker the color, the higher the performance.  Click the above picture or here to see how to do it.

Meter Display

Meter is an ideal visual tool to indicate the progress of a project, the usage of a resource, or the level of achievement.  Click the above picture or here to see how to do it. 

VLOOKUP and HLOOKUP functions

This article shows the best ways of using them and their limitations. Click the above picture or here to see how to do it. 

worldmappopulation
worldmapbarchart
usmapsmileyface
usmapcolor
meter
lookup
datareferencinglogo

Data Referencing

Demonstration of the uses of LOOKUP, VLOOKUP, HLOOKUP, MATCH, and INDEX functions for Data Referencing.  Click the above picture or here to see how to do it.

dynamicreferencinglogo

Dynamic Referencing

Demonstration of the uses of OFFSET function for Dynamic Data Referencing without the need of using Visual Basic codes or Macros. It can be applied to reports and charts.  Click the above picture or here to see how to do it.

msofficeshare logo
transpose logo

Data Transposing

Using Transpose Function to change the Data Layout. Click the above picture or here to see how to do it.

excel logo
dropdown logo

Create a Dropdown List by using Data Validation Command

There are 4 ways of doing it.  Click the above picture or here to see how to do it.

random logo

Generating Random Numbers

Use the RAND or RANDBETWEEN functions to generate test data   Click the above picture or here to see how to do it.

large logo

Large and Small

Find the largest or smallest numbers in a range of data using LARGE and SMALL functions.   Click the above picture or here to see how to do it.

amortization logo

Mortgage Amortization Schedules

The article includes fixed and adjustable schedules.  Click the above picture or here to see how to do it.

Date Functions

It covers several date related functions, such as EDATE, EOMONTHS, DATE, WEEKDAY, DATEDIF, DAYS360, NOW, and TODAY. Click the above picture or here to see how to do it.

date logo

Fill Data in Multiple Empty Cells

Click the above picture or here to see how to do it.

pic1 data fill
pic3 conditional formatting

Use Conditional Formatting to create Quick Graphical Presentations

Click the above picture or here to see how to do it.

goal seek logo

Use Goal Seek to reverse engineer desired results

Click the above picture or here to see how to do it.

Use Data Table for Scenario Analysis

Click the above picture or here to see how to do it.

data table logo
fifo logo

FIFO and LIFO Calculations

The FIFO and LIFO calculations affect the timing realization of profit and loss.  They can be automated by using VB Modules.  Click the above picture or here to see how to do it. 

holiday logo
workday logo
bond payment logo

US Holidays Calculations

A Visual Basic module is created to generating a list of US Holidays which is critical to many date calculations.  Click the above picture or here to see how to do it.

WORKDAY and NETWORKDAYS Functions

The two functions can be combined with the holiday list for more practical applications.Click the above picture or here to see how to do it.

Calculation of Bond Payment Dates

Dates are core of all accrued interest calculations. Click the above picture or here to see how to do it.

prime factoring logo

Prime Factoring

A visual basic module with 2 loopings is created to do the prime factoring.   Click the above picture or here to see how to do it.

lottery generator

Lottery Numbers Generator

A visual basic module is created to generate random numbers for lotteries.  Click the above picture or here to see how to do it.

powerball analysis

Use COUNTIF function and sorting to analyze the PowerBall Playout

This article investigates the occurrence frequency of the PowerBall numbers.  Click the above picture or here to see how to do it.

Create Organization Chart by using SmartArt

Click the above picture or here to see how to do it.

pic14 org chart
data table logo

Use graphical functions in Microsoft Office to create impressive graphics

Click the above picture or here to see how to do it.

flow chart logo

Create Flow Chart by using SmartArt

Click the above picture or here to see how to do it.

tranlate logo

Use Translator

The translator can help save 80% to 90% of time compared with the one that we did in the old fashioned way.  Click the above picture or here to see how to do it.

compare

Compare Multiple Numbers and Text in Microsoft Excel

Click the above picture or here to see how to do it.

text logo

Manipulate Text by using Excel Commands and Functions 

Click the above picture or here to see how to do it.

movement logo

Navigate around cells, worksheets, and files by using Visual Basic Codes 

Click the above picture or here to see how to do it.

looping logo

Different Looping Mechanisms in Excel VBA 

Looping is a key to increase efficiency by reducing the need to perform repetitious tasks.  Click the above picture or here to see how to do it.

irr logo

Internal Rate of Return (IRR) Calculations with different Accrual Basis 

A VB Module is created to calculate IRR.  Click the above picture or here to see how to do it.

crosstab table logo

Use Excel Pivot Table to create a Crosstab Table 

Group the data in a Pivot Table with certain intervals for better viewing.  Click the above picture or here to see how to do it.

mark-to-market

Create a Mark-to-Market Report by using VB module to pull data from website

This article uses stock prices for illustration. Click the above picture or here to see how to do it.

printer logo

Use VB Module to automate some printing processes

Click the above picture or here to see how to do it.

Animation in Excel using VBA 

Create Excel Animation is a lot of fun and a good way to impress your friends and co-workers.  Click the above picture or here to see how to do it.

running horse

Trucking Logistics

This Excel VB program will automate the planning of trucking logistics which is a critical part of many manufacturing industries.  Click the above picture or here to see how to do it. 

truck logistics logo
map coordinate logo

Find the X and Y Coordinates of a Shape Object

A VB module will help you easily locate the X and Y Coodinates of a Shape Object in Exce. Click the above picture or here to see how to do it.

crosstab conversion logo

Convert a Crosstab Table to Database Table Format

Data conversion is needed to import data into a database or as data source of Power BI.  Click the above picture or here to see how to do it. 

count duplicates logo

Count Duplicates as One Item

This article illustrates how to use an Excel Array Function to count duplicates as one item.  Click the above picture or here to see how to do it.

enter trigger logo

Use Enter or Tab Key to trigger a Visual Basic module

Click the above picture or here to see how to do it.

displayed cells

Select Displayed Cells for Copying

Use "Find & Select" functions to select only displayed cells and to avoid copying cells in hidden columns or rows. Click the above picture or here to see how to do it. 

close trigger logo

Create an action trigger (Open /Close a file) for a VB Module

This article illustrates how to create an action trigger like open or close a file to initiate a VB module..  Click the above picture or here to see how to do it.