Mike Harrison

Want to boost your productivity and outshine your colleagues with Excel? With a few easy tips, you can master Excel and make your workflows faster and smarter. Let’s dive into some powerful features that will set you apart.

First things first. Excel should contain structured data, if it’s unstructured, you might as well be using Word or PowerPoint.

So, how do we structure it?

Create a Table

Let’s assume you already have some data available like this:

You can select all of the data by clicking on any cell that contains data and pressing ‘Ctrl A’. (If you select an empty cell ‘Ctrl A’ will select every cell in the spreadsheet).

To create a Table, press ‘Ctrl L’ or go to Insert > Table. Tick ‘My table has headers’.

You can change the look of your table by clicking ‘Table Design’ in the tool bar, and then selecting one of the Table Styles.

Want to sort or filter the data? No problem, click the arrow button next to the column header.

If you want to add more data to the table, just start typing in the row below it. It will expand to include the data. If you want to add todays date type ‘Ctrl :’

Conditional formatting

Let’s highlight some cells based upon the value they contain. In this example we’re going to format everything that’s ‘in stock’ green, and everything that’s ‘out of stock’ red.

Select all cells you want to conditionally format, click the first value, then ‘Ctrl Shift + Down Arrow’ to select every row in the column (the right arrow will select every column in the row).

Click Conditional Formatting > Highlight Cell Rules > Equal To.

Then type ‘Yes’ in the field, and click ‘Done’.

IF

To check a condition you can use =IF. As an example we can add a column called ‘Stock notification’.

In the first cell of the column type:

=IF([@[In Stock]]="Yes","This item is in stock","This item is out of stock")

You don’t need to type [@[In Stock]], just click the cell you want to check.

Tapping enter, will complete the calculation for every other row in the table.

You can refer to cells in the text displayed like this:

=IF([@[In Stock]]="Yes",[@Product]&"'s are in stock",[@Product]&"'s are out of stock")

You can total the figures by clicking ‘Table Design’ then, ‘Total Row’. To change the calculation applied click the total cell, and click the arrow alongside it.

XLOOKUP

If you want to look up data in one table and include it in another you can use XLOOKUP.

In this example we’re going to look up the Description using the Product name.

=XLOOKUP([@Product],Table2[Product],Table2[Description])

Hit enter, and it will find all of the Descriptions from Table 2.

If there’s data missing, rather than return an error you can use ‘IFERROR’ to remove #N/A and replace it with something more useful.

=IFERROR(XLOOKUP([@Product],Table2[Product],Table2[Description]),"Description not found")

Everything is simpler with structured data.

Better still, you can now use your Excel table as a datasource for tools like PowerBI.

If you’re creating an Excel table for others to populate with data are you sure you wouldn’t be better using a SharePoint list?

Conclusion

By structuring your data and using these tools, tips and tricks, you’ll not only work more efficiently but also open the door to advanced data analysis and automation options.

Like this, then subscribe to our newsletter to keep up to date.