If you have two lists of numbers and you need to ensure they are identical there is a simple formula that can confirm they match.
Tag Archives: data
Working with Imported Dates and Times in Excel
Date data imported from other systems can include times. This can make lookup and other calculations difficult. One function can make removing or extracting time easy.
Enabling PowerPivot
XL2013 and XL2010
In my latest free webinar I provided a brief demonstration of PowerPivot in Excel 2013. I forgot to show you how to enable it. Luckily its easy.
Pasting Data to the Bottom of a Formatted Table
One trap to avoid
Formatted tables are a great feature in Excel. They were expanded and renamed in Excel 2007 (formerly called Lists) and their use offers many advantages.
Generating Random Text Entries in Excel
Create random first and last name combinations
In my previous blog post I discussed generating random numbers in Excel. What about generating random text? E.g. generating random names for testing or training purposes.
Pivot Tables and Different Date Grouping
When you use date grouping (by months) in one Pivot Table report it affects other Pivot Table’s date reporting from the same data. There is a work around to allow you to have daily, monthly and quarterly Pivot Table reports.
Filtering Tricks in Excel
Excel’s Filtering feature (formally called AutoFilter) is quick and easy to use, but it doesn’t always provide enough solutions to your filtering needs.
The New Slicer in Excel 2013
Slicers are a graphic filtering tool added in Excel 2010. They allow you to filter Pivot Tables. Excel 2013 added a new slicer that makes filtering by dates a lot easier.
September 2014 Excel Yourself article
Check out my September 2014 Excel Yourself article, video and companion file are now online at the magazine site. The article covers converting a badly laid out report into a structured data layout using a formula.
The video can be viewed from the media section at the bottom of the page.
Click here to view the article.
Excel and Side Ways Sorting
Yes, it is possible
I found out something new from one of my webinar attendees recently. You can sort sideways in Excel.
Techniques to Paste Values in Excel
Lose the formulas and keep the values
Sometimes in Excel you need to paste just the values from copied cells. You don’t want the formulas. You may have created temporary formulas that you need to replace with their values. You may need to capture the current values, make some changes and then compare the new values with their old values to see the difference.
Excel 2010 Search Filter Option
When filtering in Excel you will be shown all the entries in a field. Unfortunately, if there are hundreds of unique items this can mean a lot scrolling to select the correct item to filter by.
Excel and Grouping
The term grouping in Excel has many different meanings, probably more than any other term in Excel.
Excel Format as Table Part 2 [VIDEO]
The Format As Table feature has many useful features that are worth taking advantage of. The previous post listed them. The video of this blog is shown at the bottom of the post.
Format As Table in Excel – Part 1
Tables rule - see why
Excel 2007 updated a little used feature of Excel 2003 called Lists to create the functionality behind the Format As Table icon on the Home Ribbon tab.
Filtering Tables in Excel 2013 with Slicers
Sometimes when you apply a filter to a table it isn’t obvious what filter has been applied. This is the case when you are filtering by multiple entries in a field.
Slicers were introduced in Excel 2010 to filter Pivot Tables. Filtering a Pivot Table has the same issue when filtering by multiple criteria in a single field. Click here to see a post on these Slicers.
Slicers’ abilities were expanded in Excel 2013 to include filtering formatted tables. (A formatted table is one created using the Format as Table icon on the Home Ribbon tab or the Table icon on the Insert Ribbon tab).
The advantage with using a Slicer is that it is visual. It shows the filter being applied. A normal filter is hidden once selected.
The disadvantage with Slicers is the filters are limited to selecting or deselecting entries in the list. There is not the flexibility provided by Excel‘s normal filtering options.
Adding a Slicer
In the Design Tab when a formatted table is selected there is an Insert Slicer icon.
A dialog displays allowing you to select fields to add Slicers.
To select multiple items hold the Ctrl or Shift keys whilst clicking the entries.
As you can see the Slicer shows the states being filtered.
To clear the filter click the icon on the top right of the Slicer.
Excel Drop Down Selection and Free Form Entry
Flexible data entry
When creating a drop down selection you usually want to restrict the user to certain entries. There are cases however when you want to allow the user to choose an option or allow them to type in their own entry.
Fixing Excel’s Pivot Table headings
Say goodbye to Sum of
Pivot Tables are incredibly powerful and easy to use. Unfortunately their headings can include the terms “Sum of” or “Count of”. This is not always what you’d like to present to users. A macro to the rescue.
Importing Date Data Into Excel
Dates and CSV files
Sometimes when you import data via a CSV (Comma Separated Values) file or a TXT file, the dates don’t always import very well.
Quick as a Flash with Excel 2013
A new feature that's not perfect yet
Excel’s brand new Flash Fill feature can be a time saver, but it’s not fool proof. Flash Fill is a new feature in Excel 2013.
Slice and dice in Excel
Excel 2010 has Slicers
Excel 2010 introduced a new filter interface for Pivot Tables called Slicers. This feature improved the filtering process for Pivot Tables and made filtered selections more visible.
Excel 2013 – first impressions
New features
I installed Excel 2013 last week and have had a bit of play.
Note: you need Windows 7 or later to run Excel 2013.
You too can be unique in Excel [VIDEO]
A uniquely useful technique
When dealing with data lists in Excel it is a common requirement to extract the unique entries from a field. Excel has a built-in feature that will create a unique list.
What to look out for in Excel’s VLOOKUP function
An easy fix for a common problem
The VLOOKUP function is a popular method for extracting data from data lists. Its effectiveness depends on the quality of the data in the data list. I have had many questions from CPAs over the years asking why their VLOOKUP functions don’t work, when it all looks ok.