In this article, I will show you how to remove duplicates in Excel. While having duplicate data can be useful sometimes, it can also make it more difficult to understand your data. I’ll use conditional formatting to find and highlight duplicate portions of data within Microsoft Excel. Review your duplicate content and decide if you want to remove them.
Remember that when you delete duplicate values, the duplicate data is permanently deleted from your records. Before you go forward with deleting the duplicate content, I highly recommend that you copy the original data over to another worksheet. This ensures that … Read the rest
Have you ever worked in an unorganized spreadsheet? We have to admit, there is nothing more frustrating. When you scrolled down the endless rows, chances are, you couldn’t see your headers anymore. How are you supposed to keep track of where you are plotting data? This is where knowing how to freeze cells in Excel comes in handy.
If you have spent time working in a large worksheet, you may have wondered if there is a way to keep your rows and columns visible. This way, you can keep specific information visible when scrolling down or across. It is time-consuming … Read the rest
Excel’s great for displaying data and even better at crunching numbers. Here’s how to add cells in Excel to sum up totals automatically… Even when you change the numbers.
A great feature that Excel has to offer is its use of formulas. Since Excel is often used to organize numerical data for a variety of operations, it can be beneficial to have an ‘addition’ function. In this guide, we are going to discuss the various ways we can add cells in Excel.
Why is the Addition Function Useful When You Need to Add Cells in Excel?
A long time ago I was doing some reading on how you write and publish blog posts. One suggestion that helps me is the 24-hour rule. And that is to finish your post and then wait 24 hours before reading it one-last-time. After all, a blog post is going to be public, out there on the internet with your name on it, for a very long time.
The problem with this 24-hour rule is that I usually find one or more things that need to be changed. And then I have to wait another 24-hours.
I wrote a post stating that I could not find the Windows Ctrl+Home keyboard shortcut equivalent on a Mac. Well I’m here to tell you that I found the keyboard shortcut combination that does the same thing on a Mac. The Excel Gods are with me. Hallelujah!
Finding My Way Home
The key to finding this elusive keyboard shortcut is in the Keyboard Viewer. On your Mac select the Apple icon () and click System Preferences… Select Keyboard, and then make sure to click the Keyboard tab. Check the box for: Show Keyboard & Character Viewers in menu bar.… Read the rest
You can add an icon to the toolbar in Excel 2011 for your Personal Workbook Macro. In an earlier post I created a short macro to imitate the Control+Home keyboard shortcut in Excel for Windows. You can add an icon to the toolbar to run that, or any other macro with a few quick steps.
Right click on the toolbar and select Customize Toolbars and Menus… then
Click the Commands tab, then
Scroll down and select Macros from the Categories pane, and
Drag the Custom Button with a smiley face to the toolbar, then
The VLOOKUP function has four arguments and in my opinion the fourth argument always gets overlooked, yet it’s the first thing you need to know. So, like reverse polish notation, we’ll start from the inside and work out to explain each argument.
I recently began using the NETWORKDAYS function in Excel 2007, yet my file was going to be widely used by people with Excel 2003 (both are Windows versions). The NETWORKDAYS function is a standard function in Excel 2007, but with 2003 you have to install the Analysis ToolPak, otherwise you get an error.
The only way for me to avert an error with users who don’t have the Analysis TookPak installed in Excel 2003 is to use some VBA code that runs when the file is opened. I want to see if they are using a version of Excel older … Read the rest
In one of my posts on using VBA to select a column of data, I received a comment asking if the macro could be modified to select data like the screen shot you see to the left.
This data is non-sequential and consequently the CurrentRegion property won’t work. The way around this is to select the very last row in the same column, then shoot up (Ctrl + Up Arrow) to find the last data cell.
Once you know the where the column heading and last data cell is in the current column, the range can then be selected.
I want to have Excel 2011 (for Mac) open to a blank worksheet that is zoomed to 125% and it has taken a very long time to figure this out. It involves saving a template to a buried location, then manipulating the file in Finder to remove the extension. Simple, once you know the trick.
The buried location is:
Macintosh HD:Users:username:Library:Application Support:Microsoft:Office:User Templates:My Templates:
Or you can check to see where the Excel startup files are located on your computer. Just to go Excel > Preferences then select General and click the Select… box for At startup, open … Read the rest
I recently found myself with a new, loaner laptop and a brand new copy of Excel 2003 as my default spreadsheet program. This was fine until I realized there were a number of things “missing.” Like shortcuts and settings that I’ve changed over the years to make Excel serve me, instead of the other way around.
So here’s my list of things I do to “normalize” Excel 2003.
My number one pet peeve with Excel is they “automatically customize menus and toolbars based on how often you use the commands.” But my question is, “How do new users … Read the rest
I have an Excel workbook that is used in two different regions where the date format is entirely different, the US and the UK. At the top of a report worksheet I use a TEXT function to inform the user of the date range.
Here’s what a US user sees:
From: 6/6/2011 to 6/10/2011
Here is the formula:
=”From: ” & TEXT(MIN(ExtractData!A:A),”m/d/yyyy”) & ” to ” & TEXT(MAX(ExtractData!A:A),”m/d/yyyy”)
The Min and Max dates are in column A on a worksheet named ExtractData.
The problem is trying to automatically change the date format in the second argument of the TEXT Function … Read the rest