How to Freeze a Row or Column in Excel
Do you want to freeze a row or column in Excel? Do you find yourself constantly having to scroll back up to the top or to the left of the table to look at the headings? For example below, freezing the top row would be very useful because without row 1, we will not know […]
Dynamic Dropdown List in Excel
Dropdown List Dependent On Another Cell For a more complicated dropdown list, we can create dropdown list where the list changes depending on the value in another cell. In this example, we will create two dropdown lists. The first one is a simple dropdown list with the names of the countries (United States, Australia, England […]
Searching for a Value that has Multiple Entries in the Table
The VLOOKUP function searches for the lookup_value in the leftmost column in the table_array and stops when the first match is found. But what if there are multiple entries in the table? Have a look at an example below: There are multiple “Kate N”s in the company. Using VLOOKUP, you will only get Start Date […]
REPLACE Function – Changing Existing Text with New Text
In this article, we will have a look at the REPLACE function. We will look at how to use this function to change existing text in a string with new text. We will also look at how the REPLACE function is different to the SUBSTITUTE function and when to use each one. REPLACE Function The […]
How to Change and Substitute Specific Text in Excel
There could be various reasons why we need to change and substitute certain text in a cell. We have touched on this previously when needed to take out certain part of a string in a cell (How to Extract Part of a Text in Cells). You could be doing this to clean up data or […]
How to Compare Two Columns
There are many different ways to compare two columns. And it depends on how we want to do the comparison? Do we want to simply see if item in one column is in the second column? Do we want to count how many rows have different items? Do we want to specify which rows have […]
How to Calculate Sum or Count Based on Cell Colors
In this article we will do a step by step guide on how to add up values based on cell colors. And then we will go through how to count the number of cells that are of a particular color. We will be doing so using Visual Basic for Applications (VBA). However this method is […]
Difference Between Absolute and Relative Cell Referencing
In Excel we often reference another cell or a range of cells in a function or formula. And we drag the formula down the row so we don’t type the same formula again and again. Inevitably there will be times we want Excel to lock in a particular cell or range and there will be […]
Rounding Numbers and Decimals in Excel
If you are interested in rounding numbers and decimals in Excel, you’ve found the right article. In this article, we will go through how to round decimal numbers up and down, specifying how many decimal places we want and also rounding numbers to the nearest multiples. Rounding Decimals – ROUND, ROUNDUP and ROUNDDOWN functions There […]
Using Check Boxes in Excel
Check boxes in Excel are a useful tool in creating checklists or to-do lists. They are simple to use. Everyone knows how to use a checklist. Users only have two options: tick the checkbox or deselect it. In this article, we will go through step by step how to add check boxes in Excel. And […]
How To Use Radio Buttons In Excel
In this article we will go through how to insert radio buttons in Excel, how they work and how to use the results. In another article – How To Insert Check Boxes In Excel, we went through the key differences between check boxes and radio buttons. In that article, we also did a walkthrough on […]
Calculating Working Days in Excel
In this article, we will examine how to work with and calculate working days in Excel. This includes calculating the number of working days between two dates and also the other way around, we will also figure out what end date will be x number of days after a particular start date. This can come […]
Count the Number of Unique Values
Do you have a list of data that has some duplicates in there and you would like to count the number of unique values in the list? This is what we will go through in this article. We will go through two different ways to do this – 1) Remove duplicates and 2) with a […]
What is 3D Range in Excel?
3D Range is a useful tool in Excel that makes it easier for us to collate data across different tabs. This is particularly useful when we use a particular template over multiple worksheets and we want to collate everything into one sheet. Imagine here we have a standard template to capture data across all worksheets […]
Partial Lookup Values in SUMIF and COUNTIF
In this article we will look at how to use SUMIF and COUNTIF functions with partial lookup values. When we first learned about this, it changed the way we stored our data and the way we did reporting. It became a lot simpler. And of course the use of wildcard with lookup values is also […]
Calculating Weighted Average in Excel
Weighted average is very useful in data analysis and reporting. It paints a clearer picture of your dataset and will transform data into more accurate information. In this article, we will go through how weighted average can be calculated in Excel and go through some examples. But first we will go through what weighted average […]
SUMPRODUCT Function – What Can It Do?
If you don’t use Excel very often, you may not be familiar with the SUMPRODUCT function. Or you may not even heard of it. And even for those who have heard of it and know the basics, this function has other applications which may not be obvious at first sight. In this article, we will […]
How to Separate Names in Excel
How can we separate names in Excel into first names and surnames? And what if some names have middle names in them and some don’t? In this article we will go through two different ways on how to do that in Excel. The first method we go through is to use Text to Columns and […]
What You Should Not Use Excel For?
Microsoft Excel significantly refined over the past few decades. However, new features prompted people to use it in ways the program was not designed for. For one, Excel is not a database. Yet, many people use it as one. While the program can serve this function for small datasets, it can exceed Excel’s limitations as […]
What You Should Use Excel For?
Microsoft Excel, the topmost spreadsheet application, offers plenty of benefits for individuals and business organizations. Being one of the most popular software, people across all industries use Excel for storing, organizing, and tracking data. With various functions and formulas, making calculations on Excel becomes highly convenient. This guide will highlight the many uses and benefits […]
How to Extract Part of a Text in a Cell
When we download reports from CRMs and various systems, we can’t always control how the data will be presented. Luckily we have Excel. In this article, we will go through functions such as RIGHT, LEFT, FIND, MID and how to use these functions together to extract part of a text within a cell. RIGHT, MID […]
Copying and Pasting in Excel
Have you ever copied a cell in Excel but when you pasted it into another cell, the value was completely different? How do copying and pasting work in Excel? Did you copy the formula or the value? There are so many different “Paste” options in Excel: In this article, we will go through a few […]
Useful Hotkeys in Excel
Can you use Excel without a mouse? What are some of the functions or features you use most often in Excel? If there are features in the Excel ribbons you use frequently, you will save a lot of time if you start using hotkeys in Excel. Yes it may take time to get used to […]
Search for a Partial Value with VLOOKUP
How can we look for a partial value with VLOOKUP? We know that VLOOKUP function has the option to not look for exact match but the list would have to be sorted in order first. And even then it is not very accurate. In this article we will first look at how to do a […]
SUMIF – SUM IF Condition is Met
Similar to the COUNTIF function, SUMIF is also one of the most fundamental functions to use in Excel when it comes to reporting and data analysis. In this article, we will go through how this function works and also explain why values may not be adding up correctly. We will also apply multiple conditions in […]
Split Texts in Cells Using Text To Columns
The reason why we all love Excel is because we have a lot of flexibility in formatting and manipulating data the way we want. Very often we are restricted by the systems we use in terms of how we can see and present our data. And this is why we export reports into Excel files […]
Generating Random Numbers in Excel
There could be a wide range of different reasons why generating random numbers could be useful in Excel. In this article, we will go through how to generate a random number in Excel, how to select a random value from a list or table and how to generate a random table of numbers. Generating a […]
INDEX and MATCH – A More Advanced Lookup in Excel
In the VLOOKUP article, we have looked at some limitations with the VLOOKUP function in Excel. For example, we must start from the leftmost column and look up values on the righthand side. Similarly with HLOOKUP, we must start from the top row and look for values downwards. So if you are looking for a […]
IF Function with Multiple Conditions
In this article, we will explore how IF function works in Excel. It is simple but very effective and useful. We will also explore how to add multiple conditions in an IF function. First we will explain how an IF function works, then we will go on to combine IF function with AND and OR […]
User-Defined Functions – Writing Your Own Functions in Excel
There are over 500 built-in functions in Excel but yet you may have trouble finding one that suits your need. Are there calculations that you do on a regular basis over and over again? And you are tired of typing in that same formula in again and again every time. You can save time by […]