Excel for Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 More...Less Show
Microsoft Office Excel has a number of features that make it easy to manage and analyze data. To take full advantage of these features, it is important that you organize and format data in a worksheet according to the following guidelines. In this article
Data organization guidelinesPut similar items in the same column Design the data so that all rows have similar items in the same column. Keep a range of data separate Leave at least one blank column and one blank row between a related data range and other data on the worksheet. Excel can then more easily detect and select the range when you sort, filter, or insert automatic subtotals. Position critical data above or below the range Avoid placing critical data to the left or right of the range because the data might be hidden when you filter the range. Avoid blank rows and columns in a range Avoid putting blank rows and columns within a range of data. Do this to ensure that Excel can more easily detect and select the related data range. Display all rows and columns in a range Make sure that any hidden rows or columns are displayed before you make changes to a range of data. When rows and columns in a range are not displayed, data can be deleted inadvertently. For more information, see Hide or display rows and columns. Top of Page Data format guidelinesUse column labels to identify data Create column labels in the first row of the range of data by applying a different format to the data. Excel can then use these labels to create reports and to find and organize data. Use a font, alignment, format, pattern, border, or capitalization style for column labels that is different from the format that you assign to the data in the range. Format the cells as text before you type the column labels. For more information, see Ways to format a worksheet. Use cell borders to distinguish data When you want to separate labels from data, use cell borders — not blank rows or dashed lines — to insert lines below the labels. For more information, see Apply or remove cell borders on a worksheet. Avoid leading or trailing spaces to avoid errors Avoid inserting spaces at the beginning or end of a cell to indent data. These extra spaces can affect sorting, searching, and the format that is applied to a cell. Instead of typing spaces to indent data, you can use the Increase Indent command within the cell. For more information, see Reposition the data in a cell. Extend data formats and formulas When you add new rows of data to the end of a data range, Excel extends consistent formatting and formulas. Three of the five preceding cells must use the same format for that format to be extended. All of the preceding formulas must be consistent for a formula to be extended. For more information, see Fill data automatically in worksheet cells. Use an Excel table format to work with related data You can turn a contiguous range of cells on your worksheet into an Excel table. Data that is defined by the table can be manipulated independently of data outside of the table, and you can use specific table features to quickly sort, filter, total, or calculate the data in the table. You can also use the table feature to compartmentalize sets of related data by organizing that data in multiple tables on a single worksheet. For more information, see Overview of Excel tables. Top of Page Need more help?What is data cleansing?Data cleansing, also referred to as data cleaning or data scrubbing, is the process of fixing incorrect, incomplete, duplicate or otherwise erroneous data in a data set. It involves identifying data errors and then changing, updating or removing data to correct them. Data cleansing improves data quality and helps provide more accurate, consistent and reliable information for decision-making in an organization. Data cleansing is a key part of the overall data management process and one of the core components of data preparation work that readies data sets for use in business intelligence (BI) and data science applications. It's typically done by data quality analysts and engineers or other data management professionals. But data scientists, BI analysts and business users may also clean data or take part in the data cleansing process for their own applications. Data cleansing vs. data cleaning vs. data scrubbingData cleansing, data cleaning and data scrubbing are often used interchangeably. For the most part, they're considered to be the same thing. In some cases, though, data scrubbing is viewed as an element of data cleansing that specifically involves removing duplicate, bad, unneeded or old data from data sets. Data scrubbing also has a different meaning in connection with data storage. In that context, it's an automated function that checks disk drives and storage systems to make sure the data they contain can be read and to identify any bad sectors or blocks. Why is clean data important?Business operations and decision-making are increasingly data-driven, as organizations look to use data analytics to help improve business performance and gain competitive advantages over rivals. As a result, clean data is a must for BI and data science teams, business executives, marketing managers, sales reps and operational workers. That's particularly true in retail, financial services and other data-intensive industries, but it applies to organizations across the board, both large and small. If data isn't properly cleansed, customer records and other business data may not be accurate and analytics applications may provide faulty information. That can lead to flawed business decisions, misguided strategies, missed opportunities and operational problems, which ultimately may increase costs and reduce revenue and profits. IBM estimated that data quality issues cost organizations in the U.S. a total of $3.1 trillion in 2016, a figure that's still widely cited. What kind of data errors does data scrubbing fix?Data cleansing addresses a range of errors and issues in data sets, including inaccurate, invalid, incompatible and corrupt data. Some of those problems are caused by human error during the data entry process, while others result from the use of different data structures, formats and terminology in separate systems throughout an organization. The types of issues that are commonly fixed as part of data cleansing projects include the following:
What are the steps in the data cleansing process?The scope of data cleansing work varies depending on the data set and analytics requirements. For example, a data scientist doing fraud detection analysis on credit card transaction data may want to retain outlier values because they could be a sign of fraudulent purchases. But the data scrubbing process typically includes the following actions:
The cleansed data can then be moved into the remaining stages of data preparation, starting with data structuring and data transformation, to continue readying it for analytics uses. These metrics can be used to measure data quality levels in connection with data cleansing efforts.Characteristics of clean dataVarious data characteristics and attributes are used to measure the cleanliness and overall quality of data sets, including the following:
Data management teams create data quality metrics to track those characteristics, as well as things like error rates and the overall number of errors in data sets. Many also try to calculate the business impact of data quality problems and the potential business value of fixing them, partly through surveys and interviews with business executives. The benefits of effective data cleansingDone well, data cleansing provides the following business and data management benefits: Data cleansing provides these benefits to organizations.
Data cleansing and other data quality methods are also a key part of data governance programs, which aim to ensure that the data in enterprise systems is consistent and gets used properly. Clean data is one of the hallmarks of a successful data governance initiative. Data cleansing challengesData cleansing doesn't lack for challenges. One of the biggest is that it's often time-consuming, due to the number of issues that need to be addressed in many data sets and the difficulty of pinpointing the causes of some errors. Other common challenges include the following:
Data cleansing tools and vendorsNumerous tools can be used to automate data cleansing tasks, including both commercial software and open source technologies. Typically, the tools include a variety of functions for correcting data errors and issues, such as adding missing values, replacing null ones, fixing punctuation, standardizing fields and combining duplicate records. Many also do data matching to find duplicate or related records. Tools that help cleanse data are available in a variety of products and platforms, including the following:
Learn how strong data governance policies can help organizations prevent data silos and ensure better quality data. This was last updated in January 2022 Continue Reading About data cleansing (data cleaning, data scrubbing)
Dig Deeper on Data governance
What type of services does the USPS offer to most foreign countries?USPS provides reliable and affordable international delivery to about 180 countries through Priority Mail International. Most Priority Mail International shipments include tracking and insurance: up to $100 for nonnegotiable documents and up to $200 for merchandise.
Which of the following is a good practice for managing emails?Some email management best practices include using filters and labels, organizing your inbox, limiting the number emails you check per day, archiving old emails, and unsubscribing from unnecessary emails.
How many spaces should be left above the signature block to allow for the signature?Your signature should be in blue or black ink. Allow four vertical spaces for your signature.
Which of the following is the most common format of letter styles that is used in the medical office?Chapter 9. |