Excel

Dates incorrectly entered or imported as text are a showstopper. We need our dates entered as date serial numbers to be able to leverage the Date Functions, use them in math and lookups, or even use them in PivotTables. So, often the first thing we need to do is fix Excel dates formatted as text. …

Even though dates and time are actually stored as a regular number known as the date serial number, we can make use of extensive Excel date and time formatting options to display them just the way we want. We can access some quick date and time formats from the Home tab > in the Number …

The objective of this post is to teach you how Excel handles date and time and provide you with all the tools you will need. It’s designed to be read in conjunction with the accompanying Excel file, which you can download below. Download the Files Enter your email address below to download the comprehensive Excel …

As with most things in Excel, there are many ways to extract a list of unique or distinct values from a column of data. We’ll look at the options, and the pros and cons of each. Download the Workbook Enter your email address below to download the sample workbook. Get Workbook By submitting your email …

Sometimes we want to find and remove duplicates in Excel, other times we just want to identify or highlight duplicates. There are loads of ways to tackle this in Excel, so let’s look at the options. Table of Contents Find and Remove Duplicates Video Download Excel Workbook Highlight Duplicates with Conditional Formatting Identify Duplicates with …

Microsoft recently announced a project called Script Lab, previously known as 'Add-In Playground', which aims to help you learn how to write add-ins for Office. Script Lab is actually an add-in itself and runs in Excel, Word or PowerPoint and can be installed from the Office Store. Script Lab will run in Excel, Word, or …

Excel Themes are a bundle of default colors, font styles and Shape effects. There are lots of built in Excel Themes available from the Page Layout menu: Hovering over a theme will give you a preview of the effect on your workbook. You’ll notice that different fonts will have an impact on your workbook layout. …

Inserting, copying, moving, or cutting and pasting rows and columns is something we do every day in Excel. And there are loads of ways we can perform these repetitive tasks. Inserting Rows, Columns and Cells We can use the icons in the Insert menu on the Home tab: Or even the keyboard shortcut: Ctrl+Shift+= (or …

In this tutorial we’re going to look at how we can use Excel Conditional Formatting to highlight rows in a table where a field matches any item in a list. Let’s look at an example, below is our table of data: And we want to highlight the rows that contain any of the categories in …

I’ve put together this Excel custom number format guide as a resource for our members. There are loads of ways to apply custom number formats and as a result I find myself answering questions that are covered in this post on a daily basis. To be clear, number formatting in Excel is used to specify …

Using Excel Conditional Formatting to highlight matches is easy when you team it up with a data validation list like this: Download the Excel File Enter your email address below to download the sample workbook. Get Workbook By submitting your email address you agree that we can email you our Excel newsletter. Please enter a …

By Harold Graycar XL Numerics Developer of Excel for Customer Service Professionals The previous post on this topic included a method for finding the cell address for the maximum or minimum number in a table. Thanks for the correspondence  --  and especially that from Hervé, Saskia and Trevor for the questions about what happens if …

By Harold Graycar XL Numerics Developer of Excel for Customer Service Professionals When dealing with large tables containing ‘buckets’ of numbers, we often need to find the location of the largest number, the smallest number, etc. There are a couple of techniques in Excel for doing this: By eye: using conditional formatting etc. By formula: using …

One of the nice features of Excel Tables is the banded row formatting, which makes it easier to read and scan your data. Unfortunately Excel Tables aren’t efficient with large data sets (over 100k rows), but we can replicate the banded rows with Conditional Formatting, and we can toggle it on and off at the …

An awesome new feature available in Office 365 are Icons. You’ll find Excel Icons in the Insert tab of the ribbon in the Illustrations group: There’s a gallery of icons available and they’re grouped into categories to make them easily searchable: Update 1: You can use ALT Codes to enter 1000's of symbols too. Download …

Using Excel Styles in your workbooks can not only make formatting them quicker and easier, but also result in more professional and easier to use workbooks. What are Excel Styles Excel Styles are simply a group of formatting settings that can be applied in one fell swoop at the click of a single button. Those …

The Excel Clipboard is an extremely useful tool that’s very well hidden. Most of us are familiar with being able to paste the last item copied as many times as we like until we press ESC, or copy something else. But few of us know that you can actually access the last 24 items copied …

Around this time last Year I created an Advent Calendar in Excel: To Count Up or To Count Down? Now, for me an Advent Calendar had always counted down to Christmas day, but to my surprise some people counted up, or rather counted the dates. That is they read the numbers on the calendar as …

I bet there's been the odd time you've said to yourself "wouldn't it be nice if Excel had..." Or maybe even "I really hate how Excel...." Well, now you can have your ideas heard by the very people responsible for developing Excel. Excel User Voice is where you can make suggestions for new features you’d …

Excel’s Flash Fill tool is so cool that you’ll be looking for reasons to use it! Introduced in Excel 2013 to rave reviews, Flash Fill uses a form of machine-learning techniques to reformat data automatically based on one or two examples you give it. No more complex formulas or VBA, with Flash Fill you'll be …

The Excel Fill Series tool is like having a bundle of tricks at your fingertips. It can help with one of the most common tasks we do in Excel, which is to create a list of dates. Whether it’s a list of days, months or years, the Fill Series tool can easily handle them all …

Have you ever wanted to only copy or paste visible cells? For example, below I have a table containing outstanding customer invoices. I want to insert a formula in column E to insert an overdue fee for invoices outstanding longer than 31 days: However, I don’t want the formula on the subtotal rows (row 5, …

Did you know that when Excel opens a new workbook it uses a template called ‘Book’ to create the new file? This means we can create a custom default Excel workbook by replacing the template file called ‘Book’ with one containing our own formatting. How to Customize the Default Excel Workbook It’s easy to do. …

A few weeks ago one of our members, Kylie, sent me this question:   “How can I fill column E with the text ‘CURRENT’, if the Position Title in column D matches the first instance of ‘CURRENT’ in column E?” See example data below; Kylie wants to fill the cells bordered in orange: Note: I …

Excel worksheet protection is a great way to prevent people accidentally messing up the reports you’ve spent hours creating. However, it is not going to keep confidential data a secret. If someone really wants to get to your data, or see how your model was put together and all the amazing formulas you’ve used then …

Filters are one of my most used Excel tools. They’re not fancy, but they sure do earn their keep. I’ve even bestowed them with some prime real estate in my QAT: Filters allow you to quickly hide rows containing data that don’t meet your criteria. You can also apply multiple filters to one or more …