IjtabaHussain

Hi, This is Ijtaba

How to create multiple worksheets from a list of cell values without VBA

Creating Worksheets from a List of Names

Creating multiple worksheets from a list of values is very easy and it could be created by multiple methods. In this article, we will explore ways of creating it by using the following method. Though manually creating worksheets will be very time-consuming and using some tricks could make easy the task.

The first step in creating the task is to list all the values that we wanted to name the worksheets.

list the names for the worksheet
list the names for the worksheet

After listing the name insert a pivot table by using the newly created list as data range. After inserting the pivot table put the name of worksheet column in pivot table Filter as illustrated in the … Continue Reading

How to create a dynamic table in excel to summarize data

Creating a dynamic table in excel

In this example, we will be creating a dynamic table in excel from a dataset. The dataset has 3 columns out of these three 2 are categorical values while the third is numerical. Now we wanted to create a dynamic table that will have these two columns as rows and columns. Also, we wanted to summarise our data by as count of entries of these two categorical columns. Dataset is illustrated in the below image.

Creating a dynamic table in excel
Creating a dynamic table in excel

To create this kind of excel dynamic table we will first convert our dataset into the excel table. You must be wondering why we are converting this into the table, this is because … Continue Reading

Difference between Primary Key and Foreign Key

Difference between Primary Key and Foreign Key

A primary key is used to ensure data in the specific column is unique. It is a column that cannot have NULL values. It is either an existing table column or a column that is specifically generated by the database according to a defined sequence.

Difference between primary key and foreign key
Difference between primary key and foreign key

What is Primary key:

Example: Refer to the image above:
STUD_NO, as well as STUD_PHONE both, are candidate keys for relation STUDENT but STUD_NO can be chosen as the primary key (only one out of many candidate keys).

What is a Foriegn key:

Example: Refer to the above image
STUD_NO in STUDENT_COURSE is a foreign key to STUD_NO in STUDENT relation.… Continue Reading

SQL Sample Database

SQL Sample Database

In the coming articles, we will be using our sample database which is an HR database. It has multiple seven tables which will be interlinked to get answered our question.

The description of tables are as mentioned below

The jobs table stores the job data including job title and salary range.

The dependents table stores the employee’s dependents.

The employees table stores the data of employees.

The departments table stores department data.

The regions table stores the data of regions such as Asia, Europe, America, and the Middle East and Africa. The countries are grouped into regions.

The locations table stores the location of the departments of the company.

The countries table stores the data of countries where … Continue Reading

What is SQL

Introduction to SQL language

SQL is a programming language that is used to manage data stored in a relational database management system (RDBMS).

SQL pronounced as /ˈɛs kjuː ˈɛl/ or /ˈsiːkwəl/. SQL full form is Structured Query Language.

SQL has three components:

  • Data Defination Language (DDL): The data definition language deals with the schema/Table/View creation and modification like, CREATE TABLE statement allows us to create a new table in the database and the ALTER TABLE statement helps in changing the structure of an existing table.
  • Data Manupulation Language(DML): The data manipulation language helps to query data such as the SELECT statement and to update the data such as INSERTUPDATE, and DELETE statements.
  • Data Control Language (DDL): The
Continue Reading

New Excel Functions in Excel 2021: 5 Awesome Excel formulas

New Excel Functions in Excel 2021

Every time when Microsoft excel launches a new version new excel functions are also been added there. Excel 2021 also has new functions like  FILTER, SORT, SORT BY, UNIQUE, SEQUENCE. These all excel functions help us reducing our processing time. Let us see in brief what these functions are and how they work. Let us go through these New Excel Functions in Excel 2021.

FILTER function in excel

Excel FILTER function allows us to filter a range of data from a dataset based on some criteria. It is one of the New Excel Functions in Excel 2021. Let us understand first the syntax of Filter formula excel.

New Excel Functions in Excel 2021
New Excel Functions in Excel 2021

What … Continue Reading

Project Charter: What is a six sigma project charter and how to create it in 7 steps[Free download]

What is project charter in six sigma

Project charter in six sigma very important requirement for the project. It is a living document for the project and it lays out every important project variable. The creation of a project charter is one of the first steps in six sigma project. It is a part of the Define phase of DMAIC process. This includes everything from who is participating in the project, to the project goals. Let us take few minutes to understand the different components of project charter step by step.

Step-1: Defining the project name

The very step in making a Project Charter is to name the project. The project name should reflect what the project is looking … Continue Reading

Flash fill in excel: Everything You Need To Know About Flash Fill In Microsoft Excel [5 Examples]

Flash fill in excel

What is Flash fill in excel?

Flash Fill is a data tool in Microsoft Excel that allows us to combine, extract or transform data based on a few examples. We only need to provide a couple of examples of the results we want, excel will guess the pattern and fill in the rest of the data for us.

What is Flash fill in excel?
What is Flash fill in excel?

In the above gif file, we can see that we only provided one row of input, and after using Flash fill it automatically separates values.

How to Enable Flash Fill in Excel?

To enable flash fill in excel go to File then Options. After that select Advanced and … Continue Reading

How to refresh Pivot Table: 2 ways to refresh pivot tables in excel

How to refresh Pivot Table

In our previous post we learnt about how to create pivot table in excel. Now in this post we will try to understand different ways to refresh pivot table when new data is being added in the data range of pivot table. There are different ways to refresh excel pivot table, but we can categories them in two different ways.

  • Manually and
  • by VBA code.

How to refresh pivot table manually

1) To refresh a pivot table manually first select any cell on excel pivot table then go to PivotTable Analyze tab then in Data column click on Refresh. Now we ill get 2 options Refersh and Refresh all.

How to refresh Pivot Table
How to refresh Pivot
Continue Reading

Table in Excel: How to create excel table step by step by 2 methods

How to create excel table

Table in excel is a powerful feature that helps us in organizing our data by grouping data together. It is very quick to create a table in excel by just clicking a few keys or using a shortcut key and converting your flat data into a table with a lot of benefits.

The benefit of using excel table is as below but it is not limited to these only:

  • Clean Formulas : When we are using excel table feature then it become very easier to read forumals even it is very complicated.
  • Referencing a range : It is very easy to reference a range as every table has its own name.
  • Auto expand : It
Continue Reading