69 Tutorial

How to Enable Freehand Electronic Signatures in Excel

Abdur Rehman 0 Excel, Microsoft, Office 05:07

How to Enable Freehand Electronic Signatures in Excel

Acommon request I’ve gotten over the years is “how can I enable someone to sign their name electronically” within an Excel spreadsheet. In ...

Convert XLS Workbooks in Two Keystrokes

Abdur Rehman 0 Excel, Microsoft, Office 04:56

<div style="background-color: white; border: 0px; box-sizing: border-box; color: #333333; font-family: Merriweather, serif; font-size: 17px; font-stretch: inherit; line-height: 32.3px; margin-bottom: 1.5rem; padding: 0px; vertical-align: baseline;">
There’s a good chance that any software you use to export data to Excel is surreptitiously making your day a little harder than it should be. This is because many programs—particularly certain popular cloud-based accounting programs—export reports to Excel in an obsolete format. In this article I’ll discuss three ways that you can convert such workbooks into a usable format, and streamline the process down to two keystrokes.</div>
<div class="advert advert_mpu-inline" style="background-color: white; border: 0px; box-sizing: border-box; color: #333333; float: right; font-family: Merriweather, serif; font-size: 17px; font-stretch: inherit; line-height: 32.3px; margin: 0px -9.375rem 1.5rem 1.5rem; padding: 0px 0px 1rem; vertical-align: baseline;">
<div class="advertise-with-us" style="background-color: #dce6eb; border: 0px; box-sizing: border-box; font-family: Lato, sans-serif; font-size: 0.75rem; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0.125rem 0.25rem; vertical-align: baseline;">
<a href="http://www.siftmedia.co.uk/portfolio/accountingwebcom" style="border: 0px; box-sizing: border-box; color: #86a3b4; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: 1; margin: 0px; overflow-wrap: break-word; padding: 0px; text-decoration: none; vertical-align: baseline; word-break: break-word; word-wrap: break-word;">Advertisement</a></div>
</div>
<div style="background-color: white; border: 0px; box-sizing: border-box; color: #333333; font-family: Merriweather, serif; font-size: 17px; font-stretch: inherit; line-height: 32.3px; margin-bottom: 1.5rem; padding: 0px; vertical-align: baseline;">
The XLS workbook format is often referred to as the Excel 97-2003 workbook format, and for good reason. Those versions of Excel supported spreadsheets with 65,636 rows and 256 columns. Modern workbook formats, such as XLSX allow over one million rows and roughly sixteen-thousand columns. However, you may not realize the depths of frustration that XLS workbooks can cause because the other side effects are subtle. First off, starting with Excel 2007 the Compatibility Checker prompt sometimes appears when you save XLS workbooks, which you can dispatch on a file-by-file basis by clicking the checkbox shown in Figure 1. As illustrated in Figure 2, you’ll run into disabled features, and Excel will block you from copying worksheets from an XLSX file into the XLS workbook.</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgi4m3Kxx6fVIVXhXZBnmnvP-y4dRYuhdtoaDO8h-_xKpl0hPNZaG0CMSTaJpS1xUbt2llj2qKtxSEU_yIZVCUi6WS-3WFT6gG4_rdkhZzgJX9ghql2haSM_WzOzA5quL3Y8s5REWB01Kk/s1600/convert_-_figure_1_0.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="222" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgi4m3Kxx6fVIVXhXZBnmnvP-y4dRYuhdtoaDO8h-_xKpl0hPNZaG0CMSTaJpS1xUbt2llj2qKtxSEU_yIZVCUi6WS-3WFT6gG4_rdkhZzgJX9ghql2haSM_WzOzA5quL3Y8s5REWB01Kk/s400/convert_-_figure_1_0.jpg" width="400" /></a></div>
<div style="background-color: white; border: 0px; box-sizing: border-box; color: #333333; font-family: Merriweather, serif; font-size: 17px; font-stretch: inherit; line-height: 32.3px; margin-bottom: 1.5rem; padding: 0px; vertical-align: baseline;">
<br /></div>
<div style="background-color: white; border: 0px; box-sizing: border-box; color: #333333; font-family: Merriweather, serif; font-size: 17px; font-stretch: inherit; line-height: 32.3px; margin-bottom: 1.5rem; padding: 0px; vertical-align: baseline;">
<strong style="border: 0px; box-sizing: border-box; font-stretch: inherit; line-height: 32.3px; margin: 0px; padding: 0px; vertical-align: baseline;">Figure 1:</strong><span style="line-height: 32.3px;"> The Compatibility Checker Prompt is the least of your concerns with XLS workbooks.</span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgAgrswuzEt8toijVjkHBxIgJ2eCM37LCEfZHFSdUYqzZkV-ZJxpcMA1igadSweJEZMsKUX48MZKAFuShvmm9VrH19kEj3ab8xf5dWfde-wiCfQi6brOMMC2E5wZCx5qhyDFAMjBx-syoY/s1600/convert_-_figure_2_0.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="222" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgAgrswuzEt8toijVjkHBxIgJ2eCM37LCEfZHFSdUYqzZkV-ZJxpcMA1igadSweJEZMsKUX48MZKAFuShvmm9VrH19kEj3ab8xf5dWfde-wiCfQi6brOMMC2E5wZCx5qhyDFAMjBx-syoY/s400/convert_-_figure_2_0.jpg" width="400" /></a></div>
<div style="background-color: white; border: 0px; box-sizing: border-box; color: #333333; font-family: Merriweather, serif; font-size: 17px; font-stretch: inherit; line-height: 32.3px; margin-bottom: 1.5rem; padding: 0px; vertical-align: baseline;">
<br /></div>
<div style="background-color: white; border: 0px; box-sizing: border-box; color: #333333; font-family: Merriweather, serif; font-size: 17px; font-stretch: inherit; line-height: 32.3px; margin-bottom: 1.5rem; padding: 0px; vertical-align: baseline;">
<strong style="border: 0px; box-sizing: border-box; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">Figure 2:</strong> Disabled features and cryptic prompts triggered by XLS workbooks often stymie Excel users.</div>
<div style="background-color: white; border: 0px; box-sizing: border-box; color: #333333; font-family: Merriweather, serif; font-size: 17px; font-stretch: inherit; line-height: 32.3px; margin-bottom: 1.5rem; padding: 0px; vertical-align: baseline;">
There’s a simple solution, as illustrated in Figure 3, which is to use the Convert command to upgrade the workbooks to a modern format. The Convert command may feel elusive, as it only appears when needed, but is easy to use:</div>
<ol style="background-color: white; border: 0px; box-sizing: border-box; color: #333333; font-family: Merriweather, serif; font-size: 17px; font-stretch: inherit; line-height: 32.3px; margin: 0px 0px 1.5rem 1.75rem; padding: 0px; vertical-align: baseline;">
<li style="border: 0px; box-sizing: border-box; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;"><strong style="border: 0px; box-sizing: border-box; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">Excel 2010 and later:</strong> Choose File, and then click Convert on the Info tab.</li>
<li style="border: 0px; box-sizing: border-box; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;"><strong style="border: 0px; box-sizing: border-box; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">Excel 2007:</strong> Click the Office button, and then the Convert command which appears under the Open command.</li>
<li style="border: 0px; box-sizing: border-box; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">Click the “Do not ask me again about converting workbooks” checkbox, and click OK.</li>
<li style="border: 0px; box-sizing: border-box; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">Click Yes on the second prompt that appears.</li>
</ol>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh6-tvAT3Z5HOu2lcUgpIA5pOI_okZIeDstGs5dxQzL7JKiQai5q7kOO_K1RTAtj_uvlya3gOgLPOCC558gN4y6zo7ds1u5-vfV0nvHbu86H7EVZf6t0p5dBxOLM-goIymIz0bVJtQYvIQ/s1600/convert_-_figure_3_0.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="222" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh6-tvAT3Z5HOu2lcUgpIA5pOI_okZIeDstGs5dxQzL7JKiQai5q7kOO_K1RTAtj_uvlya3gOgLPOCC558gN4y6zo7ds1u5-vfV0nvHbu86H7EVZf6t0p5dBxOLM-goIymIz0bVJtQYvIQ/s400/convert_-_figure_3_0.jpg" width="400" /></a></div>
<div style="background-color: white; border: 0px; box-sizing: border-box; color: #333333; font-family: Merriweather, serif; font-size: 17px; font-stretch: inherit; line-height: 32.3px; margin-bottom: 1.5rem; padding: 0px; vertical-align: baseline;">
<br /></div>
<div style="background-color: white; border: 0px; box-sizing: border-box; color: #333333; font-family: Merriweather, serif; font-size: 17px; font-stretch: inherit; line-height: 32.3px; margin-bottom: 1.5rem; padding: 0px; vertical-align: baseline;">
<strong style="border: 0px; box-sizing: border-box; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">Figure 3:</strong> Use the Convert command to upgrade your workbook to an XLSX format.</div>
<div style="background-color: white; border: 0px; box-sizing: border-box; color: #333333; font-family: Merriweather, serif; font-size: 17px; font-stretch: inherit; line-height: 32.3px; margin-bottom: 1.5rem; padding: 0px; vertical-align: baseline;">
No muss, no fuss, your XLS workbook is swapped out in place for a modern version that won’t have any of the baggage shown in Figures 1 and 2. These commands aren’t difficult to carry out, but if you’re frequently exporting reports to Excel from a platform that can only generate XLS workbooks, you’ll likely want to streamline your workflow as much as possible. This is where the two-keystroke shortcut comes into play.</div>
<div style="background-color: white; border: 0px; box-sizing: border-box; color: #333333; font-family: Merriweather, serif; font-size: 17px; font-stretch: inherit; line-height: 32.3px; margin-bottom: 1.5rem; padding: 0px; vertical-align: baseline;">
As shown in Figure 4:</div>
<ol style="background-color: white; border: 0px; box-sizing: border-box; color: #333333; font-family: Merriweather, serif; font-size: 17px; font-stretch: inherit; line-height: 32.3px; margin: 0px 0px 1.5rem 1.75rem; padding: 0px; vertical-align: baseline;">
<li style="border: 0px; box-sizing: border-box; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">Click the arrow at the end of the Quick Access Toolbar in Excel 2007 and later.</li>
<li style="border: 0px; box-sizing: border-box; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">Choose More Commands.</li>
<li style="border: 0px; box-sizing: border-box; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">Choose File Tab.</li>
<li style="border: 0px; box-sizing: border-box; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">Double-click Convert to Open Office XML Format (this allows you to skip the Add button).</li>
<li style="border: 0px; box-sizing: border-box; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">Click OK to close the Excel Options dialog box.</li>
</ol>
<div style="background-color: white; border: 0px; box-sizing: border-box; color: #333333; font-family: Merriweather, serif; font-size: 17px; font-stretch: inherit; line-height: 32.3px; margin-bottom: 1.5rem; padding: 0px; vertical-align: baseline;">
When you tap the Alt key, Excel will reveal the shortcut number for the new Convert command, which is predicated on the order that the command appears within your Quick Access Toolbar. If the Convert command is the fourth icon on the toolbar, its keyboard shortcut will be Alt-4. Much of the time in Excel this new command will be disabled, but any time it lights up you’ll want to do these two steps:</div>
<ol style="background-color: white; border: 0px; box-sizing: border-box; color: #333333; font-family: Merriweather, serif; font-size: 17px; font-stretch: inherit; line-height: 32.3px; margin: 0px 0px 1.5rem 1.75rem; padding: 0px; vertical-align: baseline;">
<li style="border: 0px; box-sizing: border-box; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">Press Alt-4 (or the number associated with the command)</li>
<li style="border: 0px; box-sizing: border-box; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">Press Enter to close the “Do you wish to close and reopen” prompt.</li>
</ol>
<div style="background-color: white; border: 0px; box-sizing: border-box; color: #333333; font-family: Merriweather, serif; font-size: 17px; font-stretch: inherit; line-height: 32.3px; margin-bottom: 1.5rem; padding: 0px; vertical-align: baseline;">
The command will now be disabled again. If you encounter an additional prompt regarding converting workbooks, click the checkbox to suppress that prompt in the future.</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiYi462Vv0S5e-JaoukkEEjN8WJdKm00olJ0Iegl7y5n02Khy9eTERjhVQ8f__ebD9N7EkXq14leA8vnY1_inpXSPJvrOuLcXtsOQFCq1aoaM3zQSmA2meLf-DEdW04f3XpCPu3fHwdRfA/s1600/convert_-_figure_4.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="222" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiYi462Vv0S5e-JaoukkEEjN8WJdKm00olJ0Iegl7y5n02Khy9eTERjhVQ8f__ebD9N7EkXq14leA8vnY1_inpXSPJvrOuLcXtsOQFCq1aoaM3zQSmA2meLf-DEdW04f3XpCPu3fHwdRfA/s400/convert_-_figure_4.jpg" width="400" /></a></div>
<div style="background-color: white; border: 0px; box-sizing: border-box; color: #333333; font-family: Merriweather, serif; font-size: 17px; font-stretch: inherit; line-height: 32.3px; margin-bottom: 1.5rem; padding: 0px; vertical-align: baseline;">
<br /></div>
<div style="background-color: white; border: 0px; box-sizing: border-box; color: #333333; font-family: Merriweather, serif; font-size: 17px; font-stretch: inherit; line-height: 32.3px; margin-bottom: 1.5rem; padding: 0px; vertical-align: baseline;">
<strong style="border: 0px; box-sizing: border-box; font-stretch: inherit; line-height: 32.3px; margin: 0px; padding: 0px; vertical-align: baseline;">Figure 4:</strong><span style="line-height: 32.3px;"> Streamline your workflow by adding the Convert command to the Quick Access Toolbar.</span></div>

Convert XLS Workbooks in Two Keystrokes

There’s a good chance that any software you use to export data to Excel is surreptitiously making your day a little harder than it should b...

Identifying Duplicate Values in an Excel List

Abdur Rehman 0 Excel, Microsoft, Office 04:33

<div style="background-color: white; border: 0px; box-sizing: border-box; color: #333333; font-family: Merriweather, serif; font-size: 17px; font-stretch: inherit; line-height: 32.3px; margin-bottom: 1.5rem; padding: 0px; vertical-align: baseline;">
From time to time, you may need to identify duplicate values with a list in Excel. Modern versions, including Excel 2007 and later, provide this capability with just a few mouse clicks.</div>
<div class="advert advert_mpu-inline" style="background-color: white; border: 0px; box-sizing: border-box; color: #333333; float: right; font-family: Merriweather, serif; font-size: 17px; font-stretch: inherit; line-height: 32.3px; margin: 0px -9.375rem 1.5rem 1.5rem; padding: 0px 0px 1rem; vertical-align: baseline;">
<div class="advertise-with-us" style="background-color: #dce6eb; border: 0px; box-sizing: border-box; font-family: Lato, sans-serif; font-size: 0.75rem; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0.125rem 0.25rem; vertical-align: baseline;">
<a href="http://www.siftmedia.co.uk/portfolio/accountingwebcom" style="border: 0px; box-sizing: border-box; color: #86a3b4; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: 1; margin: 0px; overflow-wrap: break-word; padding: 0px; text-decoration: none; vertical-align: baseline; word-break: break-word; word-wrap: break-word;">Advertisement</a></div>
</div>
<div style="background-color: white; border: 0px; box-sizing: border-box; color: #333333; font-family: Merriweather, serif; font-size: 17px; font-stretch: inherit; line-height: 32.3px; margin-bottom: 1.5rem; padding: 0px; vertical-align: baseline;">
For good measure, I'll also describe a worksheet function-based approach that works in any version of Excel.</div>
<div style="background-color: white; border: 0px; box-sizing: border-box; color: #333333; font-family: Merriweather, serif; font-size: 17px; font-stretch: inherit; line-height: 32.3px; margin-bottom: 1.5rem; padding: 0px; vertical-align: baseline;">
As shown in Figure 1, you can easily identify duplicate invoice numbers in a list. To do so:</div>
<ul style="background-color: white; border: 0px; box-sizing: border-box; color: #333333; font-family: Merriweather, serif; font-size: 17px; font-stretch: inherit; line-height: 32.3px; margin: 0px 0px 1.5rem 1.75rem; padding: 0px; vertical-align: baseline;">
<li style="border: 0px; box-sizing: border-box; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">Select the range of cells you wish to test. One way to do so is to click on a single cell and then press Ctrl-A.</li>
<li style="border: 0px; box-sizing: border-box; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">On Excel's Home tab, choose Conditional Formatting, Highlight Cells Rules, and then Duplicate Values.</li>
<li style="border: 0px; box-sizing: border-box; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">Click OK within the Duplicate Values dialog box to identify the duplicate values.</li>
<li style="border: 0px; box-sizing: border-box; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">Duplicate values in the list will now be identified.</li>
</ul>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgkeTejrz9G6Vt3lNLYfu18TjGEglEio6FJ8PDDqtEZmoV_tPTNqlrJaRlZrOjEpmseT9dDLTLsc4h5c2ReuQ31avUfMhKLztupS7S3Cz99HVIPBDX61swyjf5MCARWSyK_xEoNajXLEWM/s1600/Duplicates---Figure-1tn.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="257" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgkeTejrz9G6Vt3lNLYfu18TjGEglEio6FJ8PDDqtEZmoV_tPTNqlrJaRlZrOjEpmseT9dDLTLsc4h5c2ReuQ31avUfMhKLztupS7S3Cz99HVIPBDX61swyjf5MCARWSyK_xEoNajXLEWM/s400/Duplicates---Figure-1tn.jpg" width="400" /></a></div>
<div>
<span style="color: #333333; font-family: Merriweather, serif;"><span style="font-size: 17px; line-height: 32.3px;"><br /></span></span></div>
<div>
<div style="background-color: white; border: 0px; box-sizing: border-box; color: #333333; font-family: Merriweather, serif; font-size: 17px; font-stretch: inherit; line-height: 32.3px; margin: 0px; padding: 0px; vertical-align: baseline;">
<strong style="border: 0px; box-sizing: border-box; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">Figure 1:</strong> Excel's Conditional Formatting feature makes it easy to identify duplicate values in a list.</div>
<div style="background-color: white; border: 0px; box-sizing: border-box; color: #333333; font-family: Merriweather, serif; font-size: 17px; font-stretch: inherit; line-height: 32.3px; margin: 0px; padding: 0px; vertical-align: baseline;">
 </div>
<div style="background-color: white; border: 0px; box-sizing: border-box; color: #333333; font-family: Merriweather, serif; font-size: 17px; font-stretch: inherit; line-height: 32.3px; margin: 0px; padding: 0px; vertical-align: baseline;">
You can then isolate just the duplicates, as shown in Figure 2:</div>
<ul style="background-color: white; border: 0px; box-sizing: border-box; color: #333333; font-family: Merriweather, serif; font-size: 17px; font-stretch: inherit; line-height: 32.3px; margin: 0px 0px 1.5rem 1.75rem; padding: 0px; vertical-align: baseline;">
<li style="border: 0px; box-sizing: border-box; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">Right-click on one of the duplicate cells, choose Filter, and then Filter by Selected Cell's Color.</li>
<li style="border: 0px; box-sizing: border-box; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">This collapses the list to show just the duplicate values, which you can copy and paste to another worksheet, or otherwise manage, as shown in the inset in Figure 2.</li>
</ul>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh1CL4NUbLMv-rquV-EoRgCdnWL85zlSHOXnMftC-mKkDNiTFSzoWzfbbPkrQd2rzO9Ey4Pzj55pdQYtxCEroh8fMdNm6xyeOXTdqQh2CaNmlNvdCs4PAmNbc6OoklB2-Xo9XESSyq4U7k/s1600/Duplicates---Figure-2tn.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="266" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh1CL4NUbLMv-rquV-EoRgCdnWL85zlSHOXnMftC-mKkDNiTFSzoWzfbbPkrQd2rzO9Ey4Pzj55pdQYtxCEroh8fMdNm6xyeOXTdqQh2CaNmlNvdCs4PAmNbc6OoklB2-Xo9XESSyq4U7k/s400/Duplicates---Figure-2tn.jpg" width="400" /></a></div>
<div>
<span style="color: #333333; font-family: Merriweather, serif;"><span style="font-size: 17px; line-height: 32.3px;"><br /></span></span></div>
</div>
<div>
<div style="background-color: white; border: 0px; box-sizing: border-box; color: #333333; font-family: Merriweather, serif; font-size: 17px; font-stretch: inherit; line-height: 32.3px; margin: 0px; padding: 0px; vertical-align: baseline;">
<strong style="border: 0px; box-sizing: border-box; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">igure 2:</strong> Excel 2007 and later offer the ability to filter cells based on color.</div>
<div style="background-color: white; border: 0px; box-sizing: border-box; color: #333333; font-family: Merriweather, serif; font-size: 17px; font-stretch: inherit; line-height: 32.3px; margin: 0px; padding: 0px; vertical-align: baseline;">
 </div>
<div style="background-color: white; border: 0px; box-sizing: border-box; color: #333333; font-family: Merriweather, serif; font-size: 17px; font-stretch: inherit; line-height: 32.3px; margin: 0px; padding: 0px; vertical-align: baseline;">
To remove the conditional formatting, one approach is to click the Conditional Formatting button, choose Clear Rules, and then Clear Rules from Entire Worksheet, as shown in Figure 3.</div>
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiQQKqIDxgitDStvNZjj1W_zRGXDwjEcSAlAVYWVS2W14O4rEDL-d4yYvFodljfLdRLhE4e5fZXAFBGbhDBPBb48tD7ReG5MhBZ7cPJzekAniayUHNBlh8e1YlTpufo3DfzgW_fiOBp0GE/s1600/Duplicates---Figure-3tn.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="286" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiQQKqIDxgitDStvNZjj1W_zRGXDwjEcSAlAVYWVS2W14O4rEDL-d4yYvFodljfLdRLhE4e5fZXAFBGbhDBPBb48tD7ReG5MhBZ7cPJzekAniayUHNBlh8e1YlTpufo3DfzgW_fiOBp0GE/s400/Duplicates---Figure-3tn.jpg" width="400" /></a></div>
<div style="background-color: white; border: 0px; box-sizing: border-box; color: #333333; font-family: Merriweather, serif; font-size: 17px; font-stretch: inherit; line-height: 32.3px; margin: 0px; padding: 0px; vertical-align: baseline;">
<br /></div>
<div style="background-color: white; border: 0px; box-sizing: border-box; color: #333333; font-family: Merriweather, serif; font-size: 17px; font-stretch: inherit; line-height: 32.3px; margin: 0px; padding: 0px; vertical-align: baseline;">
<div style="border: 0px; box-sizing: border-box; font-stretch: inherit; line-height: 32.3px; margin: 0px; padding: 0px; vertical-align: baseline;">
<strong style="border: 0px; box-sizing: border-box; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">Figure 3:</strong> You can easily clear conditional formatting from a worksheet in Excel 2007 and later.</div>
<div style="border: 0px; box-sizing: border-box; font-stretch: inherit; line-height: 32.3px; margin: 0px; padding: 0px; vertical-align: baseline;">
 </div>
<div style="border: 0px; box-sizing: border-box; font-stretch: inherit; line-height: 32.3px; margin: 0px; padding: 0px; vertical-align: baseline;">
If you're using Excel 2003,  you don't have the ability to filter by color, but you can isolate duplicate values by way of the COUNTIF worksheet function. COUNTIF has two arguments:</div>
<ul style="border: 0px; box-sizing: border-box; font-stretch: inherit; line-height: 32.3px; margin: 0px 0px 1.5rem 1.75rem; padding: 0px; vertical-align: baseline;">
<li style="border: 0px; box-sizing: border-box; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;"><em style="border: 0px; box-sizing: border-box; font-family: inherit; font-size: inherit; font-stretch: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">Range:</em> This is a range of two or more cells that you wish to test.</li>
<li style="border: 0px; box-sizing: border-box; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;"><em style="border: 0px; box-sizing: border-box; font-family: inherit; font-size: inherit; font-stretch: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">Criteria:</em> The value that you're seeking within the range.</li>
</ul>
<div style="border: 0px; box-sizing: border-box; font-stretch: inherit; line-height: 32.3px; margin: 0px; padding: 0px; vertical-align: baseline;">
Building on the example shown in Figure 1, you can add the following formula to cell C2:</div>
<div style="border: 0px; box-sizing: border-box; font-stretch: inherit; line-height: 32.3px; margin: 0px; padding: 0px; vertical-align: baseline;">
 </div>
<div style="border: 0px; box-sizing: border-box; font-stretch: inherit; line-height: 32.3px; margin: 0px; padding: 0px; vertical-align: baseline;">
=COUNTIF(A:A,A2)</div>
<div style="border: 0px; box-sizing: border-box; font-stretch: inherit; line-height: 32.3px; margin: 0px; padding: 0px; vertical-align: baseline;">
 </div>
<div style="border: 0px; box-sizing: border-box; font-stretch: inherit; line-height: 32.3px; margin: 0px; padding: 0px; vertical-align: baseline;">
Once you've entered the formula, double-click on the Fill Handle in cell C2 to copy the formula down the column. The Fill Handle is the little notch that most users drag down a column when copying formulas.</div>
<div style="border: 0px; box-sizing: border-box; font-stretch: inherit; line-height: 32.3px; margin: 0px; padding: 0px; vertical-align: baseline;">
 </div>
<div style="border: 0px; box-sizing: border-box; font-stretch: inherit; line-height: 32.3px; margin: 0px; padding: 0px; vertical-align: baseline;">
You can then filter the list for any values greater than 1:</div>
<div style="border: 0px; box-sizing: border-box; font-stretch: inherit; line-height: 32.3px; margin: 0px; padding: 0px; vertical-align: baseline;">
 </div>
<div style="border: 0px; box-sizing: border-box; font-stretch: inherit; line-height: 32.3px; margin: 0px; padding: 0px; vertical-align: baseline;">
<strong style="border: 0px; box-sizing: border-box; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">Excel 2007 and later</strong></div>
<ol style="border: 0px; box-sizing: border-box; font-stretch: inherit; line-height: 32.3px; margin: 0px 0px 1.5rem 1.75rem; padding: 0px; vertical-align: baseline;">
<li style="border: 0px; box-sizing: border-box; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">Click on cell A1 and then choose Filter on the Data tab of Excel's ribbon.</li>
<li style="border: 0px; box-sizing: border-box; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">Click the Filter arrow in cell C1, choose Number Filters, and then Greater Than.</li>
<li style="border: 0px; box-sizing: border-box; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">Enter 1, and then click OK to filter the list to for duplicate values.</li>
</ol>
<div style="border: 0px; box-sizing: border-box; font-stretch: inherit; line-height: 32.3px; margin: 0px; padding: 0px; vertical-align: baseline;">
<strong style="border: 0px; box-sizing: border-box; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">Excel 2003 and earlier  </strong></div>
<ol style="border: 0px; box-sizing: border-box; font-stretch: inherit; line-height: 32.3px; margin: 0px 0px 1.5rem 1.75rem; padding: 0px; vertical-align: baseline;">
<li style="border: 0px; box-sizing: border-box; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">Click on cell A1, then choose Data, Filter, and then AutoFilter.</li>
<li style="border: 0px; box-sizing: border-box; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">Click the Filter arrow in cell C1 and then choose Custom.</li>
<li style="border: 0px; box-sizing: border-box; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">Change Equals to Greater Than, Enter 1, and then click OK.</li>
<li style="border: 0px; box-sizing: border-box; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;"> </li>
<li style="border: 0px; box-sizing: border-box; font-family: inherit; font-size: inherit; font-stretch: inherit; font-style: inherit; font-variant: inherit; font-weight: inherit; line-height: inherit; margin: 0px; padding: 0px; vertical-align: baseline;">Read Part 2 of this series to learn more ways to identify duplicate items an Excel spreadsheets.</li>
</ol>
</div>

Identifying Duplicate Values in an Excel List

From time to time, you may need to identify duplicate values with a list in Excel. Modern versions, including Excel 2007 and later, provide...

69 Tutorial

About Me

Blog Archive

How to Enable Freehand Electronic Signatures in Excel

Convert XLS Workbooks in Two Keystrokes

Identifying Duplicate Values in an Excel List