When you start learning Excel, you begin by using the simplest methods for entering data and creating calculations. It is a credit to the software that so many tasks are able to be performed by the most inexperienced of users right from the beginning of their training.
Once you gain experience and confidence, however, these simple methods quickly begin to feel slow and tedious. Here’s a list of efficient, time-saving and downright cool Excel tips and tricks for entering data!
Top Excel Tips and Tricks for Entering Data
Tip #1 – Auto Fill
The Fill Handle appears in the corner of every selected cell. Click on the handle of a cell that has data in it, then drag to let Excel fill in the next cell of a column or row. The default fill is a simple copy of the data from the selected cell into the dragged cells, giving you a fast way to populate an entire row or column.
If, however, Excel detects a number or a date or other data that commonly appears in sequences, the Fill Series feature will automatically advance that sequence into the next cells. Numbers will advance to the next number, dates will advance to the next date, Months of the year will advance to the next month and so on.
Time Saver! Double-click the Fill Handle to quickly apply a fill to the edge of a data range!
AutoFill Trick #1: If Excel guesses wrong and fills with a sequence instead of copying cell data, you can use the AutoFill Options menu to change the fill from Fill Series to Copy Cells.
AutoFill Trick #2: Select two or three cells of data that contains a non-linear (but regular) sequence, then drag the fill handle to continue the sequence! Use this method to count by 2s, 3s, 10s or even advance a date by every-other-day.
AutoFill Trick #3: When working with date sequences, use the AutoFill Options menu to advance dates by Weekdays only, Months or Years instead of daily.
AutoFill Trick #4: You can use the AutoFill options menu to copy ONLY formatting to the affected cells. This is something like a draggable version of the Format Painter.
Tip #2 – Flash Fill
Flash Fill detects patterns as you enter data and then fills cells based on that pattern.
For example: You might want to create a Full Name column based on the individual fields for first name, middle initial and last name. Instead of typing every name manually, or building a complicated concatenate formula, use Flash Fill!
- Type the result into the first cell and hit Enter.
- Repeat until Excel offers you a shaded version of the pattern for review.
- Hit Enter to accept and apply the pattern to the remaining cells in the range.
Tip #3 – Ctrl+Enter
AutoFill and Flash Fill are great when you need to enter repetitive data in cells next to each other, but you may have a time when you need to enter the same information into cells that are not in a single row or column or aren’t touching at all.
- Select a range of cells by clicking and dragging with the mouse or use Ctrl+Click to select individual cells.
- Type the data you want added to every selected cell.
- Hit Ctrl+Enter (Not just Enter alone!).
All the selected cells are filled with the same text.
Tip #4 – Transpose
Sometimes your data wasn’t entered the way you need it to be organized. Or, you may just want to look at it in a different way. The transpose feature, located with the Paste Special options, lets you swap Rows and Columns without having to re-enter the data.
- Select the range you want to swap.
- Hit Ctrl+C to copy the range.
- Right-click at the place where the swapped range will go and select Paste Special from the fly-out menu.
- Click the Transpose
Your data is pasted into the new location with column headers moved to row headers and vice versa:
Tip #5 – Text to Columns
When you are pasting data into a spreadsheet from other software – such as text editors or a web page – you will often find that information that should be spread across several cells is pasted into one single cell. Instead of manually splitting up the information, use Text to Columns.
For example, let’s say we have the opposite problem that we did above and want to separate a cell that contains full names into three individual fields for first name, middle initial and last name.
- Select column to be separated.
- Click the Data
- Click Text to Columns.
- Choose the way that Excel will know where to split the data.
- Choose Delimited if there is a character – such as a comma, space, or tab – that consistently appears at each place a new cell should begin.
- Choose Fixed width if the data will always break at the same number of characters in each new cell.
- Click Next.
- Choose the character that will function as a delimiter, or place break lines where they will separate your data correctly.
- Click Finish.
How to Improve Your Excel Skills
Many times we continue to do things the same way we learned them, even if they are slow and inefficient. The tips shared above are all steps towards making common tasks faster and easier! But, they will take a little investment of time to learn. Just remember that you are trading time now for a LOT of saved steps later.
It takes some thought and deliberation to explore all the cool things Excel can do! But with a little effort, you’ll be an expert in no time.