Excel Help!

Excel is a great resource that can save us time and energy in our more administrative duties, but often it’s not used to its full potential. This is a quick guide for some of the many tools hidden in plain sight. But for those tools which aren’t covered, the Internet is your best friend when it comes to troubleshooting and here’s a great place to start.


Freeze Panes

Have you ever had a large table of data and when scrolling down, forgotten what the column headers were?

Freezing panes is all about locking in selected cells so they stay in place when scrolling either down or across the page.

A pane can either be the top row of cells across the page such as headers for columns, or a set number of cell rows.

How to freeze panes:

On the ‘View tab’, go to the ‘Window’ section. This will be where the Freeze Panes menu is. Click the option you need, and now you have locked in a section of the page. You can also freeze both columns and rows and the same time!


Filtering

The filtering option in Excel allows the user to automatically insert a drop down menu into a header or subheading.

How to filter a table:

After highlighting your table, simply navigate to the top right hand side of the screen and click Sort and Filter. Select the option Filter, and see drop down arrows appear in your table.

This Filter function helps to sort through and show only relevant data. This function can be used to sort by locations, names, team divisions etc.

In this example, the first column titled Letter can be changed to only include the text ‘a’.


Conditional Formatting

This is a great tool where Excel can format cells based on their contents. This makes visually identifying certain values or range of values in a large table very easy.

How to use Conditional Formatting?

First, decide what type of cells you want format. It might be dates in the last week, cells which contain a single word or blank cells. Then select the cell range which you want to conditionally format. In the ‘Home’ tab, find the ‘Conditional Formatting’ button.

Depending on what your formatting criteria is, you will select something different in this dropdown menu. For this example, we want to highlight all cells containing the word “Fail” to red so that it stands out. Click ‘Highlight Cells Rules’, and click ‘Text that contains’.

Type in your phrase into the first text box, and select the formatting type in the second box. You can already see the cell has been formatted.

You can select other rules which format based on numbers, text or dates. Also, there is an option to create a completely ‘New Rule’ which is useful when you want to format based on a custom condition. Explore the submenus under ‘Conditional Formatting’ to see what your options are, and if you ever need help, Google is the answer!


Data Tools

The following tools can all be found in the Data Tools box, found under the Data tab. These are all handy tools to manipulate data.


Text to Columns

Have you ever copied a large amount of data, pasted into Excel and the text just isn’t separated the way you want? The Text to Columns Function splits text into adjacent columns to help formatting and filtering of data!

How to use Text to Columns?

The Text to Columns function will help to split data that originally looks like:

Within the Data ribbon and select the Text to Columns function.

A pop up window will appear. Click through the steps, adjusting details if needed. The ‘Delimited’ option should be used when your data is split by a delimiter such as a comma, space or semi-colon. The ‘Fixed Width’ should be used when there’s a fixed width separation across all the cells.

Once ‘Finish’ is selected, the data will be split into separate columns.


You can now delete the irrelevant columns, or sort as needed.

Note: this split is not reversible, so usually a good habit to use Text to Columns on a copy of the data or file! Although you can concatenate the columns back together, it can be a lengthy process.


Remove Duplicates

If you have a column of data and need to filter out the unique options, this is the tool for you!

How to Remove Duplicates?

Simply select the column of data where you want to filter out duplicate information, and click the ‘Remove Duplicates’ button. A window should pop-up with the column you have selected, and just hit ‘OK’. The tool will tell you how many duplicates found and how many unique options remain. This tool will work on both numbers and text values! Note: this will “remove” the duplicate values so if you still need the original column, ensure you use this tool on a copy of the column.


Data Validation

Have you ever wondered how other people create a dropdown menu in Excel? Here’s how to do it!

How to use Data Validation?

Select the cells or column you want to create dropdown menus for. Click on the ‘Data Validation’ button and select Data Validation (again) in the submenu.

In the ‘Settings’ tab, change the ‘Allow’ option to ‘List’, and type in your dropdown menu options in the ‘Source’ field below separated by commas.


In the other two tabs, you can create a message that pop-ups when the cell is selected or create an error message. You can also use other options found under the “Allow” field to set other types of restrictions on the cell, such as limiting numbers to within a range. More info can be found here on the other options!

Excel is a great resource that can save us time and energy in our more administrative duties, but often it’s not used to its full potential. This is a quick guide for some of the many tools hidden in plain sight. But for those tools which aren’t covered, the Internet is your best friend when it comes to troubleshooting and here’s a great place to start.


Freeze Panes

Have you ever had a large table of data and when scrolling down, forgotten what the column headers were?

Freezing panes is all about locking in selected cells so they stay in place when scrolling either down or across the page.

A pane can either be the top row of cells across the page such as headers for columns, or a set number of cell rows.

How to freeze panes:

On the ‘View tab’, go to the ‘Window’ section. This will be where the Freeze Panes menu is. Click the option you need, and now you have locked in a section of the page. You can also freeze both columns and rows and the same time!


Filtering

The filtering option in Excel allows the user to automatically insert a drop down menu into a header or subheading.

How to filter a table:

After highlighting your table, simply navigate to the top right hand side of the screen and click Sort and Filter. Select the option Filter, and see drop down arrows appear in your table.

This Filter function helps to sort through and show only relevant data. This function can be used to sort by locations, names, team divisions etc.

In this example, the first column titled Letter can be changed to only include the text ‘a’.


Conditional Formatting

This is a great tool where Excel can format cells based on their contents. This makes visually identifying certain values or range of values in a large table very easy.

How to use Conditional Formatting?

First, decide what type of cells you want format. It might be dates in the last week, cells which contain a single word or blank cells. Then select the cell range which you want to conditionally format. In the ‘Home’ tab, find the ‘Conditional Formatting’ button.

Depending on what your formatting criteria is, you will select something different in this dropdown menu. For this example, we want to highlight all cells containing the word “Fail” to red so that it stands out. Click ‘Highlight Cells Rules’, and click ‘Text that contains’.

Type in your phrase into the first text box, and select the formatting type in the second box. You can already see the cell has been formatted.

You can select other rules which format based on numbers, text or dates. Also, there is an option to create a completely ‘New Rule’ which is useful when you want to format based on a custom condition. Explore the submenus under ‘Conditional Formatting’ to see what your options are, and if you ever need help, Google is the answer!


Data Tools

The following tools can all be found in the Data Tools box, found under the Data tab. These are all handy tools to manipulate data.


Text to Columns

Have you ever copied a large amount of data, pasted into Excel and the text just isn’t separated the way you want? The Text to Columns Function splits text into adjacent columns to help formatting and filtering of data!

How to use Text to Columns?

The Text to Columns function will help to split data that originally looks like:

Within the Data ribbon and select the Text to Columns function.

A pop up window will appear. Click through the steps, adjusting details if needed. The ‘Delimited’ option should be used when your data is split by a delimiter such as a comma, space or semi-colon. The ‘Fixed Width’ should be used when there’s a fixed width separation across all the cells.

Once ‘Finish’ is selected, the data will be split into separate columns.


You can now delete the irrelevant columns, or sort as needed.

Note: this split is not reversible, so usually a good habit to use Text to Columns on a copy of the data or file! Although you can concatenate the columns back together, it can be a lengthy process.


Remove Duplicates

If you have a column of data and need to filter out the unique options, this is the tool for you!

How to Remove Duplicates?

Simply select the column of data where you want to filter out duplicate information, and click the ‘Remove Duplicates’ button. A window should pop-up with the column you have selected, and just hit ‘OK’. The tool will tell you how many duplicates found and how many unique options remain. This tool will work on both numbers and text values! Note: this will “remove” the duplicate values so if you still need the original column, ensure you use this tool on a copy of the column.


Data Validation

Have you ever wondered how other people create a dropdown menu in Excel? Here’s how to do it!

How to use Data Validation?

Select the cells or column you want to create dropdown menus for. Click on the ‘Data Validation’ button and select Data Validation (again) in the submenu.

In the ‘Settings’ tab, change the ‘Allow’ option to ‘List’, and type in your dropdown menu options in the ‘Source’ field below separated by commas.


In the other two tabs, you can create a message that pop-ups when the cell is selected or create an error message. You can also use other options found under the “Allow” field to set other types of restrictions on the cell, such as limiting numbers to within a range. More info can be found here on the other options!

Page published: 29 March 2019, 15:25