Manipulating Data In Excel
Flash Fill And Remove Duplicates
If you deal with lots of data lists and find yourself doing a lot of cleaning and tiding up as well as having Microsoft Excel version 2013 or higher, then you will have this new feature called Flash Fill.
Prior to this version you will need to use the usual Text to Column feature found under the Data Tab. When you have data like an email address you may be needing to display only the surname. See “What Is Flash Fill In Excel” for examples.
With Microsoft Excel’s Flash Fill you can start the ball rolling by typing in the next column, for example the surname “Pisani” then fill the contents of the cell down. Initially the data should repeat. From the AutoFill Smart Tag that appears select Flash Fill this will start to pick up the surname from each row.
Microsoft Excel now has a feature called Remove Duplicates found under the Data Tab. Just take note the result of the duplicates does not get re located onto a separate sheet so once you press that button – Remove Duplicates, that’s it, you are left with the clean data.
In the past the alternative was to sort data then create an If Statement to test the first cell with the one below and return Yes or No, then filter all the yes’s and delete.
Sharing Excel Files
Sharing Excel Files using One Drive is very easy. One Drive is a Microsoft Office product that’s why it plays well with other Microsoft Office products like Excel.
One person can be in the file making a change and at the very same time another can be seeing the changes take place right before their eyes.
- Open the Excel File
- If you have version 2013 or 2016 you will have a share icon on the top right-hand corner
- Click the Share button
- Select Save to Cloud
- From the left-hand side Select OneDrive
- From the right navigate through your OneDrive folders
- Double Click the OneDrive folder to store it in
- Make sure you name your file then Press Save
We can use Microsoft Excel when dealing with long lists. There are several techniques you can use to achieve this. One is to Go to the Data Tab and Select the Filter button the Second option is to activate from the Home tab and Select Filter.
Whilst the Filter tool is useful and will show you only the data that you are interested in. It also has the sort feature built into it under the Drop-down arrows.
Important to note is if you have blank rows and select a cell at the top, your filter will only capture data up until there was a blank row. What you should do first is select the first cell of your data, Press Ctrl Shift End then press the Data Tab and Select Filter.
The feature I love the most in the Filters is the Search Bar. Press the down arrow of any heading you will then see the Search Bar. If you have dates you can group quickly and easily by quarter, this quarter, last quarter. I find this so handing when importing my bank statements into Excel and then doing my book work.
Filter By and Sort By colour are both great tools to gather information for example everything yellow needs a post code, everything green is completed.
What Is Flash Fill?
An alternative to using Text to Column to extract the part that you need from a mix of data is the Flash Fill feature in Excel.
For example, if you have: firstname.lastname@example.org you might want to extract each person’s surname.
Alternatively use Text to Column found under Data Tab.
- Type Pisani then fill down
- Click on the AutoFill Options Smart Tag
- Select Flash Fill only available since version 2013 forward
Split Data Into Separate Cells
When you need to split the data in Microsoft Excel from one column into several – use Text to Column. You can split the data each time there is a: space, comma, @, Tab and more. This feature has been available for many versions of Microsoft Excel.
What is new since Microsoft Excel Version 2013 is Flash Fill. Most of the time when people are using the Text to Column feature, they are not aware that they can skip columns. So, what they do is bring everything in and then start deleting columns when they could have done it in one place. Same goes for formatting the feature to format as date is in the Text to Column dialog box.
Here are the steps to separate data from on column into several columns.
Using Text To Column
- Select the column to split
- Ensure there is no data to the right
- Go to the Data Tab
- Select Text to Column
- If the data is separated by spaces, tabs, commas us the delimited options
- If the data has no notable separator use fixed
- If there is a column you do not wish to display
- Select “Do not import column(skip)”
Naming Excel Sheets
There are two techniques you can use when wanting to name your sheets in Microsoft Excel. You can Double Click on the name of the sheet, type the name and press Enter. You can also Right Mouse Click on the name of sheet and select from the list, Rename.
When working in Microsoft Excel you have a Workbook Name which means the name of the file. In just one sheet you have 16,000 columns and over 1 million rows.
English pages and pages worth of data in just 1 sheet. To navigate efficiently through all these sheets, you might like to Right Mouse Click on the arrows on the bottom left-hand corner. This will display a list of all the names of the sheets in your Microsoft Excel Workbook.
No, they do not appear in alphabetical order.
They appear in the order that you need to work with them. You can also Right Mouse Click on the Name of the sheet to change its tab colour, this can make it easier to find.
To make a copy of a sheet you can Right Mouse Click and select Move or Copy but it’s much easier if you hold the CTRL key as you drag. You should see a plus sign, this means it’s performing a copy.
YouTube Working with Sheets
This video will walk you through how to create a new sheet, rename it, copy a sheet and group sheets. The best thing about grouping sheets is that once they are grouped whatever you do in one cell will display in each of the other sheets. Imagine the formula or function was incorrect for each department sheet in cell A50. Group your sheets, make the change ungroup the sheets, that’s it they are all done all 20 different department sheets you had.
Navigating Multiple Sheets
If you find yourself working between multiple sheets you may find it frustrating to get quickly from one to the other. Pressing the navigation arrows on the bottom left will take for ever.
You could use shortcut keys Ctrl Page Up, Ctrl Page Down but this isn’t any faster.
Try the ultimate “Right Mouse Click” on the bottom left navigation arrows.
You will be presented with a list of all your sheets simply select one item in the list initially then press the first letter of the sheet name you would like to get to. If it didn’t get to the one you wanted, continue pressing the same letter. It will cycle through everything that starts with that letter.
Flash Fill is an alternative to using Text to Column. Often the simplest things are the most time saving like right mouse clicking on the bottom left hand corner of your Excel Spreadsheet to view a list of all your sheets, allowing you to quickly get to the specific Excel Spreadsheet you are after.
Be sure to subscribe to our YouTube Channel – Analir Pisani. You will be empowered.
Microsoft Office Small Group Training Sessions
AZ Solutions delivers customized training courses in Sydney – Australia. We come to you. All you need is a board room, PC’s for each student and a TV/ Projector with a HDMI connection cable.
In our training sessions you are welcomed to bring examples of your work to class. We prefer it.
Call Now M 0414 417 059 visit www.azsolutions.com.au