Excel tricks & more..

Welcome Excel Users

Microsoft Excel is one of the basic skills required to get a job. In these tutorials we will guide you to learn new tricks to stand out from others and take your career to the next level.

These tricks will save you a lot of time, work effectively and efficiently. There are also useful spreadsheets and add-ins which are available for download in this website. We can also unlock your Excel VBA, worksheets and workbooks if you happen to forgot the password.
 
 

Useful Excel Tricks

Index and Match Functions
This is a must know Excel tricks. The tutorial will demonstrate to find a data with two conditions out from an array. The use of Index and Match functions bridge the shortcomings of Vlookup and Hlookup wherein these can only find a value with one criteria.
Go to Tutorial
 
Count Working Days in Excel
Microsoft Excel is also very good in date analysis. One of the function that you might need to learn is Networkdays which can count the number of working days given the start date, end date and optional holidays.
Go to Tutorial
 
Max and If Functions
This is one of the most useful trick in Excel. The tutorial will demonstrate how to get the maximum value given an array and condition.
Go to Tutorial
 
Equation Editor
One of Excel feature is inserting neatly formatted mathematical equation as a graphical object. Please note that these doesn't perform calculations but are used mainly for presentation.
Go to Tutorial
 
Circle Invalid Data
The Data Validation feature in Excel can encircle invalid entries when the criteria set was not met. This tool is very convenient in highlighting cells which have invalid data in it.
Go to Tutorial
 
Disabling Save As Function
Sometimes you wish to send a copy of your unfinished Excel spreadsheet only for review or for other reasons, it is logical to turn off some of its features. In this tutorial we will try to explain how to do disable the 'Save As' function of Excel using Visual Basic Applications (VBA).
Go to Tutorial
 
Batch Printing
Excel VBA is very powerful in automating tasks. Looping is one of the most useful feature in this category. We will demonstrate in this tutorial how to loop in a range and batch print by pressing only one button.
Go to Tutorial
 
Excel Combo Chart
Do you want to impress your boss using Excel chart that most people doesn't know? If yes, then this tutorial is best for you. There is a combo chart feature in Excel that are barely used by some people. We will break down the steps how to do this with an example.
Go to Tutorial
 
4 Handy Data Entry Tricks in Excel
These 4 Excel tricks will surely simplify the process of entering information into worksheets and work can be done a bit faster.
Go to Tutorial
 
Flash Fill in Excel
This technique will show 2 ways how to fill a column quickly using flash fill feature in Excel.
Go to Tutorial
 
Remove Duplicates
This tutorial shows how to remove duplicates in Excel. An important trick to save tons of time when working with huge data.
Go to Tutorial
 
Interpolation and Extrapolation
This trick will save you time to do interpolation and extrapolation out from a table in Excel. It is powered by VBA or Macro using a user-defined function.
Go to Tutorial
 
Highlight Every Other Row
When your table is huge, it is easier to read the data when every other row are filled with different colors. In this example, we will demonstrate how to fill colors alternately. This trick can also highlight every third row in Excel (4th row, 5th row and so on).
Go to Tutorial
 
Data Validation
The Excel Data Validation feature allows to set up rules that determine what can be entered into a cell. A custom message can also be displayed when the entered value doesn't satisfy the set conditions.
Go to Tutorial
 
Roots of Quadratic Equation
This trick will show how to define a customized function in Excel using Visual Basic for Applications (VBA). It covers in defining variables and creating simple mathematical expression in VBA. This particular example is about getting the roots of quadratic equation. It might sound difficult but it is pretty easy in solving inside Excel module.
Go to Tutorial
 
Counting Formulas
These formulas might be very basic and simple but we find these very useful. If you are doing analysis of a huge sets of data, then this is a must-learn tutorial.
Go to Tutorial
 
LINEST Function
When you have data points given and you like to get the trend of these points, it is necessary to learn Excel LINEST function. It returns statistics that describe a linear tend matching known data points, by fitting a straight line using the least squares method.
Go to Tutorial
 
Lookup Pictures
If you are familiar of Vlookup function in Excel, it does the job well in finding a value in a table with a given criteria. But have you ever wonder of looking up a picture instead of a value from a table? This tutorial will demonstrate how to do this.
Go to Tutorial
 
Multi-tool Add-in
We developed a multi-tool add-in that we believe very useful for all Excel users. It can even unlock Excel worksheets and workbooks.
Download Now
 
Timesheet with Datepicker
A spreadsheet template you might need to record the amount of work you did in a particular job. It has an easy input of dates using a date picker add-in.
Download Now
 
Monthly Budget Spreadsheet
This will calculate the monthly income and expenses which returns the budget summary. It can also determine the difference between the budgeted and the actual income / expenses per month.
Download Now
 
Tax Calculator
This calculates the semi-monthly withholding tax as well as the take home pay. Inputs are the basic salary per half of a month, deductions, other allowances and tax exemption bracket.
Download Now
 
File Manager
Download file manager spreadsheet that can rename files in batch. It can even extract mp3 tag properties.
Download Now
 
Concrete Calculator
This will estimate the volume of concrete needed, number of bags of cement, volume of sand and volume of gravel for different types of concrete elements. It covers concrete calculator for column footing, wall footing, floor slab, rectangular column, circular column and stair.
Download Now
 
Moving Loads Spreadsheet
Real-time calculation of reactions, shear forces, moments, rotation and deflection of a beam with moving loads presented with convenient graphs. Support conditions can be fixed, free, pinned and roller.
Download Now
 
Fire Resistance of Columns
Calculates the standard fire resistance of concrete columns in accordance with Eurocode BS EN 1992-1-2.
Download Now
 
Confined Concrete Strength
This follows the guideline from the paper entitled "Theoretical Stress-Strain Model for Confined Concrete" by Mander et al. It determines the concrete capacity of rectangular column due to confinement or due to the provision of lateral ties.
Download Now
 
Half Joint Design
Design of concrete half-joint connections. This spreadsheet has been verified from the calculation presented in "Strut and Tie Model" by ACI SP-208 in particular example number 2. It uses strut and tie method to calculate the stresses at the connection. The stresses are then compared to the nominal strut strength or nodes according to ACI. It also displays the required reinforcement for tie elements.
Download Now