Articles

All of our Excel articles can be found here

Office 365 Updates

With the release of Excel 2016, Microsoft began to release regular updates via Office 365. These updates can make significant changes to Excel, so we regularly update our books and e-books to include these changes. Excel 2016 users who are not Office 365 subscribers do not receive these updates so we carefully explain the differences

Read More »

Excel 2016 for Mac vs Excel 2016 for Windows​

Excel 2016 for Windows and Excel 2016 for (Apple) Mac are very different products You could be forgiven for thinking that the (confusingly named) Excel 2016 for Mac (suitable only for Apple computers) was “just the same” as Excel 2016 for Windows. Unfortunately, this is not the case. The Excel 2016 for Mac version has

Read More »

Why was my credit card not authorized?

There are many reasons why a credit card may not be authorized. The easiest way to find out is to telephone your card company who will usually tell you the reason. Sometimes your card may not authorize and the card company may not record the reason. This would happen if you simply typed in the card number incorrectly

Read More »

Horizontal scrolling with the mouse in Excel

Most modern mice have a ‘mouse wheel’ that also acts as a middle mouse button. Rolling the mouse wheel up and down lets you quickly scroll up and down an Excel workbook. You can zoom in and out of a workbook by using the mouse wheel while holding the  key, so you might expect there

Read More »

How to split delimited data in Excel

Delimited data is data that is separated by a certain character. Comma-Separated Values (or CSV) is one of the most common forms of delimited data and looks something like this: England,12,67,3 In this example the data would be said to be delimited using a comma as the delimiter. Here’s the same data using a hyphen (-) as

Read More »

Keyboard shortcut for autofill in Excel

If AutoFill isn’t familiar to you, you could benefit greatly from our free Basic Skills E-book, which will show you the basics of Excel formulas, functions and AutoFill. If you use AutoFill often, you’re probably used to double-clicking on the AutoFill handle to fill down a whole column of data, but you might wonder if

Read More »

Why won’t the format change for a date?

Excel uses date serial numbers to store date and time information. Date serial numbers are fully explained in our Essential Skills Books and E-books. If you encounter an Excel date that won’t change when you change its format it’s likely that your dates aren’t being stored as ‘true’ dates (ie. date serial numbers) by Excel, but rather

Read More »

How to add leading zeros in Excel

It’s quite a common requirement to add leading zeros to numbers in Excel. Code numbers often use leading zeroes and numbers are often more presentable when shown with leading zeroes. There are two different ways to add leading zeros to numbers in Excel. Using a custom format If you try entering the number 0005 into an

Read More »

Duplicating tables to other worksheets

There are a few ways to duplicate a table of data onto another worksheet. Copy and Paste The simplest way to duplicate a table onto another worksheet is to simply copy it and paste it onto the other worksheet, but this will create a separate copy that won’t update when the first table is changed.

Read More »

Privacy Warning on a macro-enabled workbook

When saving a macro-enabled workbook you may see the warning message: Privacy warning: This document contains macro, ActiveX control, XML expansion pack information or web components. These may include personal information that cannot be removed by the documents inspector. You’ll find the setting that enables and disables this message under: File > Options > Trust

Read More »

How to consolidate data and automatically keep it up to date in Excel

Consolidating multiple worksheets into a single one is a problem faced by many Excel users, and it’s a problem that has many potential solutions. Using Copy and Paste The simplest way to combine multiple worksheets together is to copy and paste the data into a single worksheet. Copying and pasting is an essential Excel skill

Read More »

Forcing Solver to return whole numbers

What is Solver? Solver is an Excel Add-In that automatically ‘solves’ mathematical problems. You could use it, for example, to calculate which products would make the best use of the materials you have on hand. While Solver is an Add-In it is included with every copy of Excel and can be enabled very easily. There

Read More »

Excel crashes after refreshing data

While modern versions of Excel are much less prone to crashing than older versions, it’s still possible for crashes and error messages to appear. Excel 2016 is a very stable application, so when crashes occur they are usually caused by factors outside Excel itself. If Excel is crashing when you try to refresh data (such

Read More »

Refreshing Slicers in Excel

Slicers create buttons that make it very easy to filter data in tables and pivot tables. Slicers are covered in depth in our Expert Skills books and E-books. When you create a Slicer, it automatically creates buttons for each item. …but consider what happens when you delete the Adventure Genre from the Data table and

Read More »

Missing Field List – Pivot Table

Bringing back the Field List after closing it When working with pivot tables you’ll need to use the Pivot Table Field List task pane a lot, but it’s easy to accidentally close the task pane and not immediately obvious how to bring it back. To bring back the Field List, click inside the pivot table and

Read More »

Can Excel Slicers be used in PowerPoint?

If you’re not familiar with Slicers they are covered in depth in our Expert Skills Books and E-books. Slicers offer an extremely quick and convenient way to filter tables and pivot tables. They make it so easy to get quick summaries of data that it would be ideal if they could be used in PowerPoint

Read More »

“Cannot group that selection” error in Excel pivot tables

If you’re seeing the “Cannot group that selection” error message when trying to group pivot tables, it is most likely because your data is invalid in some way. One of our customers sent us some example data that was causing this problem: If you look carefully you’ll see that one of the dates is the

Read More »

Create a chart for the average and standard deviation in Excel

It’s very easy to chart moving averages and standard deviations in Excel 2016, using the Trendline feature. Excel charts and trendlines of this kind are covered in great depth in our Essential Skills Books and E-books. If you’re not familiar with Excel charts or want to improve your knowledge it could be of great value

Read More »

Excel chart appears blank – not recognizing values?

This workbook was sent to us by one of our customers: He tried to create a chart from the values on the workbook, but the chart appeared completely blank. The reason this happened is that the values in column B are being stored as Text instead of as Numbers. If Excel charts are unfamiliar to

Read More »

Excel IF formulas with multiple arguments?

What is the IF function? The IF function is an Excel function that is used to test for a logical condition and return different values depending upon it. For example you could test if cell A1 contains the number 1 and return “Yes” if it does using this formula: =IF(A1=1,”Yes,”No”) If you’re unfamiliar with Excel

Read More »

How to create an auto-updating Calendar Sheet in Excel

We offer a full book and e-book showing how to create an automatically updating Excel calendar in our Excel 2016 Construction Kit #1: Calendar and Year Planner Books and E-books. Here is a screenshot from the full Calendar and Year Planner course: The Construction Kit course teaches you everything you need to know to create this fully-featured

Read More »

Calculating Loan Payment Dates

Loans are typically paid over several payments, sometimes monthly or weekly. It’s not unusual to need to calculate the dates that each of the payments will be due. Calculating loan payment dates can be a rather difficult task to do accurately, because of the inconsistent nature of dates. If you want to model a loan

Read More »

A formula or easy way to annualize data based on month

Annualizing data in Excel is easy if you understand basic Excel formulas and how annualization is calculated. If Excel formulas are unfamiliar to you, you could benefit greatly from our completely free Basic Skills E-book, which teaches the basics of Excel formulas. An Excel formula to annualize data To annualize data from a single month,

Read More »

Changing dollar signs to GBP in Excel

Changing currency symbols in Excel If all you need to do is change the currency symbol you can do so very easily using the Accounting Number Format drop-down menu on the Excel Ribbon. This drop-down menu only changes the currency symbol; it won’t convert from one currency to another. When you use this option to apply

Read More »

Using VLOOKUP, if Column 1 is blank, get value from Column 2

One of our customers presented us with this problem. He had an Excel workbook containing address data, with the usual Address 1 and Address 2 fields. He wanted to use VLOOKUP to extract the first line of the address, but he noticed that in some cases the Address 1 field had been left blank and

Read More »

Adding images to tables

Excel allows you to add background colors to cells, including gradient fills and patterns, so you might expect that it would be possible to use an image as the background of a cell. Basic cell formatting is explained in our completely free Basic Skills E-book. Formatting is covered in more depth in our Essential Skills

Read More »

A cell won’t let me enter data

This is a question that comes up quite often: you want to enter data into a cell but, for some reason, Excel won’t let you! There are a few reasons why this can happen and we’ll look at all of them in this article. The worksheet may have been protected Worksheet protection allows you to

Read More »

Macro buttons suddenly stopped working

A question we receive quite often is: “Why have my macros stopped working?” This happens most often when a macro-enabled workbook is shared with another user. There are several reasons why this can happen, and we’ll look at all of them in this article. The workbook may have been re-saved as an xlsx file Since

Read More »

Adding “+” between words in Strings

Code numbers such as invoice or product codes are common in Excel. When you work with codes like these you often need to join multiple pieces of text (or ‘strings’) together, sometimes with a symbol between them such as a hyphen or +. This example was sent to us from one of our customers: She

Read More »

Changing cell padding in Excel

If you have a background in web design or desktop publishing you might be familiar with the concept of ‘cell padding’, where tables have a certain amount of ‘padding’ space within the cells. Excel doesn’t have a concept of cell padding, but the same effect can be achieved by resizing rows and columns and changing

Read More »

Table Tools, Design Tools Group

What are Tables? Tables are a fantastic Excel feature that was first introduced in Excel 2007. They allow you to define parts of an Excel workbook as a ‘Table’, which enables many useful features, including allowing formulas to be automatically filled down when data is added and automatic formatting of the Table’s contents. You can

Read More »

How to modify or delete calculated fields in Excel pivot tables

Calculated fields allow you to create pivot table fields that carry out calculations. A common example might be multiplying a number by a percentage to calculate taxes. Our Expert Skills Books and E-books explains calculated fields in depth, but this article focuses on modifying and deleting calculated fields that already exist. This article uses one

Read More »

Calculating the original number after a percentage increase

This is a surprisingly common problem that I have encountered many times over the years. Imagine you have a value, such as 15,002.56. You know that it is 23% higher than last month’s value and you want to find out what last month’s value was. Your immediate instinct might be to multiply the number by

Read More »

Convert negative numbers to positive in Excel

Simple conversion of negative numbers into positive numbers in Excel Simply converting a negative number into a positive number is easy. All you need to do is multiply it by -1 with a formula like this: =A1*-1 If Excel formulas are unfamiliar to you, you could benefit greatly from our completely free Basic Skills E-book.

Read More »

I do not want to show data in chart that is “0” (zero)

Handling zero values in Excel charts can be a challenge, because the settings that control how zeroes are handled can be quite hard to find. If you want to improve your knowledge of Excel charts, you could benefit greatly from our Essential Skills Books and E-books. There are a few questions that we often receive

Read More »

How to hide #N/A errors in Excel

What does the #N/A error code mean? #N/A means “Not Available”. Excel formulas usually return this in situations where a requested value could not be found for some reason. One of the most common causes of the #N/A error code is the VLOOKUP function. If a VLOOKUP function can’t find a matching value it will

Read More »

How to use factorial (!) in Excel

What is a factorial? A factorial calculates the ‘product’ of all numbers less than or equal to a value. For example, the factorial of 5 would be: 5x4x3x2x1=120 In mathematical notation, factorials are usually indicated with an exclamation mark. 5! would indicate the factorial of 5. Factorials are typically used to calculate a number of

Read More »

An Excel formula to get the week of month

It’s not unusual to want to split data into weeks. Excel recognizes this and provides the WEEKNUM function, which converts a date into a number between 1 and 52, representing which week of the year the date falls into. If you’re unfamiliar with Excel formulas and functions, you could benefit greatly from our completely free Basic

Read More »

Working with HEX values in Excel

It’s unusual to need to work with Hexadecimal values in Excel, but on these rare occasions it can be challenging to figure out how Excel deals with values of this kind. If you simply enter a value like 0xABCDEF78 Excel will treat it as text and won’t recognize it as a numeric value. What is

Read More »

VLOOKUP using text strings

What is VLOOKUP? VLOOKUP is one of the functions in Excel’s vast function library. Its purpose is to search a table of values for a corresponding item and return a matching item from the table. An example might be searching a table of price data for a certain product name and returning the matching product’s

Read More »

Why you can’t effectively learn Excel 2016/365 using a Video Tutorial

Why you can’t effectively learn Excel 2016/365 using a Video Tutorial Excel 2016/365 uses the new Software as a Service (SaaS) business model In the new SaaS world, you don’t buy software any more – you effectively rent it via a subscription. The software then continually improves and changes via automatic online updates.  This means

Read More »

Separating text and numbers in Excel

You’ll sometimes encounter a situation where a cell contains both text and numbers, often when data has been imported from another system. In this example, you want to separate the names and numbers from column A into columns B and C. There are three ways that you could do this. Option 1: Flash Fill Flash

Read More »

Removing gaps between bars in an Excel chart

Excel column charts have gaps between their bars by default. Not everyone likes this default appearance, but fortunately it is possible to change the size of the gaps between bars and even remove them altogether. 1. Open the Format Data Series task pane Right-click on one of the bars in your chart and click Format

Read More »

Adding VAT to a value in Excel

Sales taxes are levied in many parts of the world. In the UK, this is known as VAT, or “Value-added Tax”, but it’s also known as sales tax, consumption tax and GST (Goods and Services Tax). Adding VAT to values is a very common requirement, but doing this in Excel isn’t very intuitive and confuses

Read More »

An Excel formula to check if a cell contains some text

Sometimes you need to search for a specific piece of text within a cell. One example I had to deal with in my professional career was a situation where a workbook combined invoice and credit note data from two different systems. In one system, credit notes had the code CN before the invoice number, but

Read More »

Subtracting percentages in Excel

If you’re new to Excel, it’s only a matter of time before you encounter a problem that requires the use of percentages. Using percentages in Excel formulas isn’t very intuitive for new users, which can result in a lot of confusion and frustration. Subtracting 10% from a number Imagine that you have the following example

Read More »

Why are the sort and filter options greyed out?

Sometimes Excel’s sorting and filtering options become grayed-out and unusable for no apparent reason. This problem doesn’t arise very often, but it can be very confusing when it does. Adding to the confusion, there are several reasons why this can happen! This article explains the reasons sorting and filtering can be disabled, and how to

Read More »

Why does Excel say “The value you entered is not valid”?

This is a question we’re asked surprisingly often. You’re editing a workbook, you enter a value into a cell, and Excel responds: The value you entered is not valid. A user has restricted values that can be entered into this cell. Recent versions of Excel have changed this message, so you might also see: This

Read More »

A Keyboard Shortcut for Merge and Center

A question we’re often asked is: What is the keyboard shortcut for Merge and Center? Merge and Center isn’t a tool that tends to be used very often, but creating a complex layout often means we need to merge and center multiple times. You would expect that Excel would offer a convenient keyboard shortcut to

Read More »

Excel Versions Explained

It’s important to know which version of Excel you are using As I write this, there have been 29 different versions of Excel, stretching from 1985 to the present day, on three different operating systems. Each version of Excel has been significantly different to the last, and the Mac and Windows versions are still very

Read More »
Scroll to Top