Successful businesses are built upon a solid foundation of data, enabling them to analyse markets and deliver ROI. Whilst data is undoubtedly king, the devil is in the details. Here are five amazingly easy ways to manipulate data in Excel…
1. How to separate First Name and Last Name into two columns (or, split one field into several)
You’ll find this trick nifty if you have data that you need to manipulate to neatly import into a CRM or similar...
- Select the cells you wish to split.
- Click the Data tab, then click ‘Text to Columns’.
- In the ‘Convert Text to Columns Wizard’ chose the Delimited file type and click next.
- Then in the Delimiter selection box select Space. You should see how the data is manipulated in the Data preview box. Click Next.
- In the final step be sure your Column Data formats are correct and set your cell destination, and click Finish.
2. How to identify and remove duplicate records
Dealing with a lot of data means there’s always a chance that a duplication might slip through the cracks. Avoid doubling up on data unnecessarily and disrupting the stats with this quick trick…
- Select the cells you wish to check.
- Select the Home tab and locate ‘Conditional Formatting’.
- Hover over Highlight Cells Rules and then Duplicate Values and click OK.
- Duplicates within your selection will be identified.
- To isolate the duplicates in order to delete them right click a duplicate cell (highlighted red), click Filter, then Filter by Selected Cell Colour.
- You can then chose to amend or delete the duplicate cells.
3. Removing undesired characters
Consistent data records are a breeze with this simple fix to Find and Replace any extra characters or terms....
- If you wish only to check for a term or character in a certain column (rather than the whole sheet) then copy the column you wish to check into a new worksheet.
- Highlight the cells you wish to search and press CTRL + F.
- To remove an ampersand (&) enter “&” in the Find What field; click the Replace tab and replace the ampersand with a blank by pressing the spacebar. Once in the Replace with field, click Replace.
- If you have moved your column to the seperate worksheet (as per step 1) then copy the data into your master worksheet.
4. Using Excel to find someone’s current age
Bearing in mind your worksheet has a date of birth then this quick trick can be useful for keeping track of birthdays and ages in one fell swoop…
- Select an empty cell where you’d like the age to show.
- In the formula bar =DATEDIF(CellWithBirthday,TODAY(), "y")
- Now you’ll see the age in years - this will be accurate to the day you access the data, meaning even if you set this formula up today, but return to the worksheet in two years, the age will be reflective of the day of accessing the file.
5. Keep the header columns visible throughout the worksheets scroll
Working on a big worksheet can be frustrating when you lose sight of the signpost headers as you scroll past the first 20 rows. Keep sight of what data you’re viewing…
- Select the View tab in the navigation panel.
- Click Freeze Panes, and then Freeze Top Row.
By now you’re feeling like a Microsoft Excel wizz! Continue your tutelage with our Microsoft Excel Advanced course; it’s designed for users who wish to interrogate data, create pivot tables and use detailed business analysis tools.