3 Hidden Excel Tricks That Every Excel Pro user want to use
1. Freeze Top Row
This is a fairly well-known tool, but I still often find people who don’t know it exists. When you have a spreadsheet with enough data that you are required to scroll, have you ever wished that you could still see the top row that has the headings?
There are two ways to solve this. The first is to format your data as a table. When you do this, you identify the data as a collection or database. Excel will automatically make the top row the headers for each column when you scroll. To make this happen, go to Home > Format as Table, and select any style.
The other way to keep the top row always visible is to actually freeze it. To do this, go to View > Freeze Panes > Freeze Top Row.
2. Drop-Down Lists with Data Validation
Consistent data entry is crucial in whatever work you do, but especially in spreadsheets. I use Data Validation to build drop-down lists within cells so when I go to enter data into a cell, I can only use the options in that drop-down list. This is great for keeping data clean and consistent across the entire spreadsheet.
To apply Data Validation, go to Data > Data Validation > Data Validation. Under Allow, select List. Then in the box under Source, enter the options you want to include in the drop-down list with each option separated by a comma. Then click OK. Now you’ve got a dropdown list! Copy and paste this cell where you want to use that list.
3.Conditional Formatting
Conditional formatting has far more uses than can be explained in a single blog post, but in a nutshell, it allows you to format different data values differently.
I use this the most to find duplicate values in a list of items. This makes it easier, for instance, to remove duplicate email addresses from a list.
To use Conditional Formatting in this way, highlight a column and go to Home > Conditional Formatting > Highlight Cell Rules > Duplicate Values.
0 Comments