Blog


Finding the Largest Negative Number in Excel

Finding the Largest Negative Number in Excel

If you have a list of descending values and want to find the position of the largest negative number, how would you do it? First of all, when talking about negative numbers, which number is greater and which is lesser? Is -8 greater than -3, or is it less than ...
Read More
How to Backup Excel Workbooks [and Save Your Day]

How to Backup Excel Workbooks [and Save Your Day]

Who hasn't accidentally saved over their Excel workbook with changes, or was in a hurry to log-off the computer for the day and clicked "Don't Save" when closing out of Excel before realizing that was the wrong button to click? How many hours of work have you lost, or can ...
Read More
How to Use the Excel MATCH Function [In Detail]

How to Use the Excel MATCH Function [In Detail]

The MATCH function is part of Excel's group of lookup functions. This includes INDEX, LOOKUP, VLOOKUP, and HLOOKUP. Excel's MATCH function lets you tell it what item to look for in a range, and it then returns the position of that item in the range. Note that it returns, not the item, ...
Read More
Using the (Simple) Excel INDEX Function

Using the (Simple) Excel INDEX Function

When I hear the word "Index", I tend to think of the dictionary definition. Merriam-Webster defines Index as "a list of items (such as topics or names) treated in a printed work that gives for each item the page number where it may be found". For example, an index in ...
Read More
Finding the Excel Add-ins Folder Location

Finding the Excel Add-ins Folder Location

Excel Add-ins are used to expand on existing features or to provide new features in Excel. They must be installed and/or enabled to use them. The add-ins are saved in a specific folder. Use the steps below to find the Excel Add-ins folder location: 1 - Click the Develop tab ...
Read More
Adding an Excel Chart Target Line [Detailed Instructions]

Adding an Excel Chart Target Line [Detailed Instructions]

Charts are excellent tools to instantly and visually present information. The visual presentation seems to make the numbers a lot easier to understand. They're great when comparing a target or budget to a forecast or an actual expense. A target line is usually a straight line on a chart that ...
Read More
Live Transpose in Excel With an Array Formula

Live Transpose in Excel With an Array Formula

Excel's transpose feature is great for changing data in a vertical format to a horizontal format, and from a horizontal format to a vertical format. Excel's transpose, though, creates a static copy of the original data. If the original data changes, the transposed copy does not change. This may be ...
Read More
Excel SUMPRODUCT Function

Excel SUMPRODUCT Function

The Excel SUMPRODUCT function is a little known function. However it has a very fitting name because it multiplies and then returns the sum of the groups of multiplications, or creates a SUM of the PRODUCT. Starting With The SUMPRODUCT Function SUMPRODUCT(array1, [array2], [array3], ...)
How to Use the Excel CLEAN Function

How to Use the Excel CLEAN Function

The Excel CLEAN function removes non-printable characters from a string of text. This function is useful to clean imported data from a database or system that may contain non-printable characters. It can be used as a formula on a worksheet in an Excel workbook. The Excel CLEAN Function CLEAN(text)
How to Use the Excel COLUMN Function

How to Use the Excel COLUMN Function

The Excel COLUMN function returns the referenced column as a number. Here's a quick explanation of how to use the function. COLUMN Function COLUMN([reference])
  • reference = The cell or range of cells that you want to have the column numbered returned from. This is optional.
Here are a few ...
Read More
7 Time Saving Expert Excel Tips

7 Time Saving Expert Excel Tips

Microsoft Excel has so many little known and hardly used features and abilities. These hidden gems can be newly discovered by new Excel users, or can be re-discovered by long-time users who have simply forgotten about them. Often, it's the simplest feature that can be overlooked or forgotten if not ...
Read More
All You Need to Know About COMBIN

All You Need to Know About COMBIN

Your first question may be - What is COMBIN? And your second question is probably - Why would I need to know about it? COMBIN is a math function in Excel that calculates all the possible unique combinations of a set of numbers. For example, say you have a group ...
Read More
Here's a Quick Way to Concatenate Text Strings

Here’s a Quick Way to Concatenate Text Strings

When you need to concatenate text strings together in Excel, there are a few ways of doing it. There's the old standby CONCATENATE function, there's the ampersand (&) in the formula method, and with Excel 2016 there's now the CONCAT function and TEXTJOIN function. Here's a summary of the ways to ...
Read More
Three Simple Steps to Create An Excel Function

Three Simple Steps to Create An Excel Function

Unleash the power of Excel to simplify your work and save your time.  Does that sound like something you want to do? What is one way of doing this? By creating your very own Excel function. Yes, you can do it, and it's really quite easy to do! Just follow ...
Read More
Harness the (Power) of Excel VLOOKUP

Harness the (Power) of Excel VLOOKUP

Excel VLOOKUP is quite possibly one of the most popular and yet misunderstood Excel functions. It is a powerful function that can be used to simplify and automate so many things in Excel. As an Excel user advances in their skills, VLOOKUP typically becomes one of the next powerful tools ...
Read More
Excel Filters For Custom Reports

Excel Filters For Custom Reports

Excel is one of the most common software applications used for reporting on financial data. It’s power, flexibility and ease of use contribute to its use by so many users. Plus, having Excel as the most commonly used spreadsheet software in use on desktop computers around the world also helps make ...
Read More
How to Handle Errors in Excel Formulas

How to Handle Errors in Excel Formulas

I’m sure that this is everyone’s favorite subject – error handling in Excel formulas. The errors that typically show up on a spreadsheet are #N/A, #VALUE, #REF, #DIV/0!, #NUM, #NAME?, and #NULL!. There are a variety of built-in functions that Excel uses to handle errors. They do similar functions but ...
Read More
Using SUM, SUMIF and SUMIFS in Excel

Using SUM, SUMIF and SUMIFS in Excel

How many ways can Excel add thee, let me count the ways. Butchered Elizabeth Barrett Browning quotes aside, Excel has many functions for summing numbers. Last time I counted the different SUMs in Excel 2013, there were eight different SUM functions. There are SUM, SUMIF, SUMIFS, SUMPRODUCT, SUMSQ, SUMX2MY2, SUMX2PY2, SUMXMY2 ...
Read More
How to Enable PowerPivot for Excel

How to Enable PowerPivot for Excel

PowerPivot is a great tool for handling and reporting on data from a variety of sources. To use it, there a few steps that need to be taken first. Depending on which version of Excel that you have, the process is different. For Excel 2010, you first must download Microsoft PowerPivot ...
Read More
Using Excel 2016 Forecast Feature

Using Excel 2016 Forecast Feature

Excel 2016 forecast is a new feature with the ability to automatically create a forecast of your time based data. You can use this to forecast projected sales or costs for a variety of uses. It doesn't even need to be financial data - you can use it to forecast anything ...
Read More
Excel PowerPivot As VLOOKUP

Excel PowerPivot As VLOOKUP

Who doesn't love to use VLOOKUP?  Using that formula saves both time and effort when you need to analyze data. However, if you have thousands or more of VLOOKUP formulas, a performance hit to your computer's CPU can happen. Not only that, the addition of one or more columns of data will ...
Read More
Simple Data Conversion to Excel Table

Simple Data Conversion to Excel Table

How many times have you started to use Excel to analyze some data given to you, and decided that you needed to filter it to summarize it quickly for you or for someone else. Typically you would click in a cell within the data and then insert a filter. Excel ...
Read More
Fill Data Automatically in Cells Using AutoFill

Fill Data Automatically in Cells Using AutoFill

One of the benefits of Excel is how it reduces your time needed to enter items. One great feature is the AutoFill function. If you have Excel 2013 and above, there is an extra ability added to those versions of Excel. It is Flash Fill, and we will cover in another post. Autofill Features ...
Read More
Showing the Excel Developer Tab [Quick & Simple]

Showing the Excel Developer Tab [Quick & Simple]

To create and run macros and to develop in VBA, the Excel Developer Tab is used. However, the Developer Tab is not shown by default in Excel. It's quite simple to show the developer tab, which is very handy when you want to really make Excel into a more amazing ...
Read More