Posts

In an attempt to make Excel a smart and more user-friendly tool, there are some in-built features that end up being a little bit of an irritation. Working with decimals, you will likely face such an issue. Let me explain! If you type 10.00 in a cell in Excel, it would automatically convert this to 10 by removing the zeros after the decimal. While this is a smart thing to do, sometimes, you may want all your numbers to show a specific number of digits after the decimal, even if these are zeros....

And many times, you will need to copy and paste an entire column (or multiple columns) in Excel. It could be a copy-paste in the same worksheet, or in any other worksheet or workbook. In this tutorial, I will cover everything that you need to know about copy-pasting columns in Excel. There there are multiple ways to do it. You can choose to copy and paste an entire column or multiple columns as is, or only copy the values or formatting, or formulas from a column....

In Excel. you can quickly change the case of the text in a cell (to lower case, upper case, or proper case) using text functions. Below is an example of each type of case: Excel PROPER Function – Overview PROPER function is one of the many text functions in Excel. What Does it Do? It takes a string as the input and returns a string where the first letter of all the words has been capitalized and all the remaining characters are in lower case....

If your work involves applying the same type of formatting to data sets, using Format Painter in Excel will save you a lot of time. What is Excel Format Painter? Excel Format Painter is a nifty tool that allows you to copy formatting from a range of cells and paste it somewhere else in the worksheet (or other worksheets/workbooks). Imagine this! You get plain ugly data from a colleague, and you spend the next few minutes applying some formatting to make it beautiful....

And in most of the cases, you need these groups to be random. Today, I am sharing a random group generator template that will make it super easy for you to create a group of students/participants. All you need is the list of students or participants and specify how many groups you want to create. Random Group Generator Template Here is a demo of how this random group generator (or random team generator) template works:...

In almost all of the situations, you don’t need to know what this is, and the default settings in Excel would be enough for you. But since you are here reading this article, I’m assuming that you either have a need to know what is the difference between the R1C1 and A1 reference notations, or you’re naturally curious. In this article, I will tell you everything you need to know about R1C1 and A1 reference notation in Excel, and the Pros and Cons of each....

Just like the LOWER function, there is a similar inbuilt function in Excel VBA – LCASE The VBA LCASE function takes a string as the input and converts it into a lower case string. Syntax of VBA LCASE Function Below is the syntax of the VBA LCase function ‘String’ is the text that you want to convert to lower case. You can use a text string, a range reference that contains the text string, or a variable that has the text string....

Whether you working with professional sales data, resource management, project management, or personal data, knowing how to calculate percentage change would help you make better decisions and do better data analysis in Excel. It’s really easy, thanks to amazing MS Excel features and functions. In this tutorial, I will show you how to calculate percentage change in Excel (i.e., percentage increase or decrease over the given time period). So let’s get started!...

As you add many icons to the Excel QAT, there is also a way to group related icons together. Here is a snapshot of my Quick Access Toolbar: These vertical line separators can be used to group similar icons together. For example, here I have all the object alignment icons together. Similarly, I have the data related icons (data validation and sorting) together at the end. Creating Groups in Quick Access Toolbar Right-click on the QAT icons and select Customize Quick Access Toolbar....

This is usually needed when you’re creating a project plan, where only the weekday dates can be used, and the weekend dates are holidays. In this short tutorial, I am going to show you two simple ways to quickly autofill weekday dates in a column in Excel. We’ll first look at an inbuilt feature in excel that can quickly do this value, and then we will also learn about a formula that you can use to get only the weekday dates....

A line break is something that allows you to have multiple lines in the same cell in Excel. Below is an example of a name and address dataset wherein a single line, name and different parts of the address are separated by a line break. While it looks great in the above example, sometimes, you may not want these line breaks and have a need to remove these line breaks from the dataset....

If you work with names datasets, sorting it is one of the common tasks you would have to do often. It’s quite easy to sort data alphabetically based on the full name, where Excel uses the first character of the name to sort. But what if you want to sort data by the last name in Excel? While it’s not as straightforward, it can still be done (a lot also depends on the way names data is structured)....

This tutorial covers various ways of using a single or multiple criteria in COUNTIF and COUNTIFS function in Excel. While I will primarily be focussing on COUNTIF and COUNTIFS functions in this tutorial, all these examples can also be used in other Excel functions that take multiple criteria as inputs (such as SUMIF, SUMIFS, AVERAGEIF, and AVERAGEIFS). An Introduction to Excel COUNTIF and COUNTIFS Functions Let’s first get a grip on using COUNTIF and COUNTIFS functions in Excel....

People who work with large data sets often need simple things such as inserting/deleting rows or columns. While there are already many different (and simple) ways to add rows in Excel, when it comes to inserting a blank row after every other row (or every third or fourth row), things get a bit complicated. Insert a Blank Row After Every Other Row In this tutorial, I will show you some really simple ways to insert a blank row after every row in the existing dataset (or every nth row)....

A typical summary worksheet has the names of all the worksheets in different cells and all the names also hyperlinked to these worksheets. So you can click on a cell with a sheet name (say Jan, Feb, Mar…) and it will take you to that worksheet. Additionally, there is also a hyperlink on each worksheet that links back to the summary worksheet. While my colleagues have become super efficient in doing this, it’s still a waste of time when you can do the same thing in less than a second (yes you read it right)....

Apart from the default charts that are available in Excel, there are many advanced charts that you can easily create and use in your day-to-day work. In this tutorial, I will list an example of advanced charts that can be useful when creating reports/dashboards in Excel. What is an Advanced Chart? For the purpose of this tutorial, I am considering any Excel chart type that is not available by default as an advanced chart....

Do you use the SUM function? While that’s the right way to do this, I hope you’re not manually entering the SUM formula and selecting the range to get the sum of all the values in the column (or rows). In this tutorial, I will show you a simple shortcut to Autosum values in rows and columns in Excel. Shortcut to AutoSum in Excel Let me first give you the shortcut, and then I’ll explain how it works with different datasets....

There are multiple ways to calculate the number of months between two dates (all using different formulas). In this tutorial, I will give you some formulas that you can use to get the number of months between two dates. So let’s get started! Using DATEDIF Function (Get Number of Completed Months Between Two Dates) It’s unlikely that you will get the dates that have a perfect number of months. It’s more likely to be some number of months and some days that are covered by the two dates....

Download File Watch Video – Scenario Manager in Excel One Variable Data Table in Excel. Two Variable Data Table in Excel. Goal Seek in Excel. Excel Solver. Scenario Manager in Excel can be the tool of choice when you have multiple variables, and you want to see the effect on the final result when these variables change. Suppose you have a dataset as shown below and you want to calculate the profit value:...

When to use Excel MATCH Function Excel MATCH function can be used when you want to get the relative position of a lookup value in a list or an array. What it Returns It returns a number that represents the position of the lookup value. Syntax =MATCH(lookup_value, lookup_array, [match_type]) Input Arguments lookup_value – The value for which you are looking for a match in the lookup_array. lookup_array – The range of cells in which you are searching the lookup_value....