NH Blog - Articles on microsoft excel

Feb 15, 2021

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, 2021

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, 2021

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

Dec 04, 2020

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, 2020

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, 2020

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

Oct 30, 2020

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, 2020

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

Sep 14, 2020

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, 2020

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, 2020

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

May 28, 2021

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, 2021

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, 2021

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, 2021

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, 2021

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, 2021

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, 2021

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, 2021

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 09, 2021

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, 2021

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, 2021

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, 2021

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, 2020

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, 2020

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, 2020

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 14, 2020

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

Sep 30, 2020

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, 2020

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, 2020

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, 2020

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, 2020

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, 2020

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, 2020

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, 2020

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, 2020

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, 2020

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, 2020

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, 2020

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

May 26, 2021

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 19, 2021

Use slicers to filter table data in Microsoft Excel

Slicers were first introduced in Excel 2010 and gave us a nice easy way of filtering PivotTable data. Instead of the typical drop-down list filtering in Excel, slicers [...]

Mar 27, 2021

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, 2021

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 03, 2021

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

Nov 20, 2020

Quickly jazz up your Excel comments

Do you need your Excel comments to stand out? Did you know that it's possible to change the colour of Excel comments as well as the shape of the comment? Follow these [...]

top
Back to top