FunctionsConditional Formatting for visual data analysisLearn about data visualization & charting

Excel Quiz Questions & Answers - 2 (Basics)

This is our second set of quiz questions on basics of excel.

Some of the topics covered in this set are:

Do you know the basics of conditional formula writing(If statements)

Do you know your operators(Add, Substract, Multiply, divide, percentages etc)?

Do you know basics of editing?

So, let's put our excel skills to this breezy little test:

Questions:10
Attempts allowed:Unlimited
Available:Always
Pass rate:75 %
Backwards navigation:Allowed

Excel Quiz Questions & Answers - 1 (Basics)

This is our first set of quiz questions on basics of excel. 

Some of the topics covered in this set are:

Do you know the basics of excel worksheet layout (Columns & Rows marking, order etc)?

Do you know the basics of conditional formula writing(If statements)

Do you know your operators(Add, Substract, Multiply, divide, percentages etc)?

Do you know basics of editing?

Are you aware of ways of inserting Charts & their basics?

 

Questions:10
Attempts allowed:Unlimited
Available:Always
Pass rate:75 %
Backwards navigation:Allowed

Dynamic Ranges & Charts using Excel Tables

We frequently deal with data that is supposed to grow with time (product catalog, sales records etc), how do we write auto correcting formulas for such data? One very good solution lies in using excel tables to do this.

Suppose a stand-alone store owner decides to keep a record of all the items sold for the day in excel. The data looks like following:

Basic data that is likely to expand as we go

Excel Tables: Manage your data like a pro

Excel is all about tables (the two dimensional sheets), and we deal with tabular form data all the time. Due to this, a specific table format was introduced from excel 2007 onwards. Excel table feature help us in several ways, but the crucial ones are:

Making Histogram chart in excel

Histogram looks similar to a column charts, but rather than showing all data points individually, they group data points into ranges. They work well when we want to see the distribution of numbers in a range.

To explain the concept of Histogram, let’s consider a class of 30 students and their marks in a subject.

If we simply plot these marks using a column chart, it will make little sense as shown in the left side image below:

Stacked Column Charts

We already understand the clustered column charts in excel as we have used this option in all the previous column chart tutorials. This is the first option under column charts and is called so as all the columns for a particular category value are clustered.

Another requirement could be to have all the columns for a category label to be clubbed vertically (making a single column) rather than standing beside each other. This very option is called a stacked column chart.

This difference is shown in the image below:

Basic Formatting options to dress up your charts

Notice that in excel 2013, when you click on the chart, relevant formatting options are automatically shown in the right panel. In previous versions (& also in excel 2013), the same can be accessed through right clicking on the elements and selecting format options from the right click menu.

Chart formatting option

Understanding Column Charts

We have used column charts as example in all of the previous tutorials (to cover basics of charting),

Still for sake of completeness, let’s recap the steps required to insert a column chart.

As an example, let’s say we have the following average occupancy data for a hotel chain, and we want to represent this in a chart:

Column Chart data

Select the whole data & the headers (A2:F3) -> Go to Insert-> Column->Click on Clustered Column

Understanding Excel charts (Series data and Category axis labels)

Before we dig into different chart types in excel, it will be good to understand the way excel handles chart data. We’ll straight away dive into an example to explain this:

Suppose we have the following data (Gender-wise numbers of graduating students) and we want to represent this in a chart.

Basic formula writing in Excel

In your daily routines, you may come across situations where you need to perform certain basic calculations on your numeric data like Adding, Averaging or Counting and so on.

To perform these basic operations, Excel provides in-built functions that can be used to speed up your calculations.

Quick easy steps to get familiarise with basic functions are explained below-

Writing SUM Function

As name suggests this function is used to ADD up any list of numbers in excel sheets.

Pages