Teach on Udemy

Turn what you know into an opportunity and reach millions around the world.

Learn More

Your cart is empty.

Keep shopping

Format Data & Create Presentable Reports In MS Excel

Name: Format Data & Create Presentable Reports In MS Excel
Rating: 4.3 (10 reviews)

Use Text, Number, Currency format, customized style to display your data in presentable format on MS Excel

Created byPavas Malviya

Last updated 2/2024

English

English [Auto],

What you'll learn

Build powerful reports and let your number represent the true meaning for effective communication
Create meaningful reports and explain your data more efficiently
Explain the various formatting options available to bring life to your data
Differentiate your reports from others
Learn Conditional formatting using Data bar, find duplicate values

Course content

8 sections • 46 lectures • 2h 0m total length

Overview1:36
Learn to format data in MS Excel, apply number, currency, and phone formats, and create presentable reports through hands-on labs.
Excel is a program that is used to prepare a

Open Format cells dialog box1:03
Learn multiple ways to open the format cells dialog box in Excel, from the home tab drop-down and context menu to the Ctrl+1 shortcut, to adjust formatting options efficiently.
Modify numbers to Accounting format1:52
Add Currency as prefix to your number3:46
Apply currency formatting in Excel to existing data by selecting a column, choosing currency in the number format dialog, and setting two or three decimal places with a dollar symbol.
Difference between Currency and Accounting Format3:43
Format your numbers as Percentages4:38
Format numbers as percentages in Excel, using the home tab or the formatting dialog, and set two decimal places so numbers display as percentages automatically.
Date Format Introduction0:56
Format dates in Excel by recognizing dates are numbers behind the scenes, and tailor formats to audience needs, such as date and month views, and entering dates.
Lab Convert date to desired format5:23
Lab Convert Time to appropriate format2:24
Learn to convert time data in excel by using custom formats, selecting time options, displaying only hours and minutes, previewing changes, and applying formats across all data with format painter.
Recap of Basic Number Format0:37
Review the basic number formats covered, including accounting, currency, presentation, and date and time formats, with an introduction to custom formats. Apply these formats to present data clearly.
Practice

Introduction to Styles0:59
You will learn one of the most powerful feature of excel which is so heavily underutilised and lets you format your data quickly
Use Existing Styles for Quick formats2:32
Learn to apply existing cell styles to differentiate headers from data in an Excel dataset, using heading styles and the calculation style to color numbers.
Create your own Custom Cell Styles4:41
You can create your own custom style and name it the way you want to do. This will allow enable you to create your styles and re-use it multiple times.
Modify Styles and working examples7:51
In this lecture we will format excel file/report by adding more styles. The process of renaming the cell styles and modifying an existing style will be demonstrated.
Delete Styles and Working example1:04
Learn to delete the styles and review the outcomes on the format
Lecture Summary0:43
Summary of the Section
Styles are found in which tab

Create Table3:03
Convert spreadsheet data in MS Excel into a table using two quick methods, selecting the data range and confirming headers to create an organized, presentable report.
Quick way to format your tabular data2:53
Customize and build your own reusable format (color, style, font, etc.)2:25
Shortcut for operations on your table0:15
Overview
This document covers 7 keyboard short cut so you will be more efficient with the Table

Keyboard Short cut
1. Convert data to Table: Ctrl T

2. Select Column: Ctrl Space

3. Select Row: Shift Space

4. Select Entire Table: CTRL A

5. Insert Row: CTRL SHFT +

6. Deleting Row: CTRL -

7. Add and Remove Filter on the table: ALT D F F

Introduction to display insignificant zeros3:34
Explore how to use custom format numbers in MS Excel to display insignificant zeros, set leading zeros, and show three decimal places for price changes, with practical formatting examples.
Introduction to hide insignificant zeros4:58
Lab to hide insignificant zeros6:19
Lab to hide insignificant zeros2:15
Learn to use custom format with the hash sign to hide insignificant zeros in Excel, displaying 0.55 as .55 via a hash dot hash hash pattern in a hands-on file.
Introduction to the significance of Comma (,) in custom formatting2:28
Learn how commas in custom formatting improve readability in Excel by using them as thousand separators or as thousand-scale placeholders (K). See practical examples with large numbers.
Lab to demonstrate the use of Comma (,) in custom formatting2:33
Demonstrates creating a custom Excel format that uses a comma as the thousand separator and a k suffix to display numbers in thousands, using data in columns E and F.
Custom Format Quiz

Prefix text with numbers2:48
Display numbers as Thousands or Millions5:17
Introduction to Display Negative and Positive Numbers in various formats0:43
Master custom number formats in Excel to display negative and positive values in various formats for presentable reports, such as commodity price changes.
Four arguments of custom format to display Negative, Positive and Zeros2:58
Master Excel's custom format with semicolon-separated arguments to display positive, negative, and zero values, including color and symbols, and apply these formats to presentable financial reports.
Lab to demonstrate the display of Negative, Positive and Zeros4:18
Format numbers based on rules2:26
Learn to apply custom formatting in MS Excel to color numbers by rule, red for values greater than 5000, blue for 5000 or less, and hide zeros with #.

Overview of Conditional Formating1:23
Here are some of the benefits of applying conditional formatting.
•You can spotlight cells containing values that satisfy conditions or format an entire range of cells while adjusting the format according to each cell's value.
•Simplifies the process of emphasizing specific values
•Ensuring specific cells are readily distinguishable.
•It alters the look of a group of cells depending on a given condition or set of criteria.
Usecase:Highlight Duplicate values using condititional fomrmat2:04
When you’re dealing with a lot of data, there may be times when you want to make sure no duplicates exist—or, conversely, you may want to highlight any duplicates to call attention to them.
In that case, there’s a straightforward formula to use.

In this lecture, we will demonstrate formula use and highlight duplicate values.
Databar - Introduction3:03
Data bars are conditional formats that create visual effects in your data. These conditional formats make it easier to compare the values of a range of cells at the same time.
Databar Use case - Show Deviation of Sales for each country1:22
We explore Usecase to visualize the deviation of sales for each nation from average sales across all countries so you could make quick decisions on which countries are making sales below average.
Databar Use case - Hide Numbers and Show Only databar1:24
In this lecture, we will hide the sales number where data bar conditional formatting is applied so we can visualize our data with further clarity.
Databar Use case - Change colors of the databar2:14
In this lecture, we will edit the data ar and modify the colors of bars which depict positive and negative numbers of sales.
Color Scale - Introduction1:47
Color Scale Introduction
Color Scale Use Case - Sales Data Analysis2:17
Apply color scales to sales data using conditional formatting in Excel, creating a green, yellow, red gradient that highlights minimum, mid, and maximum values, with options to customize colors.
Color Scale Use Case - Stock Price Analysis1:14
Icon Set - Apply Sort and Filter1:41
Master icon sets in Excel to sort and filter by conditional formatting icons, color-coded red, green, and yellow, and hide numbers to show icons only.
Icon Set Use Case - Sales Data Analysis1:31
Apply conditional formatting icon sets in Excel to sales data with traffic lights, flagging exceptional sales, break even, and red flag ranges, while learning to remove and adjust the formula.
Icon Set - Modify Formula2:45
Modify icon set rules in Excel using conditional formatting to apply traffic light colors based on sales thresholds of 3500 and 2000, converting to number criteria and testing with data.

Requirements

You should have office 365 or any of the listed versions like 2016, 2013 & 2010, but most of the excel version has all the option which I'll explain
Be able to perform basic operations like open excel, input data on rows and columns and save data.
Urge to learn

Description

MS Excel has various types of formatting techniques to display your Numbers, Text, Currency and Date in appropriate format.

This course access includes lab exercises, 1-on-1 instructor support at any point of time during the course.

You may have spent few years working on Excel or your new assignment wants you to pick up Excel, this course will help you learn the fundamentals of data formatting. I have observed individuals and teams struggle with data formats in their reports and this course is designed to address that pain point.

To succeed at work or school we need to provide information in a meaningful format and this course intends to help you with that.

We will be learning and building fundamentals on creating Tables, Styles, and shortcuts to create awesome reports and the sooner you start implementing these, the faster you will be able to reap benefits at work and school

Additionally, you will be able to learn conditional formating in detail.

My suggestion to you is to ask a question during the course, so I can help you and provide any support you need.

Try this course and it will help you not only achieve quick results, but it will prepare you for a lifetime journey.

The following software can be used to learn

Windows:

Office 365, Microsoft Excel 2010, Microsoft Excel 2013, Microsoft Excel 2016, Microsoft Excel 2019, Microsoft Excel 2020

Macbook:

Microsoft Excel 2011, Microsoft Excel 2016, Microsoft Office 365

And all upper Microsoft Excel versions of software

Who this course is for:

My course is for employees who want to impress their boss and colleagues by presenting data in meaningful and presentable format.
Management or Business School graduates who wants to present data and submit assignments.
For small business owners who want to capture their data in meaningful and readable format so there partners, investors and customers so information is communicated appropriately.
School students, teachers and professors who are adopting to new ways of pedagogy

Format Data & Create Presentable Reports In MS Excel

What you'll learn

Explore related topics

Course content

Introduction1 lecture • 2min

Shortcuts to apply formats2 lectures • 5min

Basic Number format9 lectures • 24min

Styles and Themes6 lectures • 18min

Transform data to Table and create custom re-usable styles for table4 lectures • 9min

Custom Format fundamentals6 lectures • 22min

Application of Custom format and real time applications6 lectures • 19min

Conditional Formatting: Find Duplicate Values & Apply Data Bar format to reports12 lectures • 23min

Requirements

Description

Who this course is for: