Excel Beginners

Data Types And Formatting: Step 2 To Mastering Spreadsheets

A Final Note & Activity

A Final Note

In this guide we have covered a set of topics which are often overlooked and undervalued. We have looked at how Excel understands information, and shown tangible ways to troubleshoot for when Excel misunderstands information. We have also taken the time to understand the importance of formatting data without manipulating it. The combination of these three things results in a skillet that enables creativity and reliability in one’s own work.

It will frequently be the case where information is provided that is not clear, is improperly formatted (dates/times/duration especially!), or does not follow the data type you would expect of it. Those are the moments where having the tools necessary to understand the problems you are faced with will make you the individual who is the most agile at resolving said problem.

A recommendation I have for users planning on integrating custom formatting into their projects and work documents. If the document in question is something only you will edit, have fun and find the best formatting for your document. If it will be used as a template/report which other people will need to fill in with new information, try to restrict custom formatting to areas which other users will not have to edit, such as a dashboard. If the document is going to be used and edited by users and there is no output tab, be understanding of the fact that things will happen, and the formatting will either disappear or can cause confusion. Be clear in your formatting and always try to use it to reduce the complexity of a document.

With this guide is included a document that contains examples and situations which span the various topics covered here. Using this as practice, make sure you have an understanding of data types and the implications of using one over another. In many circumstances you will not have the option to chose between the data types you want to use, however situations may be improved by making modifications to allow for a different data type to be used.

An Activity

Let’s put your knowledge to the test! This activity will specifically be looking to flex your skills and creativity with custom formatting. We will start by opening a blank document, and then following a series of instructions that will lead us all to the same final output.

Instructions

• Let’s begin in cell A1 (the top-leftmost cell in the document)

  • Enter the value 33113 and use custom formatting to have the cell display “3XC3113NCY”

• In cell A2

  • Enter the value 43941 and use custom formatting to have the cell display the date in the following format:

    • ‘shorthand day of the week’ – ‘date with 2 digits’ ‘name of the month’ ‘full year’

• In cell A3

  • Enter the value 0.127916 and use custom formatting to have the cell display the time in the following format:

    • ‘seconds – only display digits as necessary’ = ‘minutes – always display both digits’ * ‘hours – only display digits as necessary’ in the 24-hour format

• In cell A4 and A5

  • Enter the values 2349.02619 and 95.1228 respectively and use custom formatting on both cells so that large values have a thousands separator, decimals are represented as fractions with up to three digits in both the numerator and denominator, and both cells will be perfectly in line with one another when the cells are left-aligned.

• In cell A6

  • Enter the text “are” and use custom formatting to append the words “You” and “awesome” as the prefix and suffix to the entered txt so that it follows this format:

    • Prefix ‘blank space’ text ‘blank space’ suffix

And that’s it for this one! Compare your final outputs for cells A1 to A6 with me by downloading the results PDF file HERE, or the results Excel file HERE.