The art of a perfectly formatted document, part II – spreadsheets

Photo by Jason Coudriet on Unsplash

Since I‘ve started exploring the basic skills last month, it is interesting to see how I could see more and more situations when people lack these skills.

Could it be that the skills and abilities get lost once the person is promoted? Could it be that people forget what made them worthy of a promotion in the first place, including skills?

Here are my 10 essential Excel skills at the core of a successful and efficient EA working routine.

Basic formula use

Sum, subtract, multiply, divide, calculate a percentage of, see how many hours have been spent on the project, calculate monthly salaries, set automatically updated date and highlight the results that exceed the norm – these are basic excel formulas primarily used in the office. Feel free to add anything I might have missed in the comments 🙂

Such formulas as countif (counting cells with specific properties or certain entries) and sumif (adding cells with particular properties) have earned my special sympathy.

Managing worksheets

Worksheets are the main building blocks of Excel documents, and it is up to you to use them creatively.

Wrap text

Let‘s state the obvious – Excel is not a text processor. It will not automatically expand the length of the cell to make sure it fits the size of the text you have just typed in. You will have to do it yourself 🙂

Cell format

More about Excel – you have to tell what type of content you are putting into the cells if you want Excel to treat that content appropriately.

Conditional formatting

Let Excel do the work for you – use conditional formatting to define what data you‘d want to see highlighted. Hence, you never miss a higher price or an insufficient amount of working hours.

Format painter

You can easily copy and apply the format of one cell range to another cell range elsewhere in the spreadsheet by using the Format Painter function.

Drop-down lists

Drop-down lists are simple to use, easy to apply, ensure the input is unified across the document. They are convenient if your other colleagues will update your table later.

Applying and using filters

During the few last years, I have been doing a lot of reporting. I have learned that filtering seems to be one of the most underestimated features in Excel. Find sales agreements signed in 2011 or sales deals closed by a specific salesperson in just a few clicks!

Print area

Excel has a handy, easily accessible function to view the document in Page Break Preview mode. Just turn the view on, and you will never print the last column of the table on another page anymore.

Another favourite of mine is the option to set title rows to be printed on each page. Working with a printed version of a data table has never been more convenient.

Charts and sparklines

Excel is excellent to do so tool if you need to quickly and easily visualise some data. Charts are so much more illustrative than the data lists.

Worthy of a separate mentioning are sparklines – tiny chart within a worksheet that visualises the data trends.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s