# All articles in 'Excel Howtos' Category

## Lenient lookup [Advanced Formula Trick]

Published on Sep 13, 2018 in Excel Howtos, Learn Excel

We all know VLOOKUP (or INDEX+MATCH) as an indispensable tool in our Excel toolbox. But what if you want the lookups to be a little gentler, nicer and relaxed?

Let’s say you want to lookup the amount \$330.50 against a list of payments. There is no exact match, but if we look 50 cents in either direction, then we can find a match. Here is a demo of what I mean.

Unfortunately, you can’t convince VLOOKUP to act nice.

Hey VLOOKUP, I know you are awesome and all, but can you cut me some slack here?

VLOOKUP is tough, reliable and has a cold heart. Or is it?

In this post, let’s learn how to do lenient lookups.

Continue »

## Performing Maths in Microsoft Word (In an Excel Blog)

Published on Aug 28, 2018 in Excel Howtos, Huis, Posts by Hui, Word

Learn to how perform Maths on Tables of Numbers in Microsoft Word.

Continue »

## Calculate travel time and distance between two addresses using Excel + Maps API

Published on Jul 19, 2018 in Excel Howtos

Ever wanted to calculate distance using Excel  – between two locations (physical addresses)? If we know the addresses, we can go to either Google Maps or Bing Maps and type them out to find the distance and travel time. But what if you are building some model (or calculator) and want to find out the […]

Continue »

## 100+ Excel Formula Examples + List

Published on Jun 27, 2018 in Excel Howtos

The first step of getting awesome in Excel is to understand that you can ask Excel do things for you. This is done by speaking a special language called as “Excel Formulas”. When you write a formula or function, you are asking Excel to figure out something from the values you have. Say you want to add up a bunch of values in a range A1:A10, you can ask Excel to do this for you by writing =SUM(A1:A10) and bingo, you get the result immediately. The best part is, if your numbers change, the answer changes too.

If you are a beginner, the world of Excel formulas can feel overwhelming. Why not? There are hundreds of different formulas in Excel.  So which formulas should you learn?

This guide gives you the answer. Here are 100+ most common Excel formula examples for every occasion. Each box describes a problem statement, an example, result, some notes and link to learn more. Use this guide to learn formulas quickly.

Continue »

## How to use Date & Time values in Excel – a handy guide

Published on Jun 20, 2018 in Excel Howtos, Learn Excel

Excel date time features are very handy and knowing how to use Excel date values can help you save a ton of time in your day to day spreadsheet chores. Let us prepare for your date with the sheet using these 10 handy tips.

Before jumping on to the tips, it helps to know how excel represents the date and time.

Microsoft Excel stores dates as sequential numbers … January 1, 1900 is serial number 1, and 20 June, 2018 is serial number 43271 because it is 43,271 days after January 1, 1900. Excel stores times as decimal fractions because time is considered a portion of a day. [Excel Help Text on Date / Time]

So you see, Date and Time are in fact numbers in Excel. Just enter a date in your excel sheet and format it as number to see its equivalent numeric value. If a date is `20-June-2018` and excel represents it as `43271`.

Continue »

## Excel Tables Tutorial & 13 Tips for making you a Data Guru

Published on Jun 13, 2018 in Excel Howtos, Featured, Learn Excel

Excel table is a series of rows and columns with related data that is managed independently. Excel tables, (known as lists in excel 2003) is a very powerful and supercool feature that you must learn if your work involves handling tables of data.

What is an excel table?

Table is your way of telling excel, “look, all this data from A1 to E25 is related. The row 1 has table headers. Right now we just have 24 rows of data. But I can add more later!”

Continue »

## Excel formula showing as text instead of actual result – How to fix the problem

Published on May 24, 2018 in Excel Howtos

Once in a while everyone is bound to come across this problem. You type a formula in a cell, then you press ENTER. Bam! nothing happens. You check if a donut chunk went in to the key board and some how jammed the ENTER key. So press it again, this time harder. But nothing. Excel […]

Continue »

## One Control Three Cells

Published on Mar 5, 2018 in Excel Howtos, Huis, Posts by Hui

Learn a technique to control multiple cells with 1 Control.

Continue »

## 5 conditional formatting top tips – Excel basics

Published on Dec 5, 2017 in Excel Howtos, Learn Excel

Time for another round of unconditional love. Today, let’s learn about conditional formatting top tips. It is one of the most useful and powerful features in Excel. With just a few clicks of conditional formatting you can add powerful insights to your data. Ready to learn the top tips? Read on.

Continue »

## How to Distribute Players Between Teams – Evenly

Published on Oct 12, 2017 in Excel Howtos, Huis, Posts by Hui, Solver

Learn how to use Solver to allocate players evenly to Teams.
A Solver Tutorial.

Continue »

## Conditional Formatting – Chart Data Labels

Published on Oct 9, 2017 in Charts and Graphs, Excel Howtos, hacks, Huis, Posts by Hui

Learn how to conditionally format Chart Data Labels without VBA

Continue »

## Copy Paste Visible Cells only (Two more ways to do it)

Published on Aug 28, 2017 in Excel Howtos, Keyboard Shortcuts, Learn Excel

Last week, we talked about how to copy and paste visible cells alone (ie exclude any filtered rows or hidden columns etc.) In the comments section many of you suggested two more ways to deal with this annoying problem. Let’s take a look them.

Continue »

## Use File > Info to quickly unprotect multiple worksheets [Quick tips]

Published on Aug 1, 2017 in Excel Howtos

Ever had a workbook with multiple protected worksheets? May be you are enterprise architect at Death Star or chief strategist at Mordor and got all the plans in a tidy little but protected workbook. Of course, you hate having to unprotect many of the worksheets every time you have a new evil plan for world domination. Don’t you worry, you can use this handy little trick to unproect en masse.

• Just open the workbook
• Go to File > Info
• Right on the top, you can see all protected worksheets and a link to unprotect them.
• Click to unprotect the ones you want to.
• Done.
Continue »

## Rounding time to nearest minute or quarter hour etc. [formulas]

Published on Jun 26, 2017 in Excel Howtos

The other day, I was building a spreadsheet to calculate FTE (full time equivalent) for staff based on hours worked on various days in a fortnight. While building the spreadsheet, I came across an interesting problem. Rounding Time to nearest minute.  We can’t use ROUND() or MROUND() to round time as these formulas aren’t designed to work with time values. Although time values are technically decimal, rounding time to nearest minute (or quarter hour etc.) can be tricky when usual round formulas. Let me share a few formulas to round time to nearest point.

Let’s say you have a time value (either user input or calculated) in cell A1.

Use below formulas to round time in A1.

Continue »

## Selective Sub-totals in Pivot Tables [Quick Tip]

Published on May 2, 2017 in Excel Howtos, Pivot Tables & Charts

Recently I was creating a pivot report with multiple items in row labels area. I had to show sub-totals, but only for one of the fields. Something like above.

How to show selective sub-totals in Pivot Tables

Continue »

### Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.