NH Blog - Articles on Excel

Aug 29, 2018

Predict your data with Excel Forecasting

Predict your data’s future using a Forecast Sheet  (Excel 2016 – Subscription Version) Did you know that you can easily create a forecast when [...]

Jan 27, 2017

Secrets of Excel dates and numbers

This information isn’t meant to be ‘secret’ but I find that not a lot of people will tell you this about Excel. If you go back and read (or reread) [...]

Jan 19, 2017

Joining values in Excel made simpler!

One of the more common uses of Excel we see here at New Horizons is using Excel for data formatting and simple manipulation. With that in mind, there is often a [...]

Jan 12, 2017

Secrets of Excel dates

It’s not meant to be a secret, but do you know what Excel really stores dates as? I’ll give you a clue, have you ever typed a date into Excel and it [...]

Oct 28, 2016

Create a chart with a secondary axis in Excel 2016

If you have two series of data that are on very different scales you can change your chart to have two sets of axes, one on the left hand side (a primary axis) and one [...]

Oct 21, 2016

Define and solve a problem by using Excel Solver

Solver is a Microsoft Excel add-in program you can use for What-If Analysis. You can use Solver to determine the maximum or minimum value of one cell by changing other [...]

Oct 18, 2016

Do a quick forecast in Excel 2016

Excel 2016 has a very handy new feature that enables you to create a quick forecast from a list of dates and their associated figures. It also automatically plots them [...]

Oct 13, 2016

Remove all hyperlinks from an Excel sheet using VBA

You can remove all hyperlinks in an Excel sheet by writing only one statement in VBA and that’s: ActiveSheet.Hyperlinks.Delete Of course if you were to remove [...]

Oct 10, 2016

Make multiple copies of a Microsoft Excel spreadsheet in seconds

Microsoft Excel is one of the most popular and widely used Microsoft applications in businesses worldwide. The capabilities of Excel are limitless if you know how to [...]

Sep 30, 2016

Decisions made easily with Excel

So, we’re in Excel and we have to apply some conditional logic. Hopefully, you are familiar with the IF function. Basically we provide a condition that will evaluate to [...]

Sep 16, 2016

Use conditional formatting to create a Gantt Chart in Excel

Efficient Project Management involves displaying the project plan in a visual format. The tool used to do this is called the Gantt Chart. The Gantt Chart is a list of [...]

Sep 12, 2016

Excelling in Excel

Microsoft Excel is packed with features, libraries of formulas, and comes with an increasing number of intuitive abilities. Excel is the most powerful spreadsheet [...]

Aug 30, 2016

Our most popular Excel blog posts

Microsoft Excel is an extremely powerful application that allows you to have meticulous control over your data and reporting. The first step to achieving control is [...]

Aug 29, 2016

How to use Excel range names efficiently in calculations

A Microsoft Excel user can use range names in calculations which will create meaningful reports to management. While names make calculations easier to understand, [...]

Aug 23, 2016

How-to: Import Appointments from Excel to SharePoint 2013 Calendar

If you have a list of appointments in Excel and you want to append them to your SharePoint calendar follow these steps: In Your SharePoint calendar click on [...]

Feb 15, 2016

The magic of scrollbar control in Excel

By adding a scrollbar control to a Microsoft Excel sheet you can bind it to a range of values. As you scroll, the values in that range will be returned into a cell. You [...]

Feb 08, 2016

Custom number formats in Excel

While Excel has plenty of standard formats for dates and numbers, if these don’t suit what you need, you can make use of custom number formats to display your data [...]

Feb 02, 2016

Text to Columns - First Space Only

I was recently asked how the street number and street name could be split into two cells (eg 344 Queen Street to 344 in one cell and Queen Street in another) using [...]

Jan 25, 2016

Using Text Functions in Excel

Excel is mainly known for creating spreadsheets. It has many worksheet functions, like =SUM(), for performing mathematical calculations. In addition to those, there are [...]

Dec 04, 2015

Combining different chart types in Excel 2013

Have you ever had two different types of data that you wanted to show in one chart? In Excel 2013 it is much easier to create combo charts. Let’s look at the four [...]

Nov 30, 2015

Custom Data Validation Rules in Excel

Data Validation is a feature of Excel that can greatly improve the quality of data that is entered into cells by limiting what a user can type. You can create simple [...]

Nov 23, 2015

Ever typed a date into Excel and it turned into a number?

Before I explain why Excel might turn a date into a number I need to discuss how Excel stores data. When you type information into a cell in Excel, the program tries [...]

Nov 19, 2015

The best features of Excel 2013 - Part 1

NOTE: SkyDrive is now OneDrive, and SkyDrive Pro is now OneDrive for Business The first thing you will see when you open Excel is a brand new look. It is cleaner, but [...]

Oct 30, 2015

Power Map in Excel 2013

If you are using Office 365 Pro Plus or Excel 2013, you can take advantage of Power Map for Excel. Power Map is a three-dimensional (3-D) data visualisation tool that [...]

Oct 22, 2015

Absolute Cell References use in Formulas

Definition: In Excel, an absolute cell reference, like other cell references, identifies the location a cell or group of cells and are used in such things as [...]

Oct 19, 2015

Create Scenarios in Excel using cell names

Analysing data in Microsoft Excel and creating different scenarios for management will allow them to choose the best case scenario, the one that will provide the company [...]

Sep 28, 2015

Create a Chart Template in Excel

A picture says a 1000 words… or so the saying goes. Charts are a popular way for people to graphically represent data when trying to emphasise trends and generally [...]

Sep 21, 2015

Use a slicer to make better business decisions

Being able to analyse all the data in your worksheet can help you make better business decisions. But sometimes it is hard to know where to start, especially when you [...]

Sep 14, 2015

All About VLOOKUP() in Excel

Vlookup is a handy function in Excel. It is a quick and easy answer to lookup requests. It does exactly what we humans normally do when we look up something in a table. [...]

Sep 11, 2015

Creating a layout for Word and Excel

How many times have you wanted to test a layout in Word or Excel before actually putting the content in? In this post, I'll show you a couple of simple ways of [...]

Sep 08, 2015

Do the Quick Step in Outlook

In Outlook, what is a quick step? It is a way to make a common action you perform in Outlook 2010 or 2013 take a single click. It isn’t like a Rule that often happens [...]

Sep 04, 2015

Rounding Numbers in Excel, Part 2

In Part 1 of this series I discussed the worksheet functions ROUND, ROUNDUP and ROUNDDOWN. They will truly round numbers to a certain number of decimal places. But what [...]

Aug 27, 2015

Rounding Numbers in Excel, Part 1

The first thing I’d like to point out about rounding numbers in Excel is that simply hiding decimal places isn’t true rounding; the decimal places are still there, just [...]

Aug 14, 2015

Excel – Weighted Average Calculation

Calculating an average in Excel is really easy, but sometimes the business requirement calls for a more sophisticated weighted average. For example when analysing a [...]

Jul 31, 2015

Excel – Fill in the Blanks

Spreadsheets are fantastic not just for number crunching but for extracting useful information from large quantities of data. Filtering, sorting, pivot tables all allow [...]

Jul 27, 2015

Unlinking A Pivot Table From Its Source Data

You may have a situation in which you need to send someone a pivot table summary report, but you don't want to include the original data. In other words, you want to [...]

Jul 09, 2015

Using conditional formatting to highlight weekend dates in Excel

When you design an automated calendar in Microsoft Excel, you don’t need to colour the weekends yourself. With the conditional formatting tool, you can automatically [...]

Jul 07, 2015

Autofill to the Last Row Using VBA

Sometimes you require imitating the operation of Autofill to the last cell in Excel VBA. This is when you double click the little dot located at the right bottom corner [...]

Jun 18, 2015

For Each loops in Excel VBA

Unlike the traditional For Next loops, the For Each loops do not iterate a ‘set’ number of times. Simply put, this looping structure allows you to iterate through a [...]

May 29, 2015

Recursive functions in VBA

In programming, you use functions for two purposes. You either want to reuse a piece of code over and over again, therefore you encapsulate it as a function, or you may [...]

May 28, 2015

PivotTable timelines in Excel 2013

Timelines are a new feature in Excel 2013. A timeline lets you filter records in a PivotTable - it works similar to a slicer, but you'll filter by dates. Once you [...]

May 21, 2015

Remove those rogue records in Excel

When lists of data are merged, duplicates can be introduced. Fortunately, Microsoft Excel has a Remove Duplicates feature that will eliminate these rogue records. All [...]

May 18, 2015

Find a filter result without filtering in Excel

You may remember a previous blog post of mine about advanced filters previously. I recently had a student in one of our Excel Courses who was interested in filtering and [...]

May 08, 2015

Round, RoundUp and RoundDown in Excel

This article explains the three functions to round numbers in Excel - the ROUND, ROUNDUP and ROUNDDOWN function. Note: Before your start, if you round a number, you [...]

May 05, 2015

Group data in ranges of values in Excel

Let’s assume you have a column full of numbers, as shown in Figure 1 below. Let's say you need to visually group the Quantity column into 4 groups or ranges as [...]

Apr 17, 2015

Create a SharePoint list based on an Excel spreadsheet

You can create a list in Microsoft SharePoint by importing an Excel spreadsheet file, if your data is already in a spreadsheet format. When you create a list from a [...]

Apr 07, 2015

Copy visible cells only in Excel

I am often asked why it is that sometimes when you copy and paste a filtered or subtotaled range of cells in Microsoft Excel, they expand and paste even the hidden [...]

Mar 31, 2015

How to create custom lists in Excel

Did you know that Microsoft Excel is very smart? If you type the name of the month (e.g. January), it knows automatically what comes next in the series so that if you [...]

Mar 27, 2015

Excel Array Formulas (Part 1)

Excel array formulas are power tools that enable you to perform operations that in many cases can’t be done with regular Excel functions. So what are array functions? In [...]

Mar 24, 2015

Join text without using the Concatenate function in Excel 2013

Excel 2013 has a new feature called Flash Fill. This feature will allow you to join text strings from different cells very quickly without using the Concatenate [...]

Mar 09, 2015

Instant calculations in Excel

The ability to create formulas in Excel using worksheet functions is one of the applications' most important features. However, for six of these functions, it’s [...]

Mar 06, 2015

The Best Excel Keyboard Shortcut

OK, I may be exaggerating slightly with the title because the ‘best’ keyboard shortcuts are really the ones we each individually use the most, and you may do different [...]

Feb 03, 2015

Reverse engineering a nested formula in Excel

Sometimes, understanding how a nested formula in Microsoft Excel calculates the final result is difficult because there are several intermediate calculations and logical [...]

Jan 27, 2015

Three really handy Excel keyboard shortcuts

Here are three keyboard shortcuts I find really handy in Microsoft Excel. These shortcuts each do something very useful they and are often hard to achieve using the [...]

Dec 16, 2014

Excel formulas are not just for numbers

I had a student recently say that she "didn't need to know formulas in Excel" as she "didn't work with financial spreadsheets." Formulas, however, do a lot more than [...]

Dec 02, 2014

Using the 'Flash Fill' feature to apply the desired formatting in Excel 2013

Excel 2013 has the wonderful new feature called Flash Fill. This feature allows the user to split and combine text string as well as format the split or combined text [...]

Nov 24, 2014

Comparing and combining two lists using VLOOKUPs

You may remember that in my last blog post, I talked about how to do a VLOOKUP in Microsoft Excel. Today, we are going to use VLOOKUPS to compare and combine two lists [...]

Nov 20, 2014

Removing the background from a picture in Microsoft Office

If you have a picture that you want to insert into a Microsoft Office document that is perfect except for the photo-bomber or other unwanted content in the background, [...]

Nov 14, 2014

Charts that aren't charts

In Microsoft Excel, there are a couple of ways to create a 'chart that isn't a chart.' Each produces a graphical representation of data, but using different [...]

Nov 06, 2014

Evolving Office 365 plans

Making Office 365 even better for small and mid-sized businesses. On October 1, 2014, Microsoft released three new Office 365 plans tailored to meet the needs of [...]

Oct 31, 2014

Doing a VLOOKUP that finds exactly what you want

A VLOOKUP in Microsoft Excel looks up and finds a piece of information in a list, a list like the one below: The V in VLOOKUP stands for vertical because you are [...]

Oct 28, 2014

Use Office 2013 at home with an Office 365 subscription

If you find the Microsoft Office 2013 applications essential at work, then why not at home? Kids, especially, require access to applications like PowerPoint and Word for [...]

Oct 17, 2014

The mystery of mail merge in Microsoft Word

There seems to be some mystery surrounding the process of performing a mail merge in Microsoft Word. People are often daunted by the thought of having to use this [...]

Sep 30, 2014

Customise the Ribbon in an Excel workbook

We all know how to customise the Ribbon in Microsoft Excel by using the graphical user interface, but the problem with this way of customisation is that if you move the [...]

Sep 26, 2014

Dynamic charts in Microsoft Excel

Charts in Microsoft Excel are a great way of representing data graphically, but sometimes that data is subject to change and when it does change, we need our charts to [...]

Sep 22, 2014

All about autofill in Microsoft Excel

You all agree that Microsoft Excel’s Autofill feature is one of the most popular and versatile features in Excel, however not a lot of people know about its full [...]

Sep 04, 2014

Create an advanced filter in Excel

Filtering is a common task in Microsoft Excel and the normal filtering (the ‘autofilter’) can do quite a lot of what you will need. However, there are some filters that [...]

Aug 21, 2014

Dress up and present your data with Power View

Power View is a feature of Microsoft Excel 2013 that allows you to create stunning interactive visual presentations of data from multiple sources.  In this tutorial we [...]

Aug 18, 2014

How to create a simple Waterfall chart in Excel

Microsoft Excel comes with a whole heap of different chart types and it is amazing to see that Waterfall chart is not one of them. A Waterfall chart is a column chart [...]

Aug 08, 2014

Easily delete blank rows from your data using Excel VBA

Quite often, you'll be required to remove non-contiguous blank rows to join ranges of data together. This is quite tricky to do in Excel, but I've explained how to do [...]

Aug 05, 2014

Keep your Excel formulas in place with dynamic named ranges

So, you have developed an exciting formula somewhere that works perfectly by extracting some value from a specified range and you are quite proud of it. Then one day, [...]

Jul 29, 2014

Returning multiple lookups using Excel's VLOOKUP

Excel's #vlookup function, like every other Excel function, returns only one value from a table. It looks up a table to find a row that has a key value in its first [...]

Jul 25, 2014

Remove excess spaces from data in Microsoft Excel

When data is imported into Excel, excess characters sometimes come with it. Non-printing characters are the hardest to detect as they are, of course, invisible. Spaces [...]

Jul 17, 2014

Remove blank rows in Excel with this VBA code

Quite often, you will find that you may have several table ranges in your Excel worksheet separated by blank rows that are undesirable. You also might even have a big [...]

Jul 03, 2014

Combine MATCH and INDEX in Excel for a powerful tool

If you have used Excel for comparisons, reconciliations, validations or have attended our Microsoft Excel Level 2 course, you will have encountered "VLOOKUP" before. [...]

Jun 30, 2014

How to populate tables in Excel VBA

There may be occasions where you need to populate an existing table with more data, and in such occasions, the first thing you would generally think would be "Where [...]

Jun 12, 2014

Pinpoint your 3D chart's data points with drop lines in Excel

One of the frustrating things I find about Microsoft Excel, particularly when you're producing a chart, is the huge quantity of formatting options that are available. [...]

Jun 06, 2014

Sorting made easy with custom lists in Excel

Sorting is a common task in Microsoft Excel, but it does have a few specific, sophisticated options that not too many people know. One of these is the ability to sort in [...]

May 29, 2014

3 programming tips in Visual Basic

As per the Microsoft Developer Network website, Visual Basic is engineered for productively building type-safe and object-oriented applications. Here are 3 tips I've [...]

May 26, 2014

VBA Excel: Finding the last row of a worksheet (Part 2)

In my last blog post, VBA Excel: Finding the last row of a worksheet (Part 1), I explained how to use the control arrows to find the last row of a worksheet in VBA [...]

May 01, 2014

How to access Office Applications from within VBA

Did you know you can access and manipulate any Office application from within Visual Basic (VB) code? In today's blog post, we'll go through the basic steps to connect [...]

Mar 27, 2014

How to convert text to columns in Microsoft Excel

Have you ever needed to sort or filter data in Microsoft Excel but couldn't because the data have been combined into a single column rather than multiple columns? A [...]

Mar 25, 2014

Creating a drop-down list in Microsoft Excel

Nothing looks more clever than a drop-down list of options for people to choose from in your Microsoft Excel spreadsheet. Drop-down lists also makes data entry [...]

Mar 17, 2014

Enhancing the IF function with nested IF functions in Microsoft Excel

Out of all the Microsoft Excel's worksheet functions, the IF function is one of the most useful and versatile functions. Its purpose is to decide, based on a [...]

Mar 03, 2014

A Closer Look at Excel PowerPivot and Power View

I'm sure that we can all agree that Microsoft Excel is an incredible application. You can do so much with it, but broadly speaking, there are 3 main functions that Excel [...]

Jan 10, 2014

How to do an IF statement in Excel

An IF statement or an IF function is a way to ask a closed ended question that can be one of two answers, either true or false. Once the result is determined then you [...]

Jan 07, 2014

Creating Effective Lists and Tables in Excel

Excel is the perfect application for storing lists. Lists of parts, lists of products, lists of people… the list, as it were, is endless as to what you can store. And [...]

Dec 16, 2013

PowerPivot's CALCULATE function to the rescue

One of the most commonly used DAX functions in PowerPivot is the ‘CALCULATE’ function, and in my opinion, it's one of the most interesting functions because it’s a very [...]

Nov 13, 2013

How to insert a symbol in Excel without leaving your keyboard

Did you know there are multiple ways to insert a symbol in Excel without leaving your keyboard? They're all faster than the usual "Insert > Symbol" command. Here are [...]

top
Back to top