Data forms the backbone of any analysis that you do using Excel. And there are thousands of things that can go wrong with the data – be it the structure, extra spaces, wrong placement, different formats, and so on...
In this blog post, I will show you 10 simple ways to clean data in Excel:
- Getting rid of extra spaces.
- Select and treat all blank cells.
- Converts numbers stored as text into numbers.
- Remove duplicates.
- Highlight errors.
- Change text to lower/upper/proper case.
- Use Text to Columns to parse data in Excel.
- Spell check.
- Deleting all formatting.
- Use Find and Replace to clean data in Excel.
Full article: 10 super neat ways to clean data in Excel spreadsheets
In this post, we'll explore a method for generating a drop-down that contains a unique list of choices derived from a table column with duplicate values.
This technique involves creating a unique list in a worksheet with a PivotTable. The PivotTable is an intermediate step that creates a list that we can use to feed the data validation drop-down list.
Full article: Unique Data Validation drop-down from duplicate table data
Microsoft Excel's worksheet password protection options have been known to trip people up from time to time. Mainly because there are just so many option combinations to choose from! Hopefully by the end of this article you will be comfortable with all the various restrictions you can place on other users who may be working in or viewing your spreadsheets.
Full article: The various ways to password protect Excel worksheets
The VBA Immediate Window is an awesome tool that allows you to get immediate answers about your Excel files, and quickly execute code. It is built into the Visual Basic Editor, and has many different uses that can be very helpful when writing macros, debugging code, and displaying the results of your code.
This post will explain 5 different uses for the Immediate Window:
- Get info about the Active Workbook.
- Execute a line of VBA code.
- Run a macro.
- View
Debug.Printinfo. - Get or set a variable's value.
Full article: 5 ways to use the VBA Immediate window in Excel
This article describes a technique for adding images to the labels on a chart.
Also read the article's comments, for additional suggestions.
Full article: Add pictures to a chart axis
The Worksheet Object has a UsedRange property. Normally, we should be able to use this property so that we can quickly identify the entire UsedRange on the Worksheet without having to jump through a lot of hoops.
However, a false positive occurs when we test for data and Excel tells us that there is data when in fact there is not. This article develops a VBA function for finding the true used range of a Worksheet.
Full article: Excel Used ghost
In its simplest form Excel's GETPIVOTDATA function enables you to extract values from a PivotTable report, but if you're like me when you first came across the GETPIVOTDATA function you were less than pleased with the results. Understandably so, because in it's default form it's quite inflexible.
However, the benefit in using GETPIVOTDATA, as opposed to a regular cell reference, is huge in terms of reducing your ongoing workload in maintaining your reports. The trick with leveraging GETPIVOTDATA power is to replace the hard keyed arguments with nested formulas so the GETPIVOTDATA formula becomes dynamic.
Full article: Excel GETPIVOTDATA function
Chart events can make it much easier for people to use your programs, and add interactivity to your charts.
In this article, you'll find out how useful chart events can be. They can extend the user interface of the programs you write, making it easy for users to identify points for your program to work on.
With a little ingenuity, you can create powerful applications. Once you've experimented with chart events, you'll think of many ways to enhance your projects.
Full article: Chart events in Microsoft Excel
One of the greatest things about Excel is that with its endless array of tools and commands it almost always offers multiple ways of achieving a certain goal.
Some Excel purists (particularly those that are familiar with Array formulas), will almost always opt in favor of formulas, whereas a pragmatic will always choose the fastest way (whatever it may be). The following example shows pragmatic and pure approaches to solving the same problem.
Full article: Pragmatism vs. purity in Excel
