Posts

While the keyboard is generally quicker, you shouldn’t completely ignore the mouse. There are also some great time saving mouse shortcuts as well. For the ultimate in Excel efficiency, you should combine both keyboard and mouse. In this post we’ll take a look at some of the best Excel mouse time saving tips and tricks. Video Tutorial Mouse Shortcuts For The Window Close Multiple Workbooks At Once If you’ve got a ton of Excel workbooks open and you want to close them all, then it can be a pain go through and close each workbook individually....

Strikethrough font can be a good great way to visually indicate something has been deleted, removed or is no longer relevant without actually removing it from your text. This formatting option is also available in Excel, but unfortunately it’s not available directly from the ribbon commands. However it can be accessed in quite a few ways. This post will show you all the ways you can add strikethrough font to your Excel workbooks....

Excel is just a really big and flexible calculator, so it makes sense that some people use it for math problems. The factorial comes up a lot in statistics when calculating permutations and combinations of events. So it’s likely one you’ll need to calculate quite a bit when doing any statistical work in Excel. This post will show you all the ways you can find the factorial of an integer number in Excel....

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....

MOD(Range,2)=0 will create an array of Boolean values where TRUE will mean the number is even and FALSE will mean the number is not even. We then convert these TRUE and FALSE values to 1 and 0 values by multiplying the array by 1. This will give us the count of the number of even numbers in the range. In our example MOD({1.36;-2;2;9;10;7;1.33;8},2)=0 will result in the following Boolean array....

In our example ISBLANK(Range) creates the following array. MATCH(TRUE,{FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE},0) then looks for the first exact match to a TRUE value within this array. This returns the value 3 since the first TRUE value is in the 3rd position and this is our first blank cell in the range.

This can be pretty handy if you have a large workbook with hundreds of sheets and you want to create a table of contents. This method uses the little known and often forgotten Excel 4 macro functions. These functions aren’t like Excel’s other functions such as SUM, VLOOKUP, INDEX etc. These functions won’t work in a regular sheet, they only work in named functions and macro sheets. For this trick we’re going to use one of these in a named function....

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....

Syntax IFNA(Expression, Value if #N/A!) Expression (required) – This is part of the formula that is checked for an #N/A error.Value if #N/A! (required) – This is the value that is returned if the expression results in an #N/A! error. Example In this example we use the IFNA function with a VLOOKUP function. If the VLOOKUP function results in an #N/A! error (ie the lookup value was not found), then the IFNA function will return the text “Item not found”....

After a bit of investigation, I came to the conclusion that what they really needed was a custom quote app which could be built fairly easily with Microsoft PowerApps. PowerApps can easily connect to and save data to many different data sources, so after a bit more investigating, I decided I would use SharePoint lists to store all the quote data from the app. I had heard about SharePoint before, but I never really knew what it was....

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....

You’re gonna learn all the tips the pros use, so get ready for a very very long post! Download the example file with the data used in this post to follow along. Your Source Data Needs to be in Tabular Format When using a pivot table your source data will need to be in a tabular format. This means your data is in a table with rows and columns....

Microsoft Excel is used for many purposes across many industries, so it’s no wonder the need to add superscripts or subscripts arises quite frequently. Thankfully it’s very easy to insert subscripts and superscripts in Excel and there are quite a few options for this. This blog post will show you 11 easy ways to add subscripts and superscripts in Microsoft Excel. Add Subscript or Superscript from the Format Cells Dialog A lot of common formatting commands can be found in the Home tab in the Font, Alignment, or Number section....

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....