Excel spreadsheets can quickly give the data in your spreadsheet an organized look and make it easier to manage. After formatting a set of cells as an Excel spreadsheet, you can sort and filter the data they contain and use structure references in related formulas.
Creating an Excel spreadsheet is a simple task, but despite this, Excel spreadsheets are a cornerstone of many spreadsheets. Read on to find out how to create these handy spreadsheets in Excel!
What are spreadsheets in Excel?
Even though Excel spreadsheets look like large spreadsheets, a range of cells is not considered an Excel spreadsheet until you format it as such. Excel spreadsheets consist of a heading, which is usually the first row in the spreadsheet, and the columns under the headings. You can use the header to filter the data in your Excel spreadsheet.
Once you’ve formatted your range of cells as a table, not only can you easily sort and filter the data based on the headings, but you can also use structure references in the formulas. Structure references are references to a structure in the table, such as B. a row or a column, and not a cell.
A formula that uses structure references can output the results to a structure in the spreadsheet, so multiple cells are affected by a single formula. Although this may sound similar to array formulas, the output of a formula with structure references is not necessarily an array.
Excel spreadsheets are similar to Excel data tables in name, but these two are very different. Data tables are part of Excel’s what-if analysis toolkit. For more information on data tables, see our article on how to use the data table in Excel to compare results.
How to create a table in Excel
Despite its importance, creating a spreadsheet in Excel is easy. In this example, we have the sales made by multiple employees over two quarters. The goal is to format the data as an Excel spreadsheet and then calculate the total sales using the SUM function in Excel with structure references.
- Select your range of cells. In this example it is A1:D11.
- In which home tab, click Format as table in which styles Section.
- Choose a style for your table. Note that if your cells already have styles, the table style will be ignored.
- Check My tables have headings if you have already output headers. If not, you can disable this option to have Excel add headers automatically. We will check this.
- click OK.
Voila! You have an Excel spreadsheet! You can now use the headers to sort and filter your data however you like. However, our work here is not done yet. It’s time to create a formula to calculate the total sales for each employee:
- Select the first cell where you want to display the formula results. This will be cell D2 in this example.
- In the formula bar, enter the following formula:
=SUM(Table1[@[Q1 Sales]:[Q2 Sales]])
It’s best if you type a formula that contains structure references rather than pasting them because the headings are clear. As soon as you type the at sign after the bracket, Excel prompts you with the available headers Table 1. In this formula we have called the TOTAL Function and structure references made Q1 sales and Sales in the second quarter Pillar. As a result, this formula sums the numbers in these two columns together.
- Press Enter.
As soon as you press Enter, Excel immediately sums the columns and displays the results. Notice that all the cells have been filled automatically because you used a reference structure.
Excel spreadsheets make analysis easier
Excel is a powerful spreadsheet application designed to help you organize and analyze data. Excel spreadsheets do just that for you, organizing the data and making analysis easier. Using the headings in your spreadsheet, you can sort and filter your data with just a few clicks, without having to use any functions.
Excel spreadsheets also give you the benefit of using structure references in your formulas, which can make bulk-scale calculations much easier. Now that you know how to create and use Excel spreadsheets, it’s time to put those newfound skills to work in your spreadsheets!
This article was previously published on Source link