Efficient Data Entry and Management in Excel
Efficient Data Entry and Management tools are very important when it comes to speeding up your workflow and as well as increasing accuracy.
Tools like AutoFill and Flash Fill can save a lot of time by predicting what you are trying to write. While using tables can help us filter and sort data more effectively. Lets see how each of these tools can be used to make our lives easier in excel!
AutoFill and Flash Fill Tools
AutoFill
Recognizing Patterns: Excel’s Autofill recognizes basic patterns. If you have months that typically go in chronological order it can help fill out the rest of the months.
For Example if we write “January” in one cell and then click the corner of the cell and drag it down excel will fill in the rest of the months for us.
Using AutoFill with Numbers: With numbers its a little bit different. We can’t just put “1” in a single text then expect excel to know what were trying to do. It needs to recognize some pattern remember.
If we make the cell below it a “2” then excel knows the pattern for now is +1. So if highlight both cells and drag it down we get a list of numbers 1,2…n+1.
Creating Custom AutoFill Lists: If you want to create your own copy of a list you are repeatedly using throughout your worksheet. Go to File > Options > Advanced > Edit Custom List.
Highlight the cells and then import them to create a reusable list. This is particularly useful when it comes to having a list that excel doesn't typically recognize as a pattern such as a list of food ingredients like “strawberries, bananas etc..”
Flash Fill
Finding Patterns: Flash Fill is similar to AutoFill in the sense that it try's to find patterns and fill accordingly. However AutoFill has its limitations. While Flash Fill is a smarter version that can predict more complex scenarios. Helping us fill out emails, names and more!
If you trying to get the first name and last name in separate cells using the full name in another cell Autofill will struggle with that. All that will happen is when you drag down it will copy the first cell over and over again.
First drag the cell down , preferably the same length as the data your trying to copy from. You will see all the cells look the same but fear not. Click the corner of where you dragged down and click the box to open up your choices. Then click Flash Fill and your done!
Formatting as a Table
Creating and Using Tables
Steps to format data as a table: To format random data into a more organized table go to home Tab > click format table in the styles section. Make sure to have your data selected when you click on it. Then click the style of table you want to apply and if your table includes headers make sure to check off that box as well.
Dynamic Table Features: One of the neat things about tables is that the tables automatically expand when new data is added. Another thing is when you click on tables in excel the design tab reveals itself on the header. Which gives you additional options from removing the filter buttons to readjusting the column and row styles and more!
Sorting and Filtering: While we will discuss this in more detail later. Tables enable us to sort and filter data through the utilization of the arrows that are on the headers for our columns. All you have to do is click on the respective arrow for the column you want to sort and filter.
For example if wanted to sort by first name. We would go to the column that has the first name and click “Sort A to Z”. If we also wanted to filter certain values we can uncheck the boxes to our liking and we can do so much more!
Sorting Data
Steps for Sorting
Using the Home Tab: To start sorting in excel , select in a cell in the column that you want to be sorted. Go to the Home tab, click the Editing section, and choose “Sort & Filter.” After that you will be shown a selection of different sorting options from ordering numbers to adding a filter to that column if it didn't exist before and creating your own custom sorting.
Filtering Data
Applying and Clearing Filters
Using Keyboard Shortcuts: You can apply filters with Ctrl + Shift + L and clear them with the same shortcut. If for whatever reason you don’t want to use the shortcuts you can manually un click what you have selected of course.
Using the Sort Button: You can apply filters manually by using the sort button mentioned before or by right clicking the cell and selecting filter.
Advanced Filtering Options: If you want to take your filtering skills one step further. Look no further than using the text filter option. Here we can filter by specific keywords you might be looking for.
For example we can add wildcards “*” that we have discussed before for more advanced searching. We can search for colors as well or even by date.
Copying Filtered Data: Lets say we have filtered our data and we want to actually use those results even though the filtered table is just a temporary table. All you have to do is select the table then Ctrl + A then Ctrl + C . Go to a new worksheet and type Ctrl + V and their you go!
Using Subtotal Formula: An issue arises if we try to perform formulas on filtered data. For example if we tried to sum up the filtered values we would get the sum of all the hidden cells as well which is not ideal. However using the subtotal formula we can bypass that.
Here’s how its written =SUBTOTAL(function_num,range1,...) . The parameter function_num is a number from 1–9 which is the different types of statistical functions you want to use. In this case since we are trying to sum up the filtered values SUM is number 9. So all we have to do is type this =SUBTOTAL(9,range1).
Overall, by using tools like AutoFill and Flash Fill to speed up our efficiency in data entry and by also using tables and filters/sorting to analyze our data more efficiently we can get one step closer to becoming a better data analyst. Remember at the end of the day excel is a tool just like many other tools out their that can help you do your job more efficiently.
Whether your looking to up skill your own workflow or analyze sales data more efficiently , mastering these tools will help you do just that. In the next article I will discuss how we can not only analyze but now visualize our data to uncover more patterns and trends we couldn't see before.
