8 Benefits of converting DATA into excel TABLE

Excel Tables are powerful data objects which make life easier for users. It improves data accessibility and analysis with the help of derived rows and columns. Before jumping to the benefits, I want to show how we convert data to an excel table. The sample data used in my example has been taken from https://www.contextures.com/xlsampledata01.html.

Converting data to excel table -

Select the data range, go to the Home tab in the ribbon, select Format as Table, and select any format. It will convert your data to an excel table object.

This is what the converted table looks like -

Benefits of this conversion -

1. Sorting and Filtering is more accessible - You can click on the small dropdown next to the column name that you want to sort or filter from.

2. Addition of rows and columns is easier - You can simply add adjacent rows and columns and excel will automatically make them a part of the table.

3. Frozen headers - Table headers are by default frozen. Here I have scrolled till the 9th row, still, the column headers are still visible.

4. Attractive formatting - You can get beautiful table formats with different colour bands.

5. Dynamic Data - Charts, Graphs, and Pivot tables will automatically be updated as we add new rows to the table. The moment you add new data and refresh your chart or pivot table, it will automatically reflect.

6. Fomulae stay consistent throughout - Usually when you write a formula to calculate a field, you need to drag that formula down to all the rows. But in case of tables, you can insert the formula in the first row, and excel will apply the formula to all the following rows by its own. You can also see that the referencing in the formula is based on column name rather than cell coordinates.

7. Column referencing made easier - Instead of cell coordinates in the formula, you can directly use the table name and its column names. You can check the formula I used to evaluate average age of the players.

8. Calculating Total Row - When you hit Ctrl + Shift + T by selecting any of the table cell, one extra row is added at the bottom. That is a Total Row, which lets you calculate various things about the data.

In this example, I am trying to calculate the maximum of height among the players (the tallest player).

I hope you understood all the benefits. But these can only be termed as benefits, if they come in handy for us. For that, we need to practice and use them in our day to day work.

Keep Analyzing!!

Comments

FOOD DELIVERY DATA VISUALIZATION PROJECT

I will be sharing a very interesting project with you all, where I will use Power BI to analyze the data of a Meal Delivery organization and answer a few of their business questions. While creating the dashboard, I will touch upon a few concepts named 'Calculated Column', 'Calculated Measures', etc. Wherever needed, I will mention some GCPs (Good Case Practices) in the process. ABOUT DATA The data used in this analysis has been taken from the internet and has no direct reference to any organization. It consists of three CSV files - 1. DEMAND DATA - It contains the historical data demand of various products by the centers. 2. MEAL LOOKUP - It contains the details about individual meals. 3. CENTER LOOKUP - It contains information about the various centers delivering the meals. SOLUTION The very first step before we jump into any analysis is cleaning the data and creating the schema. I won't be covering the data cleaning and schema generation steps here as it goes...

Git Basics and Local Usage

This blog is going to be on Git and a couple of very basic commands to run Git locally in our system. But before that, it is better to bust the myth, that “Git and GitHub are the same things”. This is a very beginner-level misconception to assume Git and GitHub to be the same thing. Though they are related to each other, both of them serve different purposes. According to Wikipedia, Git is software for tracking changes in any set of files, usually used for coordinating work among programmers collaboratively developing source code during software development. In simple terms, Git is a version control system. While on the other hand, GitHub is a hosting platform where you can host your git repositories and share them with others. I guess the basic definition is clear, now we can take it to the next step. Downloading Git in your local system is very easy. You may follow any of the tutorials present on the Internet to install Git or maybe you can simply start installing on your ow...

Reminiscence

I was sitting in the second row as our class teacher called roll numbers for attendance. With each roll number called, I would turn around to see the face, which is supposed to be familiar as I meet them every day, but surprisingly brought a smile to my face as if I am meeting them after a long time. My best friend was sitting beside me too, just like any perfect day. Everyone talked to each other without our teacher stopping us, and the classroom seemed like a warm and cordial place to socialize. As time passed, not realizing the exact reason, I didn't want the period to get over. I didn't want that space to be disturbed. There was an urge in me to stop the time then and there. The sound of my alarm clock woke me up. Suddenly everything started to make sense. My "turning around" to see the faces, my "urge to stop time" everything seemed to be coherent. I was dreaming about my school days after five years of graduation. That's when I realized that I ha...

Datareads

Search This Blog