1. Text and date functions
Let's continue by focusing on utilizing specific text and date and time functions in Excel for data preparation purposes.
2. Data types
Let's look at some examples of the data types that these two categories refer to and the functions they might have. The text category refers to functions that can be used on text data types, such as a column showing a name.
3. Data types
The date and time category contains functions that can be applied to date and time data, such as the date a test was taken.
4. Text functions - LEN()
The LEN function allows you to count how many characters are in a text string, including the spaces and special characters.
For data preparation, it is a great way to help you identify if there are trailing spaces in your cells or identify anomalies in the data. For example, if you are dealing with phone numbers from a specific country, you can use LEN to check they are all the same length.
5. Text functions - CONCAT()
Next up is CONCAT, which is short for concatenate, meaning to link things together in a series or chain. The function can join text from multiple ranges, strings, or inputs. However, it doesn't insert delimiters like a comma, which can be entered manually if required. This function is handy for merging information into a single cell or column. For example, if you have delivery address information in separate columns, you can use CONCAT to create one address column.
6. Text functions - TEXTJOIN()
Finally, TEXTJOIN is similar to CONCAT. It combines text in the same way, but it also allows you to specify the delimiter of your choice between each value and include cells in the output if they are empty. You can use this function to create a URL from components in different cells or generate a unique identifier for something like product sales.
7. Date and time functions - TODAY()
Now time for some date and time category functions.
First up, the function TODAY. This will display the serial number of the current date the function is used on. It comes in handy for date calculations and data filtering. For example, the function can indicate to users when a report or table was last updated by the person who owns it.
8. Date and time functions - WEEKDAY()
Next is WEEKDAY, a function that will give you an integer for the day of the week based on an inputted date. You can adjust the start of the week so that your output suits your definition of a week. Say you have delivery dates for items your business stocks. You can use the WEEKDAY function to check the day of the week that the items will arrive on based on the delivery date.
9. Date and time functions - WORKDAY()
Finally, WORKDAY will help you calculate the date based on the number of working days before or after a starting date. You are also able to exclude holiday dates from the calculation. It is useful for project planning. Suppose a manager is trying to plan upcoming projects. In that case, they can use the start date of the current project and the average days taken for the project's completion to calculate when each employee will next be available.
10. Protection and security
As well as performing data preparation in your role at DataCo, preparing your Excel workbook for sharing will also be a requirement.
When sharing your Excel workbooks, protecting any work you have done or any sensitive data is best practice. Excel provides various options for protecting your data.
There are three different options: File-level, Workbook-level, and Worksheet-level.
11. Protecting the worksheet
In this chapter, you will learn how to protect a worksheet, which allows you to control how users interact with a sheet.
This can involve enabling which users can add or remove rows and columns. It can also involve whether the user can sort or auto-filter the data.
It can also focus on other elements, such as cells, ranges, and formulas.
12. Let's practice!
It's time to put this newly acquired knowledge to some good use.