Excel is a great spreadsheet tool, but there are a few things about it that may irritate you sometimes. For example, if you add a plus sign before a number in a cell in Excel, that plus sign would just go away. The same happens when you try and add leading zeros before a number. The reason for this is that Excel considers these as redundant (which is correct). But sometimes, you may need to be able to add a plus sign before a number (especially when you’re showing changes as shown below):...

When working with text data, a common task is to make the data consistent by capitalizing the first letter in each cell (or to capitalize the first letter of each word in all the cells) In this tutorial, I will show you a couple of methods to capitalize the first letter in Excel cells. So let’s get started! Capitalize First Letter Using Formula There can be two scenarios where you want to capitalize:...

While the dollar sign is right there on the keyboard, cent symbol is a little more difficult to find and insert. How to Insert a Cent Symbol in Excel In this tutorial, I’ll show you three different ways to insert the Cent symbol in Excel: Let’s begin. Using a Keyboard Shortcut The keyboard shortcut to insert the cent symbol in Excel is ALT + 0162. You need to hold the ALT key and then press 0162 on the number keypad of your keyboard....

Imagine working on an Excel workbook with 15 worksheets, and having to constantly switch between worksheets #3 and #12. There are multiple ways to switch between worksheets in Excel, and in this tutorial, I’m going to show you some really cool and advanced tricks that will help switch between tabs a little easier and faster. Keyboard Shortcut to Switch Between Sheets (Page UP/DOWN) If you need to move between sheets in the same workbook that are not far apart (i....

While it’s quite easy to fill color in a cell in Excel (using the inbuilt option in the ribbon), it’s not the fastest way to do it. And if this is something you have to do multiple times in a day, knowing faster ways to fill color in a cell would make you more efficient and also save time. And needless to say, you can always use the shortcuts and tricks I cover in this tutorial to impress your boss and colleagues....

Conditional Data Entry in Excel Using Data Validation Here are a few examples of conditional data entry rules: Allow data entry from a pre-defined list only (using drop-down lists). Allow data entry only when the specified cell(s) are filled. Allow DATE entry between two specified dates only. You can also combine multiple conditions to create a data entry rule. This type of conditional data entry in excel can be done using the data validation feature in Excel....

Recently, I was working on setting the agenda for a meeting. I had a list in Excel where I had a list of people and the dates on which they acted as the ‘Meeting Chair’. Since there was repetition in the list (which means that a person has been Meeting Chair multiple times), I also needed to know when was the last time a person acted as the ‘Meeting Chair’....

Below is a detailed written tutorial about Excel Data Entry form in case you prefer reading over watching a video. Excel has many useful features when it comes to data entry. And one such feature is the Data Entry Form. In this tutorial, I will show you what are data entry forms and how to create and use them in Excel. Why Do You Need to Know About Data Entry Forms?...

Both – a line chart and an area chart – show a trend over time. In case you’re only interested in showing the trend, using a line chart is a better choice (especially if the charts are to be printed). For example, in the below case, you can use a line chart (instead of an area chart) Area charts are useful when you have multiple time series data and you also want to show the contribution of each data set to the whole....

A lot of Excel users hide rows and columns when they have some data that they don’t need visible. It’s a way to only keep useful data visible, and at the same time not have to delete the data you don’t need to be visible. And, if you have lots of such hidden rows/columns, it could be a pain to find and delete these hidden rows and columns (in the case when you don’t need them)....

When working with data in Excel and creating reports/dashboards, sometimes you would have a need to split a cell diagonally. This is helpful when you want to show two separate headings within the same cell – one for the row and one for the column (as shown below in cell A1). And being the super awesome spreadsheet tool that Excel is, there is an inbuilt way to easily insert a diagonal line within a cell in Excel....

Pivot Tables are great when you want to analyze a huge amount of data in seconds. It also allows you to quickly create different views of data by simply dragging and dropping. And to create a Pivot Table, you need to have the data in a specific Pivot Table ready format. In many cases, you’re likely to get the data in formats that are not Pivot Table ready. This often is the case when someone manually collects data and creates a format that is more readable by humans (not Pivot Tables)....

When to use Excel SUBSTITUTE Function SUBSTITUTE function can be used when you want to substitute text with new specified text in a string. What it Returns It returns a text string where an old text has been substituted by the new one. Syntax =SUBSTITUTE(text, old_text, new_text, [instance_num]) Input Arguments text – the text you want to change. old_text – the text string that you want to substitute. new_text – the new text string with which you want to substitute old_text....

He has some amazing articles on his website, and I am going to copy this tip shamelessly here [with his permission of course] If you have a workbook where the tab’s names are numbers (for example 1, 2, 3.. or 2020, 2021, 2022..) then you are going to love this tip. Here it is – Give the desired number to your tab and put it in round brackets – (1)....

In this tutorial, you will learn all about the Excel Paste Special feature and useful Excel Paste Special Shortcuts. What is Excel Paste Special? What happens when you copy a cell in excel and paste it somewhere else? It copies not only the content of the cell but also the format such as background color, font color, borders(as shown below). But what if you want to copy only the cell contents and not the formatting, or only the formatting and not the values, or only the formula within it?...

For example, you can add two different time values or date values or you can calculate the time difference between two given dates/times. In this tutorial, I will show you a couple of ways to perform calculations using time in Excel (such as calculating the time difference, adding or subtracting time, showing time in different formats, and doing a sum of time values) How Excel Handles Date and Time? As I mentioned, dates and times are stored as numbers in a cell in Excel....

When to use Excel ROWS Function Excel ROWS Function can be used when you want to get the number of rows in a specified range or array. What it Returns It returns a number that represents the total number of rows in the specified range or array. Syntax =ROWS(array) Input Arguments array – it could be an array, an array formula or a reference to a contiguous range of cells....

For example, you may want to know the date three months after a specific date. Thankfully, Excel has an in-built function (EDATE) that does exactly this. In this tutorial, I will show you how to use a simple formula to add or subtract months to a date in Excel. Add Months to a Date Suppose you have a dataset as shown below and you want to add the given number of months in column B to the dates in column A....

In the bell curve, the highest point is the one that has the highest probability of occurring, and the probability of occurrences goes down on either side of the curve. It is often used during employee performance appraisals or during evaluation in exams (ever heard – “You will be graded on the curve?”). Now before I jump in on how to create a bell curve in Excel, let’s get a better understanding of the concept by taking an example....

Normally, we select the worksheet tab, then spot the cell (which may involve scrolling) and then click on it. In this blog post, learn 2 quick methods to do this. #1 – Using Excel Watch Window to Monitor an Excel Cell Excel Watch Window enables you to keep a track of the values in selected cells. It is a handy feature if you have a couple of cells that you want to monitor as you work with your data....