fbpx
Search
Close this search box.

All articles with 'named ranges' Tag

Become a Comparison Ninja – Compare 2 Lists in Excel and Highlight Matches

Published on Jun 17, 2010 in Excel Howtos, Featured, Learn Excel
Become a Comparison Ninja – Compare 2 Lists in Excel and Highlight Matches

Comparison of lists of data is something that we do all the time. Today, lets learn a few tricks that you can apply immediately to compare 2 lists using Excel. This post discusses how to compare two lists with formula based rules. If you just want to quickly highlight common values, click here. If you […]

Continue »

Automatic Rolling Months in Excel [Formulas]

Published on Apr 6, 2010 in Excel Howtos, Learn Excel
Automatic Rolling Months in Excel [Formulas]

Often when we are making spreadsheets for forecasting or planning we would like to keep the starting month dynamic so that rest of the months in the plan can automatically rolled. Don’t understand? See this example: This type of setup is quite useful as it lets us change the starting month very easily. We can […]

Continue »

Data Validation using an Unsorted column with Duplicate Entries as a Source List

Published on Feb 2, 2010 in Learn Excel
Data Validation using an Unsorted column with Duplicate Entries as a Source List

Here is a typical scenario: We want to allow only one of the pre-defined customer names in our spreadsheet. We have listed down all the customers in column B and want excel to check against this list and validate the data. But there are 3 problems. (1) Our list is not sorted alphabetically (2) It contains duplicates and (3) The list comes from external source, so we can not remove duplicates and sort the list every time.

Now how can we set up a simple data validation list that would not repeat customer names and shows them in sorted order like this.

Read the rest of this guest post by Hui to learn how to use data validation in creative new ways.

Continue »

Making a chart with dynamic range of values

Published on Oct 15, 2009 in Charts and Graphs, Excel Howtos
Making a chart with dynamic range of values

We all know that to make a chart we must specify a range of values as input.

But what if our range is dynamic and keeps on growing or shrinking. You cant edit the chart input data ranges every time you add a row. Wouldn’t it be cool if the ranges were dynamic and charts get updated automatically when you add (or remove) rows?

Well, you can do it very easily using excel formulas and named ranges. It costs just $1 per each change. 😉

Ofcourse not, there are 2 ways to do this. One is to use Excel Tables and another is to use OFFSET formula.

Continue »

Generating invoice numbers using excel [reader questions]

Published on Jul 20, 2009 in Excel Howtos, Learn Excel
Generating invoice numbers using excel [reader questions]

Learn how to generate invoice numbers, tax codes etc. using Microsoft Excel. In this example we will take a real life example shared by Michelle and findout how we can generate invoice numbers using excel formulas. Read more to learn and download the example workbook.

Continue »

Do you know these Excel 2007 Productivity Secrets (Hint: Coffee is not one of them)

Published on May 26, 2009 in Excel Howtos, Learn Excel
Do you know these Excel 2007 Productivity Secrets (Hint: Coffee is not one of them)

Do you know these excel 2007 productivity secrets? (1) How to turn on the clipboard pane so that you can do rapid copy pasting (2) How to lock a feature for repeated use (3) How to copy charts as pictures (4) How to features not on ribbon to the quick access tool bar (5) How to change the default file save settings so that excel always save files in earlier version and much more. Go ahead and read the secrets and become excel guru.

Continue »

Excel Links of the Week – Minor Changes to PHD edition

Published on Jan 26, 2009 in blogging, excel links
Excel Links of the Week – Minor Changes to PHD edition

There are some minor changes to PHD sidebar layout and feed address. Also, in this week’s featured links learn how to Understand Stock Charts in Excel, Choose the Right Chart Using this Flow Chart, Quickly Create Named Ranges using F4 Key, Print Multiple Areas by Creating a Consolidation Sheet and Get a List of Filenames in a Directory to Excel using Browser

Continue »

Excel Links of the Week [Jan 12]

Published on Jan 12, 2009 in excel links

Everyweek PHD shares some of the Excel links to help you learn better and become productive at using Excel. This weeks edition will help you with printing, creating named ranges, finding excel version.

Continue »