Advanced Excel Charts: Sooo Muuch Data – Analysis Needed !

Data visualization is the presentation of data in a pictorial or graphical format.

Today, we see data visualization being embraced like never before. Organizations that know and understand it, love its very existence. Since Big Data is a popular lingo in the tech world today across industries ranging from finance to communications, the need to drive better business decisions is the need of the hour. As a result, organizations are looking for better data visualization tools, to turn their essential load of data into something that is more comprehensible.
Excel charts are an efficient means to visualize the data to convey the results. In addition to the chart types that are available in Excel, some application charts are popular and widely used.
The importance of advanced charts
  • They provide consolidated information in a single chart that makes it easier to compare more than one data set and make decisions quickly
  • They allow us to customize the look and feel of the charts
Lets see some important Advanced Charts in Excel:

1. Band Chart

A Band chart is a Line chart with added shaded areas to display the upper and lower boundaries of the defined data ranges.
Band charts provide by far more context to your visualization and more insight into your data. Especially if you have to explore unknown data sets.

Why is a band chart more efficient than a line chart? Because it provides more context. Unlike a single line chart displaying only the development of the selected data series over time, a band chart provides a variety of additional information at a glance.

2. Waffle Chart:

A Waffle chart is a square divided by 10×10 cells. The value is displayed as percentage, so you can clearly see the difference down to 1%.
Pie charts are often criticized because while at the outset they do seem to convey the information, people still have a hard time comparing the angles and sizes of each element.
Making pie healthier resulted in the Waffle chart! A Waffle chart is essentially a squarified pie. Since it does not involve angles, it’s easier for the reader to compare accurately as well as evoke emotional comparisons.
You can use it with just one measure…
or with a category.

3.Heat Map

A Heat Map is a visual representation of data in a Table to highlight the data points of significance.
For example, in the dataset below, I can easily spot which are the months when the sales were low (highlighted in red) as compared with other months.
In the above dataset, the colors are assigned based on the value in the cell. The color scale is Green to Yellow to Red with high values getting the green color and low values getting the red color.

4.Waterfall Chart:

A Waterfall chart is a form of data visualization that helps in understanding the cumulative effect of sequentially introduced positive or negative values.
The columns are color coded so you can quickly tell positive from negative numbers. The initial and the final value columns often start on the horizontal axis, while the intermediate values are floating columns. Because of this “look”, waterfall charts are also called bridge charts.

5.Pareto Chart:

Pareto chart is widely used in Statistical Analysis for decision-making.It represents the Pareto principle, also called 80/20 Rule, which states that 80% of the results are due to 20% of the causes.
A Pareto or sorted histogram chart contains both columns sorted in descending order and a line representing the cumulative total percentage. Pareto charts highlight the biggest factors in a data set, and are considered one of the seven basic tools of quality control as it’s easy to see the most common problems or issues.

Want to learn Data Analytics?

SQLite Overview with Installation_pRoCess and self-start examples:SQL – Where all to UsE ?

sqlite image

SQLite is embedded relational database management system. It is self-contained, serverless, zero configuration and transactional SQL database engine. SQLite is free to use for any purpose commercial or private. In other words, “SQLite is an open source, zero-configuration, self-contained, stand alone, transaction relational database engine designed to be embedded into an application”.

SQLite is different from other SQL databases because unlike most other SQL databases, SQLite does not have a separate server process. It reads and writes directly to ordinary disk files. A complete SQL database with multiple tables, indices, triggers, and views, is contained in a single disk file. SQLite was designed by D. Richard Hipp for the purpose of no administration required for operating a program in 2000.

SQLite Features

Following is a list of features which makes SQLite popular among other lightweight databases:

  • SQLite is open-source. License is not required to work with it.
  • SQLite doesn’t require a different server process or system to operate.
  • It facilitates you to work on multiple databases on the same session on the same time.
  • SQLite doesn’t require configuration. No setup or administration required.
  • SQLite is a cross-platform DBMS.
  • The length of the columns is variable and is not fixed. It facilitates you to allocate only the space a field needs.
  • Provide large number of API’s: SQLite provides API for a large range of programming languages.
  • SQLite is written in ANSI-C and provides simple and easy-to-use API.

SQLite Limitations

There are few unsupported features of SQLite which are listed below:-
  • Right Outer Join and Full Outer Join is not Supported.
  • The RENAME TABLE and ADD COLUMN variants of the ALTER TABLE command are supported. The DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT are not supported.
  • FOR EACH ROW triggers are supported but not FOR EACH STATEMENT triggers.
  • VIEWs in SQLite are read-only. You may not execute a DELETE, INSERT, or UPDATE statement on a view.
  • The only access permissions that can be applied are the normal file access permissions of the underlying operating system.

SQLite Installation

SQLite doesn’t require configuration. No setup or administration required. Lets see how to install SQLite on different Systems such as Windows, Linux.

Install SQLite on Windows

Step 1: Go to SQLite official website download page And download precompiled binaries from Windows section.
Step 2: Download the sqlite-dll and sqlite-shell zip file. Or file.
Step 3: Create a folder C:>sqlite and unzip above two zipped files in this folder, which will give you sqlite3.def, sqlite3.dll and sqlite3.exe files.
Step 4: Add C:>sqlite in your PATH environment variable and finally go to the command prompt and issue sqlite3 command, which should display the following result.
sqlite shell

Install SQLite on Linux

Today, almost all the Linux OS are being shipped with SQLite. So you just issue the following command to check if you already have SQLite installed on your machine.
SQLite version 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
If you do not see the above result, then it means you do not have SQLite installed on your Linux machine. Follow following Steps
Step 1: Go to SQLite download page and download sqlite-autoconf-*.tar.gz from source code section.
Step 2: Run the following command :
$tar xvfz sqlite-autoconf-3071502.tar.gz
$cd sqlite-autoconf-3071502
$./configure --prefix = /usr/local
$make install
The above command will install SQLite on Linux.

SQLite Commands

SQLite commands are similar to SQL commands. There are three types of SQLite commands:
DDL: Data Definition Language
  • CREATE: Creates a new table, a view of a table, or other object in database.
  • ALTER: It is used to modify an existing database object like a table.
  • DROP: Deletes an entire table, a view of a table or other object in the database.
DML: Data Manipulation Language
  • INSERT: This command is used to create a record.
  • UPDATE: It is used to modify the records.
  • DELETE: It is used to delete records.
DQL: Data Query Language
  • SELECT: This command is used to retrieve certain records from one or more table.

SQLite Create Table Command

In SQLite, CREATE TABLE statement is used to create a new table. While creating the table, we name that table and define its column and data types of each column.
CREATE TABLE database_name.table_name(  
column1 datatype PRIMARY KEY(one or more columns),
column2 datatype,
column3 datatype,
columnN datatype,
ID int PRIMARY KEY not null,
Address TEXT,
You can view the tables created in SQLite using .table command and .schema command is used to view the table details.

sqlite create table command

SQLite Insert Query

In SQLite, INSERT INTO statement is used to add new rows of data into a table. After creating the table, this command is used to insert data into the table.
There are two types of basic syntaxes for INSERT INTO statement:
INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)    
VALUES (value1, value2, value3,...valueN);
Here, column1, column2, column3,…columnN specifies the name of the columns in the table into which you have to insert data.

You don’t need to specify the columns name in the SQlite query if you are adding values to all the columns in the table. But you should make sure that the order of the values is in the same order of the columns in the table.
Then the syntax will be like this:
INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);
Inserting values by first method:
INSERT INTO SCTPL (id, name,address)   
VALUES (101, Suven Consultants,Chembur);

Inserting values by second method:
INSERT INTO SCTPL VALUES (102,Suven Consultants,Thane);


In SQLite database, SELECT statement is used to fetch data from a table. When we create a table and insert some data into that, we have to fetch the data whenever we require. That’s why select query is used.
SELECT column1, column2, columnN FROM table_name;
SELECT * FROM table_name;

Do you want to learn Database Programming?