by Excel is such a powerful tool for presenting and analysing data. But, with the huge amount of manipulations you can do, it can sometimes be overwhelming to discover some of the simplest tricks that can save you so much time.
Each time you find yourself manually entering or formatting a large amount of data, there’s probably a formula that can do it for you. So it always worth looking for a quick fix using Excel’s functions, before you start.
So, whether you are just starting out using Excel, or are an experienced user – here are some simple Microsoft Excel tricks that can save you a lot of time formatting and manipulating your spreadsheets.
Enter all your Column Headers Without Clicking into Every Cell
The first action nearly all of us take when setting up a new Excel spreadsheet is to label all our column headers.
If you currently do this by clicking a column, entering your data and then repeating by clicking the next column – there is a quicker way.
Select the cell when you want your column headers to start. Then highlight all the cells you need for your column headers.
Type the header for your first column, and then hit enter.
As you have only selected the cells you want to use, hitting enter will jump you to the next column (rather than the next row).
If you ever should need to, this technique work if you highlight any number of cells across your spreadsheet. Hitting enter will move you between those cells.
Insert Multiple Rows or Columns in One Go
As you start to organise your data, you’ll find that you’re constantly needing to add more rows or columns.
Depending on the size of your spreadsheet, you may need to add hundreds of rows at a time. We all know the right click and insert, but doing this constantly gets a little tedious.
Here’s a quicker way:
Say you want to add 5 rows to your spreadsheet.
Simply highlight 5 of the existing rows, where you want to insert the new ones, then right click and select “insert”.
This will add the 5 extra rows you wanted.
If you want to add more than 5 rows, simply highlight the required number of rows and insert.
Select Your Data Range Quickly
You will notice in the picture examples we are using, that the data in our spreadsheet starts in column B rather than A.
This is deliberate, as it allows us to do some handy things as our data grows.
If you have ever tried to select all the data in your range – and have clicked and dragged, only to fly 1000 cells past the end of your data – this tip is for you.
With this white space around your data, if you use CTRL-A, this will instantly select the current data in your range. Meaning Excel finds an ‘island’ of data with white space around it.
By entering your data as we have here, beginning in column B, you can easily select it all with CTRL-A whenever you need to.
Use Format Painter to Make Your Spreadsheet Look Consistent
If you have ever pasted a new data set into your carefully formatted spreadsheet, you will have no doubt had to spend some time re-formatting all the data so that it looks consistent with the rest of the information you have entered.
To quickly format any data in cells and rows to your current design, using the Format painter will save you a lot of time.
First click on a cell that has the correct formatting for your spreadsheet. Then in the ‘Home’ tab in the top ribbon, select ‘Format Painter’ under the clipboard section.
Once you do this, you will notice that your cursor will have a small paintbrush next to it.
Now simply highlight the cells or cells you want to fit the format a click.
This will change them to match your selected formatting.
Auto Filling Columns with Dates or Numbers
If your data includes dates or number, there are some neat ways you can quickly add or repeat the required date or number range you need.
Say for your monthly report, you want to add all the whole date range for September.
Enter the first date in the series, e.g. 01/09/2016, and select that cell. Then hover over the small green square in the bottom right of the selected cell.
The mouse cursor will change to a ‘plus’. Then simply click and drag down, to autofill all the rows below with the next dates.
If you need to repeat a single date, follow the same pattern as above, but just hold ‘CRTL’ while you click and drag. This will fill all the rows below with the same date.
The same will work with numbers.
Removing Duplicates
If you have a large data set, then it is likely you have duplicated content.
Say you have a list containing all your contacts at the companies your business works with, but you only want to see the total number of companies you work with.
Excel’s duplicate removal tool comes in very handy in these situations.
To remove any duplicated data, highlight the row or column you want to remove duplicates of. Then go to the ‘Data’ tab in the top ribbon and select ‘remove duplicates’ from the data tools section.
A pop up will ask you to confirm the data you want to remove the duplicates from, and once you’ve clicked ‘Removed Duplicates’ the cleaned data is ready for you.
Change Your Table Columns into Rows
We’ve all been there, we have formatted all our data only to realise, or be asked to display the information in rows rather than columns.
To change all of your data round would take ages, but luckily there is a quick way to do it with Excels ‘Transpose’ function.
Let’s say you want to change your column into a row.
Start by highlighting your column. Next, right click the column and select ‘Copy’. Now select the cell on your spreadsheet where you want your first new row to begin.
Right click and select ‘Paste Special’. A pop up will appear with all the options available to you.
Simple select ‘Transpose’ in the bottom right hand corner and hey presto, your column is now a row.
Transpose also works if you wish to switch a whole table from columns to rows and visa versa. Just select all the data and transpose as above.
Split Out One Cell into Two Cells
Let’s say, for example, that you have entered the full names of the contacts you have at a company in one column and you now require to have that information split into two columns, first name and last name.
Rather than re-entering all the data again, Excel can do this for you.
First highlight the column you want to split into two. Next go the ‘Data’ tab in the top ribbon and click ‘Text to Columns’ in the data tools section.
A pop up will appear asking you to confirm if Excel has selected the right split method for your data.
For this example, ‘Delimited’ is correct, as this what you would use to break up a column based on spaces, tabs or characters like commas.
Click next to choose your ‘Delimiters’ to split your column.
For this example, we want to use ‘Space’ as this is the gap between the first name and last name.
Click finish and your column of full names is now split into two columns containing the first name and last name of your contacts.
Combine Cells Easily with Formulas
So, what if you want reverse the above and combine some data into one cell? The quickest way to combine data into one cell is using the simple ‘&’ sign in a function.
Let’s take the names we have just spilt into two columns in the previous example.
We now have the first name and last name split in columns A and B. Let’s recombine those in column E where we need them to be.
First select the cell in the column where you want the combined data to go, then go to the function bar and enter then function: =B5&” “&C5
This formula tells Excel to do the following:
B5 is the location of the first name and C5 the location of the last name we want to combine.
The ‘&’ character is what creates the combination.
The two quotation marks “ “ are important, as this tells Excel that you want to add a space between the combined cell data. If we did not add these quotation marks, you would not get the space between the persons first and last name.
To then easily do this for all the rows in our example table, just drag the corner of the cell as we did in tip 2 above.
Easy.
Pivot Tables – They’re not so hard
Pivot Tables are one of Excel’s most powerful functions, but they also come with a reputation of being complicated and we’re not all Excel experts.
Good news:
Learning how to create Pivot Tables that you will find useful is much easier than you may believe.
Pivot Tables are a great way to re-organise the data you have in your spreadsheet in many ways, without altering the information you have carefully put in.
You can use them to sum up values or compare certain information, depending on what you want to do.
Let’s look at how we can use a Pivot Table to see how many people have certain clearance levels on the Death Star.
First select the data you’d like to use, then click the ‘Insert’ tab in the ribbon at the top of the page and select ‘Pivot Chart’.
You’ll get a pop-up asking you to confirm the data range, which we already have selected and where we want the Pivot Chart to be placed. We’ll keep it in the same spreadsheet for now, so we’ll select ‘existing worksheet’ and click ok.
We now have four options to choose from.
Filter: This allows you to look at certain rows in our spreadsheet.
Column: These are your column labels, and as an example could be the headers in our spreadsheet.
Row: These could be your rows in the spreadsheet.
Values: This section allows you to look at your data differently. Instead of just pulling in any numeric value, you can sum, count, average, max, min, count numbers, or do a few other operations with your data.
So, to see how many people have the different clearance levels on the death star, all I need to do is go to the Pivot Table and drag the ‘Clearance’ column into the Rows and Values boxes.
This will sum up the number of people who have each of the clearance levels on the Death Star.
Easier than you thought, right?
We hope you found this article helpful. Bookmark it to keep these handy Excel tips at hand for when you need them.
