Latest posts

object(WP_Query)#6239 (50) { ["query"]=> array(5) { ["posts_per_page"]=> int(7) ["orderby"]=> string(4) "date" ["order"]=> string(4) "DESC" ["paged"]=> int(1) ["tax_query"]=> array(1) { [0]=> array(4) { ["taxonomy"]=> string(15) "myexcelcategory" ["field"]=> string(4) "slug" ["operator"]=> string(6) "NOT IN" ["terms"]=> string(7) "podcast" } } } ["query_vars"]=> array(65) { ["posts_per_page"]=> int(7) ["orderby"]=> string(4) "date" ["order"]=> string(4) "DESC" ["paged"]=> int(1) ["tax_query"]=> array(1) { [0]=> array(4) { ["taxonomy"]=> string(15) "myexcelcategory" ["field"]=> string(4) "slug" ["operator"]=> string(6) "NOT IN" ["terms"]=> string(7) "podcast" } } ["error"]=> string(0) "" ["m"]=> string(0) "" ["p"]=> int(0) ["post_parent"]=> string(0) "" ["subpost"]=> string(0) "" ["subpost_id"]=> string(0) "" ["attachment"]=> string(0) "" ["attachment_id"]=> int(0) ["name"]=> string(0) "" ["pagename"]=> string(0) "" ["page_id"]=> int(0) ["second"]=> string(0) "" ["minute"]=> string(0) "" ["hour"]=> string(0) "" ["day"]=> int(0) ["monthnum"]=> int(0) ["year"]=> int(0) ["w"]=> int(0) ["category_name"]=> string(0) "" ["tag"]=> string(0) "" ["cat"]=> string(0) "" ["tag_id"]=> string(0) "" ["author"]=> string(0) "" ["author_name"]=> string(0) "" ["feed"]=> string(0) "" ["tb"]=> string(0) "" ["meta_key"]=> string(0) "" ["meta_value"]=> string(0) "" ["preview"]=> string(0) "" ["s"]=> string(0) "" ["sentence"]=> string(0) "" ["title"]=> string(0) "" ["fields"]=> string(0) "" ["menu_order"]=> string(0) "" ["embed"]=> string(0) "" ["category__in"]=> array(0) { } ["category__not_in"]=> array(0) { } ["category__and"]=> array(0) { } ["post__in"]=> array(0) { } ["post__not_in"]=> array(0) { } ["post_name__in"]=> array(0) { } ["tag__in"]=> array(0) { } ["tag__not_in"]=> array(0) { } ["tag__and"]=> array(0) { } ["tag_slug__in"]=> array(0) { } ["tag_slug__and"]=> array(0) { } ["post_parent__in"]=> array(0) { } ["post_parent__not_in"]=> array(0) { } ["author__in"]=> array(0) { } ["author__not_in"]=> array(0) { } ["ignore_sticky_posts"]=> bool(false) ["suppress_filters"]=> bool(false) ["cache_results"]=> bool(true) ["update_post_term_cache"]=> bool(true) ["lazy_load_term_meta"]=> bool(true) ["update_post_meta_cache"]=> bool(true) ["post_type"]=> string(0) "" ["nopaging"]=> bool(false) ["comments_per_page"]=> string(2) "50" ["no_found_rows"]=> bool(false) } ["tax_query"]=> object(WP_Tax_Query)#6161 (6) { ["queries"]=> array(1) { [0]=> array(5) { ["taxonomy"]=> string(15) "myexcelcategory" ["terms"]=> array(1) { [0]=> string(7) "podcast" } ["field"]=> string(4) "slug" ["operator"]=> string(6) "NOT IN" ["include_children"]=> bool(true) } } ["relation"]=> string(3) "AND" ["table_aliases":protected]=> array(0) { } ["queried_terms"]=> array(0) { } ["primary_table"]=> string(8) "wp_posts" ["primary_id_column"]=> string(2) "ID" } ["meta_query"]=> object(WP_Meta_Query)#6518 (9) { ["queries"]=> array(0) { } ["relation"]=> NULL ["meta_table"]=> NULL ["meta_id_column"]=> NULL ["primary_table"]=> NULL ["primary_id_column"]=> NULL ["table_aliases":protected]=> array(0) { } ["clauses":protected]=> array(0) { } ["has_or_relation":protected]=> bool(false) } ["date_query"]=> bool(false) ["request"]=> string(367) "SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts WHERE 1=1 AND ( wp_posts.ID NOT IN ( SELECT object_id FROM wp_term_relationships WHERE term_taxonomy_id IN (342) ) ) AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'acf-disabled') GROUP BY wp_posts.ID ORDER BY wp_posts.post_date DESC LIMIT 0, 7" ["posts"]=> array(7) { [0]=> object(WP_Post)#6273 (24) { ["ID"]=> int(16024) ["post_author"]=> string(1) "1" ["post_date"]=> string(19) "2020-05-15 04:25:27" ["post_date_gmt"]=> string(19) "2020-05-15 02:25:27" ["post_content"]=> string(27578) "Most organizations find it challenging to create reports or make decisions by analyzing huge amounts of data spread across various Excel spreadsheets. This is where Excel Graphs comes into action!

Learning how to make a Graph in Excel can make your report aesthetically pleasing and easy to analyze!

Graphs can be used to convert a plethora of rows and columns in Excel into simple charts that are easy to evaluate. In this step by step tutorial on How to Make a Graph in Excel, you will learn the basics of an Excel Chart, including: Make sure to download the exercise workbook to follow along and learn how to make a graph in Excel:

DOWNLOAD EXCEL WORKBOOK

 

How to Create a Graph in Excel

Excel Charts are visual representations of data that are used to make sense to the gazillion amounts of data jammed into rows and columns. It is essential to learn how to create a graph in Excel if we want to obtain more information from the data. Charts are extremely useful to:
  • Understand the meaning behind the numbers
  • Summarize large amounts of data
  • Draw comparisons between data sets
  • Spot data outliers that are unrelated to the rest of the data
  • Identify data trends & patterns
You can either work on existing data stored in Excel or you can import data from other applications. Just keep in mind that the data should have headers and no blank rows. In the example below, we have monthly sales data recorded in Excel and want to know which month achieved the highest and lowest sales: how to make a graph in excel Below is the step-by-step tutorial on How to Create a Graph in Excel: Step 1: Select the range of data (B3: C15) in Excel select data in excel Step 2: Go to Insert Tab  insert-tab-in-excel Step 3: Under the Charts group, you can choose from a variety of chart types available or select Recommended Charts to let Excel decide the appropriate chart for your data. recommended-charts-in-excel Step 4: The Insert Chart dialog box will open and from the left panel you can select the chart based on a chart preview. Based on our data, a clustered column chart will be the most appropriate. So, you can select that and click OK.  insert chart dialog box in excel This will create a Graph in Excel with each bar representing a month. From this visual representation of monthly sales data, you can easily spot that the highest sales are achieved in July and the lowest in September.   column-bar-in-excel You can even update the data used to make your Excel graph. Let's say you want to remove months, November and December, from the chart created using the step-by-step tutorial on how to make a graph in Excel above. You can use either of the two methods mentioned below to do so: Method 1:
  • Go to Chart Design Tab and then click on Select Data
  • In the Select Data Source dialog box, under Horizontal Axis Labels, scroll below and un-check November and December.
  • Click OK. You will have the edited chart displayed. how to make graph in excel
Method 2:
  • Select the chart. You can see the data used to make a graph are highlighted. edit data in excel chart
  • Hover the mouse towards the end of the data table and drag up the data handle to exclude the cells containing data for November and December. how to make graph in excel
  • You will have the edited chart displayed. how to make graph in excel
Now that you are familiar with how to make a graph in Excel, let's move on to the different types of Excel Charts available and their uses.

Types of Excel Chart

The different types of Excel Charts are elaborately explained here:
  1. Column Chart - Use this Chart if you have less than 12 data points & want to show trends E.g. Monthly or Annual data points: Column charts are used to visually compare values across a few categories by using vertical bars. In this graph below, each vertical bar represents a month and the vertical axis represents the sales month. This is an example of a Clustered Column chart. Excel offers seven different column chart types: clustered, stacked, 100% stacked, 3-D clustered, 3-D stacked, 3-D 100% stacked, and 3-D.
  2. Line Chart - Use this Chart if you have more than 12 data points & want to show trends E.g. Historic results or Statistical data: Line charts are used to display trends over time. In this graph below, it shows the sales trend over the months. There are 7 different line charts available on Excel - line, stacked line, 100% stacked line, line with markers, stacked line with markers, 100% stacked line with markers, and 3-D line. line chart in excel
  3. Pie Chart - Use this Chart for component comparisons only (Sums to 100%) E.g. Market share: Pie charts are used to display the contribution of each value (slice) to a total (pie). It can quantify items and show them as a percentage where the total of the numbers is 100%. In this graph below, each pie shows the percentage of sales of a particular month. Excel offers 5 different pie chart types: pie, pie of pie (this breaks out one piece of the pie into another pie to show the sub-category), bar of pie, 3-D pie, and doughnut.
  4. Bar Chart - Use this Chart if you have long names and want to compare E.g. Competitor analysis: It is similar to a Column chart but here the constant value is assigned to Y-axis and the variable is assigned to X-axis. In this graph below, each bar represents a sales amount for each month. bar chart in excel.
You can also take a look at this video by Microsoft on how to make a graph in Excel. There are other chart types available in Excel like Area Chart, Surface Chart, Radar Chart, Funnel Chart, etc. You can also create a graph called the Combo Chart that displays multiple sets of data in different ways on the same chart. You can even look at this blog to view our Excel Chart Infographic  and also learn about the new Excel Charts that were introduced in 2016. Now that you have understood How to Make a Graph in Excel and the different types of Excel Charts that are available, it is essential to customize the chart to enhance its appeal.

Customize an Excel chart

When you create a graph in Excel and then click on it, you will see two new tabs appearing on the Excel Ribbon - Chart Design & Format. These two tabs can be used to customize the chart as per your requirements. Chart Design Tab - Chart Desgin Tab in Excel Chart Design Tab in Excel contains design commands including Chart Layout, Chart Styles, Switch Row/Column, Select Data, Change Chart Type & Move Chart. Format Tab - Format Tab in Excel This ribbon contains format commands like Current Selection, Insert Shape, Shape Styles, WordArt Styles, Arrange Group & Size Group. You can also click on the chart and access the Chart Elements button on the upper-right corner of the chart. It contains three options - Chart Elements, Chart Style and Chart Filters Chart Elements button in Excel Chart Element Option (with the + sign icon) Using this option you can customize different chart elements like Chart Title, Axes, Axis Title, Data Labels, Data Table, Error Bars, Legend, Gridlines, and Trendline. To add or remove a chart element, you can simply use the Chart Element Options and check or un-check the boxes you want to show or hide. For example, if you have monthly sales data including sales amount and average sales and you can create a column chart using that data. How to make a graph in excel - column chart Now if you wish to add a Legend key to your chart, follow the steps below: Step 1: Click on the Chart. chart element options Step 2: Click on the + sign icon to access the Chart Elements Options. + sign icon excel chart Step 3: Check the box next to Legend.   How to make a column chart in excel This will add a legend keys next to the chart. To make further customizations like shifting the legend keys to the bottom of the chart: Point to Legend, select the arrow next to it and select bottom. How to make a column chart in excel

Chart Style

After you have learned How to Make a Graph in Excel and customize the elements, you should move on to making the charts more impactful.  You can change the style or color of your chart using this step by step tutorial below: Step 1: Click on the chart you want to change charts in excel Step 2: Click on the Brush icon in the upper right corner of the chart brush icon in excel Step 3: Click Style or Color and pick the option you want. excel chart style        excel chart color Step 4: You can scroll below the different options available and hover over them to get a preview of the styles and colors available and select the one that suits you the best. edited chart in excel This is how your edited chart will look like. Pretty cool right? :) Learning how to make a graph in Excel is not enough. You can match the style and color of the chart as per your company theme colors, font, and layout.

Chart Filters

Ever faced a situation where you want to display only certain data for an Excel Chart. Here is a tutorial on How to Create Graphs in Excel with Filters. Follow the steps below: Step 1: Click anywhere on the chart Step 2: Click on the Chart Filter button on the upper right corner of the chart filter button in excel Step 3: On the Values tab, you can hover the Categories and the corresponding data for that category will be highlighted. In the example below, when you hover over February with your mouse pointer, you can see the bar of February in the Chart being highlighted.  Cool! filtered data in excel Step 4: To filter the data, you can check or uncheck the series or categories you want to show or hide. In the example below, I have checked the first four months (Jan, Feb, Mar, Apr) and clicked on Apply.     This will let you filter the data in your Excel Charts and allow you to select the data you want to display or hide.

Save as Chart Template

Imagine you have around 20 charts that you want to create in Excel. You went through all the steps mentioned above to add Chart Elements, Customize a Layout and Style, and created an excellent Chart. Now, are you ready to repeat all these steps for the remaining 19 Charts? Well, you don't have to do that. You can Save the Chart as Template and then simply apply that template for the remaining charts. Let us follow the steps below and learn how to create a graph in Excel and save it as a template: Step 1: Right Click on your perfect Chart right click on excel chart Step 2: Click on Save as Template Step 3: In the Save Chart Template dialog box, give your template a "name" and click on Save. save chart template dialog box excel   Step 4: Now you move to the next chart where you want to paste this format. make a graph   Step 5: Right-click on that chart and select Change Chart Type make a graph Step 6: In the Change Chart Type dialog box, under the All Charts tab click on Templates and then select the template you have saved. Click OK. change chart type dialog box Your second chart will automatically change to the format of your saved template! Using the steps mentioned above, you can learn not only how to make a graph in Excel but also how to save the created graph as a template and reuse it whenever you want. create a graph

Conclusion

In this tutorial, you have learned how to make a graph in Excel, the different types of Excel charts, how to customize an Excel Chart, and save a chart as a Template. You can learn more about How to Make a Graph in Excel by viewing our Free Excel tutorials on Excel Charts! Make sure to download our FREE PDF on the 333 Excel keyboard Shortcuts here: You can follow our YouTube channel to learn more about How To Create a Graph in Excel!" ["post_title"]=> string(70) "How to Make a Graph in Excel - Make Your Data Talk With Visual Charts!" ["post_excerpt"]=> string(0) "" ["post_status"]=> string(7) "publish" ["comment_status"]=> string(4) "open" ["ping_status"]=> string(4) "open" ["post_password"]=> string(0) "" ["post_name"]=> string(28) "how-to-make-a-graph-in-excel" ["to_ping"]=> string(0) "" ["pinged"]=> string(0) "" ["post_modified"]=> string(19) "2020-05-20 04:58:13" ["post_modified_gmt"]=> string(19) "2020-05-20 02:58:13" ["post_content_filtered"]=> string(0) "" ["post_parent"]=> int(0) ["guid"]=> string(38) "https://www.myexcelonline.com/?p=16024" ["menu_order"]=> int(0) ["post_type"]=> string(4) "post" ["post_mime_type"]=> string(0) "" ["comment_count"]=> string(1) "0" ["filter"]=> string(3) "raw" } [1]=> object(WP_Post)#6272 (24) { ["ID"]=> int(15675) ["post_author"]=> string(1) "1" ["post_date"]=> string(19) "2020-05-05 00:21:06" ["post_date_gmt"]=> string(19) "2020-05-04 22:21:06" ["post_content"]=> string(27432) "How to Use Excel An Excel spreadsheet is a very powerful software which was developed by Microsoft in 1985 and is used by over 800 million users for number crunching, data analysis & reporting, charting and note taking - wherein its true power is often underutilized :) It is widely used by organizations for calculating, accounting, preparing charts, budgeting, project management, and various other tasks. The different uses of an Excel spreadsheet is in fact limitless!  In this tutorial we will hold your hand and teach you how to use Excel for the first time.

An Excel Spreadsheet is the go-to software to analyze, sort, or present a large amount of information and data in no time.

In this Excel tutorial, I will cover the Basics of Excel that you need to know to get started with how to use Excel. This Excel for dummies guide will include tutorials on: Let's start this step by step Excel tutorial on - "How to use Excel"

Opening an Excel Spreadsheet

To open an Excel Spreadsheet, follow the steps of this Excel tutorial below: Step 1: Click on the Window icon on the left side of the Taskbar and then scroll below to find “Excel”. open excel file Step 2: You can either click on the “Blank Workbook” button to open a blank Excel spreadsheet or select from the list of pre-existing templates provided by Excel. To open an existing Excel spreadsheet, click on the “Open Other Workbooks” and select the Excel sheet you want to work on. Step 3: An Excel spreadsheet is now opened and you are ready to explore the wonderful world of Excel. blank excel file

Understanding the Different Elements of an Excel Spreadsheet

To explore the different ways on How to use Excel you should be familiar with the different elements of Excel first. Excel Workbook and Excel Worksheet are often used interchangeably, but they do have different meanings. An Excel Workbook is an Excel file with the extension ".xlsx" or ".xls" whereas an Excel Worksheet is a single sheet inside the Workbook. Worksheets appear as tabs along the bottom of the screen. excel worksheets Now that you are clear about these two terms, let's move forward and understand the layout of an Excel Spreadsheet. It is a crucial step if you want to know how to use Excel efficiently.

Excel Ribbon

The Excel Ribbon is located at the top of the Excel Spreadsheet and just below the title bar or name of the worksheet. It comprises various tabs including Home, Insert, Page Layout, Formulas, Data, etc. Each tab contains a specific set of commands. excel ribbon By default, each Excel spreadsheet contains the following Tabs - File, Home, Insert, Page Layout, Formulas, Data, Review, and View.
  • File Tab can be used to open a new or existing file, save, print, or share a file, etc.
  • Home Tab can be used to copy, cut, or paste cells and work on the formatting of data.
  • Insert Tab can be used to insert the picture, charts, filter, hyperlink, etc.
  • Page Layout Tab can be used to prepare the Excel spreadsheet for printing and exporting data.
  • Formula Tab in Excel can be used to insert, define the name, create the name range, review the formula, etc.
  • Data Tab can be used to get external data, sort filter and group existing data, etc.
  • Review Tab can be used to insert comments, protect the document, check spelling, track changes, etc.
  • View Tab can be used to change the view of the Excel Sheets and make it easy to view the data.
You should be familiar with these tabs so you can understand how to use Excel efficiently. You can even customize these Tabs using the following steps: Step 1: Right-click on the ribbon and click on “Customize the Ribboncustomize ribbon Step 2: An Excel Options dialog box will open, click on the New Tab.

Step 3: Select that newly created tab and click on Rename and give it a name e.g. Custom Step 4: Now you can add the commands that you want under each group by simply clicking on the command from the Popular Command column and click on Add >> add command to excel tab This will create a New Tab called "Custom" with a popular command "Center". custom tab Under each Tab, there are various buttons grouped together. For Example - Under the Home Tab, all font-related buttons are clubbed together under the Group name “Font”. groups under excel tab You can access other features related to that group by clicking on the small arrow at the end of each Group. Once you click on that arrow, a dialog box will open and you can make further edits using that. arrow in excel group There is also a search bar available next to the tabs which was introduced in Excel 2019 and Office 365. You can type the feature that you are after and Excel will find it for you. You can also collapse the ribbon to provide extra space in the worksheet by pressing the keyboard shortcut Ctrl + F1 or by right-clicking anywhere on the ribbon and then clicking “Collapse the Ribbon”. collapse excel ribbon This will collapse the Ribbon! excel ribbon collapsed

Formula Bar

Excel's Formula bar is the area just below the Excel Ribbon. It contains two parts - on the left is the name box (it stores the cell address) and on the right is the contents of the currently selected cell. It is used to type values, text or an Excel formula or function. excel formula bar You can hide or unhide the formula bar by checking/unchecking “Formula Bar” under View Tab. excel formula bar in view tab You can also expand the formula bar if you have a large formula and its contents are not entirely visible. Click on the small arrow at the end of the formula bar and it will be expanded. collapse formula bar

Status Bar

At the bottom left side of the workbook, all the Excel worksheets are shown. You can access an Excel sheet by simply clicking on it. To add more Excel sheets, click on the “+” sign below which will add a new blank Excel sheet. access excel worksheets You can reorder the Excel sheets in your workbook by dragging them to a new location with your left mouse button. You can also rename each Excel sheet by Right Clicking on a Sheet Name > Click on Rename > Type the Name > Press Enter. rename excel worksheet At the bottom right of the Excel spreadsheet, you can quickly zoom the document by using the minus and plus symbols. To zoom to a specific percentage, in the ribbon menu go to the View tab > Click Zoom > Click on the specific percentage or type in your custom % > Click OK. zoom excel document There are different Excel workbook views available at the left of the zoom control: Normal View, Page Break View, and Page Layout View. You can select the view as per your choice. excel worksheet views

Cell & Excel Spreadsheet Basics

Any information including text, number, or an Excel formula can be inserted within a Cell. Alphabets are used to label Columns and numbers are used to label Rows. An intersection of a Row and Column is called a Cell. In the image below, cell C4 is the intersection of Row 4 and Column C. excel cell You can refer to a series of cells as a range by putting a colon between the first and last cells within the range. For example, the reference to the range starting from A1 to C10 will be A1: C10.  This is great when you are using an Excel formula. Now that you are familiar with the different elements in an Excel Spreadsheet, let's show you how to use Excel to enter data and do some calculations!

Entering Data in an Excel Spreadsheet

Follow this step -by-step tutorial on how to use excel to enter data below: Step 1: Click the cell you want to enter data into. For Example, you want to enter your sales data and want to start with the first cell, so click on A1 enter data in excel Step 2: Type what you want to add, say, Date. You will see that the same data will be visible on the Formula Bar as well. type data in excel Step 3: Press Enter. This will store the written data on the selected cell and move the selection to the next available cell, which is A2 in this example enter data in excel file To make any changes in the cell, simply click on it and make the changes. You can copy (Ctrl + C), Cut (Ctrl + X) any data from one Excel worksheet and paste it (Ctrl + V) to the same or another Excel worksheet.

Basic Calculations in an Excel Spreadsheet

Now that you have understood how to use Excel to enter data, let’s do some calculations on the data. Let's say you want to add two numbers: 4 and 5 in the excel spreadsheet. Follow the steps below on how to use Excel to add two numbers: Step 1: Start with the = or the + sign to tell Excel that you are ready to run some sort of calculation. addition in excel Step 2: Type number 4. addition in excel Step 3: Type + symbol to add addition in excel Step 4: Type number 5. addition in excel Step 5: Press Enter. addition in excel You will see the result 9 is displayed in the cell A1 and the formula is still displayed in the formula bar. Let’s try to use a cell reference to make calculations. In the example below, you have Column A that contains the number of products sold and Column B that contains price per product and you need to calculate the total amount in Column C. To calculate the total amount, follow the steps below: Step 1: Select cell C2 enter = in excel cell Step 2: Type = to start the formula basic calculation in excel Step 3: Select cell A2 with your mouse cursor or by using the left arrow key. basic calculation in excel Step 4: Type the multiplication sign * basic calculation in excel Step 5: Select cell B2

basic calculation in excel

Step 6: Press Enter enter formula in excel You can use various calculation operators, such as Arithmetic, Comparison, Text Concatenation and Reference operators that will be useful for you to have a  clear and complete idea on how to use Excel. Here is a walk-through for Excel for Dummies by Microsoft covering the most requested features of Excel.

Saving an Excel Spreadsheet

To save your work in Excel, click on the Save button on the Quick Access Toolbar or press Ctrl + S. save a excel file If you are trying to save a file for the first time, then follow these steps: Step 1: Press Ctrl + Shift +S or Click on the “Save As” button under the File tab. save as excel file Step 2: Click on “Browse” and choose the location where you want to save the file. browse excel file Step 3: In the File name box, enter a name for your new Excel workbook. enter excel file name Step 4: Click Save save excel file Here is an Excel for Dummies Video from Microsoft explaining how to save an Excel Workbook. This brings us to the end of this how to Use Excel tutorial. I have just covered the basics of how to use Excel in this article.  As an Excel newbie, Excel is a completely unexplored & exciting world for you right now and you are going to learn so much along your journey. My advice is to take baby steps, learn how to use one Excel feature, apply it to your data, make mistakes and keep on practicing. Within 7 days your Excel confidence will skyrocket! Make sure to download our FREE PDF on the 333 Excel keyboard Shortcuts here: You can learn more on how to use Excel by viewing our FREE Excel webinar training on Formulas, Pivot Tables and Macros & VBA! You can follow our YouTube channel to learn more about How To Use Excel for Dummies! [special_box] 👉 Click Here To Join Our Excel Academy Online Course & Access 1,000+ Excel Tutorials On Formulas, Macros, VBA, Pivot Tables, Dashboards, Power BI, Power Query, Power Pivot, Charts, Microsoft Office Suite + MORE!  [/special_box]" ["post_title"]=> string(28) "How To Use Excel For Dummies" ["post_excerpt"]=> string(0) "" ["post_status"]=> string(7) "publish" ["comment_status"]=> string(4) "open" ["ping_status"]=> string(4) "open" ["post_password"]=> string(0) "" ["post_name"]=> string(28) "how-to-use-excel-spreadsheet" ["to_ping"]=> string(0) "" ["pinged"]=> string(0) "" ["post_modified"]=> string(19) "2020-05-15 04:31:27" ["post_modified_gmt"]=> string(19) "2020-05-15 02:31:27" ["post_content_filtered"]=> string(0) "" ["post_parent"]=> int(0) ["guid"]=> string(38) "https://www.myexcelonline.com/?p=15675" ["menu_order"]=> int(0) ["post_type"]=> string(4) "post" ["post_mime_type"]=> string(0) "" ["comment_count"]=> string(1) "0" ["filter"]=> string(3) "raw" } [2]=> object(WP_Post)#6271 (24) { ["ID"]=> int(15422) ["post_author"]=> string(1) "1" ["post_date"]=> string(19) "2020-04-24 22:27:51" ["post_date_gmt"]=> string(19) "2020-04-24 20:27:51" ["post_content"]=> string(23073) " While working in an Excel Worksheet, you will come across scenarios when you will have to merge cells or combine cells in Excel. Knowing how to merge cells in Excel can be useful when you want to create titles for your reports or combine various cells into one. Excel has a number of approaches on how to merge cells in Excel that spans across rows and/or columns.

Let’s take a look at how to merge cells in Excel.

In this post, I will go into detail about several approaches on how to merge cells in Excel. Each one will produce a different result and layout:
  1. How to Use the Merge & Center button
  2. Other Merge & Center Options
  3. Using Center Across Selection
  4. Using CONCATENATE Formula
  5. Adding a Space & Line Breaks while Merging Cells
  6. Bonus Approach – Flash Fill
Make sure to download the exercise workbook to follow along:

DOWNLOAD EXCEL WORKBOOK

Watch on YouTube and give it a thumbs up 👍

How to Use the Merge & Center button

A great way to customize the layout of your Excel worksheet is to use the Merge & Center feature in Excel. It is a great way to create a label that spans several columns. This feature will retain the value in the upper-left cell but keep in mind that all data in the other merged cells will be deleted. In the example below, you can see that the text "SALES REPORT" is located in a single cell in A1. Let us fix that! Step 1: Select the cells A1:F1 that you want to merge. Step 2: Go to Home > 'Alignment' group > Merge & Center button merge & center button excel Step 3: The currently selected cells will be merged, and their contents will be center aligned. Notice that the reference for the 6 merged cells cell points at A1. By following this step-by-step guide on How to Merge Cells in Excel you can create headers/titles for you report that will make it much easier to understand.

Other Merge & Center Options

When you click on the drop-down arrow beside the Merge & Center button in the Alignment group, you will see it contains a drop-down list with additional options and each one produces a different result:
  • Merge Across − When a multi-row range is selected, this command creates multiple merge cells in Excel — one for each row.
  • Merge Cells − Merges the selected cells without applying the Center attribute.
  • Unmerge Cells −This unmerges the merged cells in Excel and we explain in detail below.
  Once you have learned how to merge cells in Excel, you should also know how to unmerge them. In order to Unmerge Cells in Excel Step 1: Select the cells that you want to unmerge. Step 2: Click the Merge & Center button or select the Unmerge Cells option from the drop-down menu. Your data is now unmerged. NB: Even though this is a fairly simple process to know how to merge cells in Excel, it is not highly recommended, and further below we explain the alternatives to merge cells in Excel. If any data was lost when the original cells were merged, they will not be restored (unless your select CTRL + Z to undo the last action). Here are a couple of shortfalls once cells have been merged:
  • Excel Functions won’t work on merged cells
  • Excel 'Sort' command will not work on ranges that contain the Excel merge cells.
  • Single column can’t be selected if it contains any merged cells
  • Excel Filters cannot be applied
  • Dates cannot easily be copy-pasted elsewhere
Here is a video from Microsoft showing merging and unmerging cells in action.

Using Center Across Selection

To achieve the same result as Merge & Center without having the above restrictions, use the Center Across Selection feature. This would merge the cells across columns and still let you select each cell individually. Below we explain how to apply the Center Across Selection: Step 1: Select the cells A1:F1 that you want to merge. Step 2: Press Ctrl + 1 to bring up the Format Cells dialog box. format cells Step 3: Under the Alignment Tab, in the Horizontal drop-down box, select “Center Across Selectionformat cells - how to combine cells in excel Step 4: Click OK. excel merged cells - center across selection The selected cells are now merged. You can see that even though the data is merged across cell A1: F1, you can still select and refer to each cell individually!

Using the CONCATENATE Function

If you use the above options to merge cells in Excel, it will keep the text at the leftmost cell (A1 in this case) and remove the text from all other cells. If you don’t want to lose the text from the merged cells in Excel, use the CONCATENATE Function (pre Excel 2019), the CONCAT Function (Excel 2019 & Office 365) or the ampersand "&" operator. The CONCATENATE function combines cells in Excel quickly. There may be times when you are dumped with data in Excel and it is not formatted quite the way you want. Say, you have the First Name and Last Name in different columns and you want to merge them in a single column containing First & Last Name. Now, if you use Merge & Center or Merge Across Selection, it will delete the text in the second column and only display the First Name as a merged cell. But this is not what you were looking for. Right? Let me introduce you to the Concatenate Function and the ampersand "&" operator that will merge cells in Excel! This feature should definitely be bookmarked as it will come in handy when cleaning, transforming and analyzing data in Excel. The Concatenate Function will merge multiple cells into a single cell and keep both values. You can merge or join more than two cells together whether it contains text, numbers, or both. In the SALES REPORT below, you have the First Name in Column A and Last Name in Column B. For reporting and further analysis, you need them to be combined into one column, so it's best to use the Concatenate function or the "&" Operator for this. It is perfect for when you want to merge cells or combine 'FIRST NAMES' with 'LAST NAMES' that are in separate columns and merging them into one cell. how to merge cells in excel Before I  get into how to merge cells in Excel, let’s talk about exactly what happens when you try to use Merge & Center here. If you select cells A3 and B3 and then press the “Merge & Center” button, you will see that you end up with an Excel merged cell with only the upper-left cell’s value i.e. the First Name. To merge cells in Excel without losing any data, you should use the Concatenate Function in Excel. Follow the steps to know how to combine cells in Excel: Step 1: Select Column C and press Ctrl + to add a new column. Name this column as “Full Name”. how to merge first and last name in excel Step 2: Select Cell C3 and type the formula: =CONCATENATE(A3, B3). Press Enter use concatenate to merge cells in excel Step 3: Copy cell C3 and paste it in remaining cells or drag the fill handle to copy the formula to the other cells below. concatenate function to combine cells in excel This will merge cells in Excel without losing the data!  Below we show you how to make this merged cell look prettier by adding a space between the First & Last Names as well as a line break.

Adding a Space & Line Breaks while Merging Cells

In the FULL NAME column above, you can see that there is no space or any character between the FIRST NAME and the LAST NAME. This mashed-together value of the FULL NAME isn’t a typographical mistake. It is important to understand how to combine cells in Excel including spaces. To concatenate this information and include spaces, in the 2nd CONCATENATE function argument, you need to type in double quotation marks and put a space between these quotations i.e. " " The edited formula will be =CONCATENATE(A3," ", B3). concatenate function to merge cells in excel including spaces You can replace the space with a dot, comma, or any other character. Simply, replace the space in this function with a character and make sure to enclose the character or text in double quotation marks. With the Concatenate function you also have the option to amend the combination later, whereas you don’t have any such option if you use Merge & Center. You can also use the ampersand sign, &, to combine cells in Excel. The & operator works just like the CONCATENATE function where you can combine text, numbers, individual cells, etc. Both CONCATENATE and & produce the same results. Let’s see how to merge cells in Excel using the & operator. The following examples show the same SALES REPORT but this time I will use & operator to merge cells in Excel. The formula to be used is = A3&" "&B3 & operator to merge cells in excel You can see that the result is the same and you can use either the ampersand sign & or the CONCATENATE function, it just depends on what you are more comfortable with. You can also add line breaks while merging cells by using the CHAR(10) function in Excel. In the following example, the CHAR(10) function is used to add a line break between FULL NAME & SALES. You can use the formula: =CONCATENATE(B3, CHAR(10), C3). Also, make sure you select the “Wrap Text” option under the Home Tab, otherwise the result will be displayed in the same line only. add line break while merging cells in excel You can see that the FULL NAME is displayed on the first line and the second line contains SALES, which is another cool way you can create Excel merged cells.

Bonus Approach – Flash Fill

Lastly, there is a BONUS approach to merge cells in Excel with – Flash Fill. It is probably the simplest way to combine cells in Excel and is available in Excel 2013 or later. Flash Fill is a special tool that analyses the pattern from the existing cells and then automatically extracts the data to the pattern that you set. We can combine the FIRST NAME and LAST NAME from the previous example using Flash Fill. Step 1: You should establish a pattern by typing the FULL NAME in cell C2. use flash fill to merge cells in excel Step 2: Highlight the remaining cell and press the keyboard shortcut Ctrl + E or go to the ribbon menu and select: Data > Flash Fill flash fill button in excel Step 3: Excel will sense the pattern you provided in C2, and will fill in the empty cells below, merging the FIRST NAME with the LAST NAME. excel merge cells using flash fill If the Flash Fill does not seem to be working for your data, make sure it is turned on. To turn Flash Fill on, go to Tools > Options > Advanced > Editing Options > check the "Automatically Flash Fill" box. Excel's Flash Fill feature is a versatile tool that can be used to merge, split, or clean data. You need to type the first entry for Excel to sense the patterns and then Excel fills in the rest of the data based on the pattern you provided.

In Conclusion

So, the Merge & Center or Center Across Selection features in Excel makes data more visually presentable and highly organized. But it only keeps the contents of the top-left cell and deletes the rest. This loss of data is a big disadvantage when you merge cells in Excel. To preserve your data in Excel, use the & operator, the CONCATENATE function, or Flash Fill to merges cells' contents into one cell. Make sure to download our FREE PDF on the 333 Excel keyboard Shortcuts here: You can learn more on how to use Excel by viewing our FREE Excel webinar training on Formulas, Pivot Tables and Macros & VBA! You can follow our YouTube channel to learn more about How To Merge Cells in Excel! [special_box] 👉 Click Here To Join Our Excel Academy Online Course & Access 1,000+ Excel Tutorials On Formulas, Macros, VBA, Pivot Tables, Dashboards, Power BI, Power Query, Power Pivot, Charts, Microsoft Office Suite + MORE!  [/special_box]
" ["post_title"]=> string(37) "6 Simple Ways to Merge Cells in Excel" ["post_excerpt"]=> string(0) "" ["post_status"]=> string(7) "publish" ["comment_status"]=> string(4) "open" ["ping_status"]=> string(4) "open" ["post_password"]=> string(0) "" ["post_name"]=> string(44) "how-to-merge-cells-in-excel-definitive-guide" ["to_ping"]=> string(0) "" ["pinged"]=> string(0) "" ["post_modified"]=> string(19) "2020-05-18 04:21:36" ["post_modified_gmt"]=> string(19) "2020-05-18 02:21:36" ["post_content_filtered"]=> string(0) "" ["post_parent"]=> int(0) ["guid"]=> string(38) "https://www.myexcelonline.com/?p=15422" ["menu_order"]=> int(0) ["post_type"]=> string(4) "post" ["post_mime_type"]=> string(0) "" ["comment_count"]=> string(1) "0" ["filter"]=> string(3) "raw" } [3]=> object(WP_Post)#6036 (24) { ["ID"]=> int(14511) ["post_author"]=> string(1) "2" ["post_date"]=> string(19) "2020-04-01 11:41:09" ["post_date_gmt"]=> string(19) "2020-04-01 09:41:09" ["post_content"]=> string(12342) "How to Use Microsoft Teams What is Microsoft Teams? This is an awesome software from Microsoft where it focus on these 3 Cs:
  • Colleagues
  • Content
  • Conversations
It is all about collaborating with your team members & workplace with Microsoft Teams. It is your hub for teamwork in Office 365. Working from home has never been easier and we will show you a quick overview on how to use Microsoft Teams in this tutorial. How to Use Microsoft Teams Here is the full list of what we will cover:

Learn What Is Microsoft Teams In Just 1 Hour!

*** Watch our video on how to use Microsoft Teams below and downloadable our Free Microsoft Teams Cheat Sheet to practice ***
Watch on YouTube and give it a thumbs up 👍
Below we have created a FREE downloadable PDF cheat sheet and a full list of the what you can do with Microsoft Teams! Download it, print it and put it beside you to remind you of the amazing things you can do with Microsoft Teams. Share it with your friends and colleagues as well and they will thank you for it!

Download Our PDF Cheat Sheet On How To Use Microsoft Teams below...for FREE!

How to Use Microsoft Teams

DOWNLOAD THE MICROSOFT TEAMS PDF CHEAT SHEET

Teams and Channels

First of all you need to sign up and create an account for free for Microsoft Teams using this link here. Then to download Microsoft Teams, get it here. To understand what is Microsoft Teams, we need to talk about the foundation blocks of Teams and Channels. If you work with a project team and continually communicate with them, then forming a Team will be a perfect fit for this. Inside Teams, you can create Channels to segregate topics that you want to cover. Here are the things you can do inside Teams: 1. Select an existing Team 2. Select a Channel inside a Team 3. Manage settings, add channels and make changes to the Team 4. Add more tabs and apps to the Channel 5. Start a conversation inside the Channel 6. Join or create a team How to Use Microsoft Teams

Chat

Chat is an area wherein you can have direct messages with specific people or a smaller group of people. There are a lot of things you can do inside Chat: 1. Start a new conversation 2. Write a message to the person(s) you are conversing with 3. Start a video call, audio call, or screen sharing session 4. Add more tabs and apps that you and that person use frequently 5. Filter messages inside the chat How to Use Microsoft Teams

Files

We primarily use OneDrive inside files. And when you share files within Channels or Chats, you can conveniently share the files inside your OneDrive as well. These are what you can do: 1. Open your OneDrive storage 2. Add, Edit, Delete files inside your OneDrive storage How to Use Microsoft Teams

Calls

Calling is very much integrated inside Teams. It is similar to how you use your phone: contact list, voicemails, the works. Here’s a quick list of what you do: 1. Add contacts to your contact list 2. Start a chat, email, audio call, or video call your selected contact 3. You can access the following here:
  • Speed dial – quick dial the selected contacts you have placed here
  • Contacts – like a phone book, modify your list of contacts here
  • History – a quick history of everything that happened with calls
  • Voicemail – check your voicemails here
How to Use Microsoft Teams

Activity

The Activity section shows everything that happened that would be of interest to you. It’s a mix of mentions, reactions, calls, voicemails, and other events happening inside Microsoft Teams that are related to you. Here’s a quick list of Activity functionality: 1. Filter out events inside your Feed 2. Switch to My Activity – to see what you have done inside Teams 3. Click on specific events in your feed to see details How to Use Microsoft Teams

Profile and Search

You can update your profile and your status inside Teams. If you work in multiple organizations, you can use the same account to switch to a different Microsoft Teams environment. The search bar is very flexible and allows you to search for messages, people, and files. You can also perform commands inside it. Let us have a look on how to make the most of this: 1. Search for messages, people or files 2. Click on this to open Profile and Settings 3. Switch organizations if you have more than one 4. Set your status 5. Open Settings to configure Microsoft Teams How to Use Microsoft Teams

Settings

This is where you can modify the inner workings of Microsoft Teams. Let us go over a quick overview of each settings section: 1. General – Change themes and main application settings (e.g. startup) 2. Privacy – Manage priority contacts for DND (do not disturb) exemptions, read receipts 3. Notifications – Manage notifications to Teams events 4. Devices – Manage audio devices and the camera 5. Permissions – allow access to components in your device (e.g. location, camera, microphone, speakers) 6. Calls – modify your ringtone and manage voicemail settings How to Use Microsoft Teams

Mobile Apps and Other Microsoft Teams Resources

Our overview does not end here, there are a lot more tidbits when it comes to using Microsoft Teams! Here are some quick links that you could use for jumpstarting your Microsoft Teams app experience: The best part is both of these are free to download and use. I use the Microsoft Teams app a lot with my team so that I can collaborate with them while I'm on the go! Another resource that I want to share to you is the Demo Teams Office website. This is a wonderful site to give you a sneak preview of how Microsoft Teams would work. It is not a fully functional teams website but it is enough to give you a good idea on how working with Teams would feel like. Check it out!
For more training materials, you can have a look at the Microsoft Support website. There is so much more to explore about Microsoft Teams: OneDrive, Outlook, Video Conferencing and so much more, you name it! One of the things I love about MS Teams is it allows me and my team to work inside a single application as it does a very good job at integrating everything that you need inside.
If Microsoft Teams does not work out for your needs though, you can easily uninstall Microsoft Teams in a few steps. Here is the official Uninstall Microsoft Teams tutorial.

Download Our Microsoft Teams PDF Cheat Sheet below...for FREE!

How to Use Microsoft Teams

DOWNLOAD MICROSOFT TEAMS PDF CHEAT SHEET

" ["post_title"]=> string(26) "How to Use Microsoft Teams" ["post_excerpt"]=> string(0) "" ["post_status"]=> string(7) "publish" ["comment_status"]=> string(4) "open" ["ping_status"]=> string(4) "open" ["post_password"]=> string(0) "" ["post_name"]=> string(26) "how-to-use-microsoft-teams" ["to_ping"]=> string(0) "" ["pinged"]=> string(0) "" ["post_modified"]=> string(19) "2020-05-07 17:02:29" ["post_modified_gmt"]=> string(19) "2020-05-07 15:02:29" ["post_content_filtered"]=> string(0) "" ["post_parent"]=> int(0) ["guid"]=> string(38) "https://www.myexcelonline.com/?p=14511" ["menu_order"]=> int(0) ["post_type"]=> string(4) "post" ["post_mime_type"]=> string(0) "" ["comment_count"]=> string(1) "0" ["filter"]=> string(3) "raw" } [4]=> object(WP_Post)#6037 (24) { ["ID"]=> int(11342) ["post_author"]=> string(1) "2" ["post_date"]=> string(19) "2020-02-28 10:48:03" ["post_date_gmt"]=> string(19) "2020-02-28 09:48:03" ["post_content"]=> string(4994) "Want to have fun with your text and have it displayed backwards? Excel Macros will reverse text for you in a single click! Make sure your Excel has the Developer Tab enabled following this tutorial. I explain how you can do this below step by step! What does it do? Converts your selected text and puts it in reverse Copy Source Code: [code language="vb"] Public Function ReverseText(ByVal cell As Range) As String 'Reverse text using this function ReverseText = VBA.strReverse(cell.Value) End Function 'Make sure you have a selection ready Sub ReverseTextInSelection() Dim range As Range Selection.Value= Selection.Value 'Loop through all the cells For Each range In Selection 'Call your function range= ReverseText(range) Next range End Sub [/code] Final Result:  Exercise Workbook:

DOWNLOAD EXCEL WORKBOOK


This is our list of text. We've added a couple of palindromes in here for good measure! A palindrome is when you reverse the text it reads completely the same! STEP 1: Go to Developer > Code > Visual Basic   STEP 2: Paste in your code and Select Save. Close the window afterwards. We have also added a function in #1 to show you on how we define custom functionality. Then in #2 we refer to this function and use this functionality that we have created.   STEP 3: Let us test it out! Open the sheet containing the data. Go to Developer > Code > Macros   Make sure your text and macro are selected. Click Run.   With just one click, all of your text are now reversed! Notice the ones in red read exactly the same:
  • Was it a Cat I saw?
  • No Lemon, No Melon
How to Reverse Text Using Macros in Excel   HELPFUL RESOURCE: 101 Macros Book" ["post_title"]=> string(43) "Reverse Text Function Using Macros In Excel" ["post_excerpt"]=> string(0) "" ["post_status"]=> string(7) "publish" ["comment_status"]=> string(4) "open" ["ping_status"]=> string(4) "open" ["post_password"]=> string(0) "" ["post_name"]=> string(43) "reverse-text-function-using-macros-in-excel" ["to_ping"]=> string(0) "" ["pinged"]=> string(0) "" ["post_modified"]=> string(19) "2020-05-17 13:10:56" ["post_modified_gmt"]=> string(19) "2020-05-17 11:10:56" ["post_content_filtered"]=> string(0) "" ["post_parent"]=> int(0) ["guid"]=> string(38) "https://www.myexcelonline.com/?p=11342" ["menu_order"]=> int(0) ["post_type"]=> string(4) "post" ["post_mime_type"]=> string(0) "" ["comment_count"]=> string(1) "0" ["filter"]=> string(3) "raw" } [5]=> object(WP_Post)#6238 (24) { ["ID"]=> int(12869) ["post_author"]=> string(1) "2" ["post_date"]=> string(19) "2020-02-25 05:39:48" ["post_date_gmt"]=> string(19) "2020-02-25 04:39:48" ["post_content"]=> string(9733) " Microsoft Excel is a powerful tool which has many Excel keyboard shortcuts to make you faster and more efficient. Instead of memorizing all of these useful Excel keyboard shortcuts, we have compiled a list for you so you can apply to your Excel worksheet straight away. Below we have created a FREE downloadable PDF cheat sheet and a full searchable list of the 333 Excel Shortcuts for both in Windows and Mac for you! Download it, print it and post it to your wall so that you can get a quick reminder of the best Excel keyboard shortcuts out there. Share it with your friends and colleagues as well and they will thank you for it!
  [table_of_contents] [one_half] [icon name="lightbulb-o" class="" unprefixed_class=""] Frequent Keyboard Shortcuts [icon name="wrench" class="" unprefixed_class=""] Inside the Ribbon Shortcuts [icon name="recycle" class="" unprefixed_class=""] Cell Navigation Shortcuts [icon name="edit" class="" unprefixed_class=""] Formatting Cell Shortcuts [icon name="retweet" class="" unprefixed_class=""] Making Selection Shortcuts [icon name="flask" class="" unprefixed_class=""] Working with Formula Shortcuts [icon name="bookmark" class="" unprefixed_class=""] Ribbon Tab Shortcuts [/one_half][one_half_last] [icon name="list" class="" unprefixed_class=""] Power Pivot Shortcuts [icon name="check-circle" class="" unprefixed_class=""] Function Key Shortcuts [icon name="wrench" class="" unprefixed_class=""] Drag and Drop Shortcuts [icon name="flask" class="" unprefixed_class=""] Macros and VBA Shortcuts [icon name="list" class="" unprefixed_class=""] Pivot Table Shortcuts [icon name="lightbulb-o" class="" unprefixed_class=""] Power BI Shortcuts [icon name="bolt" class="" unprefixed_class=""] Other Shortcuts [/one_half_last] [/table_of_contents]

Download Our Excel Shortcuts PDF Cheat Sheet for Windows and Mac below...for FREE!

333 Excel Shortcuts for Windows and Mac 333 Excel Shortcuts for Windows and Mac

DOWNLOAD THE 333 EXCEL SHORTCUTS PDF CHEAT SHEET

We also have a free video tutorial on the 333 Excel Keyboard Shortcuts which you can view here:
Watch on YouTube and give it a thumbs up 👍
333 Excel Keyboard Shortcuts

Full List of the 333 Excel Keyboard Shortcuts for Windows and Mac!

Here is the list of the Microsoft Excel Keyboard Shortcuts for Windows and Mac. Windows keyboard shortcuts are on the left side, while the Mac keyboard shortcuts are on the right. [include id="12" title="Shorctcuts search"]

Frequent Shortcuts[icon name="arrow-up" class="" unprefixed_class=""]

[table id=4 /]

Inside the Ribbon[icon name="arrow-up" class="" unprefixed_class=""]

[table id=5 /]

Cell Navigation[icon name="arrow-up" class="" unprefixed_class=""]

[table id=6 /]

Formatting Cells[icon name="arrow-up" class="" unprefixed_class=""]

[table id=13 /]

Making Selections[icon name="arrow-up" class="" unprefixed_class=""]

[table id=7 /]

Working with Formulas[icon name="arrow-up" class="" unprefixed_class=""]

[table id=8 /]

Ribbon Tabs[icon name="arrow-up" class="" unprefixed_class=""]

[table id=9 /]

Power Pivot[icon name="arrow-up" class="" unprefixed_class=""]

[table id=10 /]

Function Keys[icon name="arrow-up" class="" unprefixed_class=""]

[table id=11 /]

Drag and Drop[icon name="arrow-up" class="" unprefixed_class=""]

[table id=14 /]

Macros and VBA[icon name="arrow-up" class="" unprefixed_class=""]

[table id=15 /]

Pivot Table[icon name="arrow-up" class="" unprefixed_class=""]

[table id=16 /]

Power BI[icon name="arrow-up" class="" unprefixed_class=""]

[table id=17 /]

Others[icon name="arrow-up" class="" unprefixed_class=""]

[table id=12 /]  

Download Our Excel Shortcuts PDF Cheat Sheet for Windows and Mac below...for FREE!

333 Excel Shortcuts for Windows and Mac 333 Excel Shortcuts for Windows and Mac

DOWNLOAD THE 333 EXCEL SHORTCUTS PDF CHEAT SHEET

" ["post_title"]=> string(39) "333 Excel Shortcuts for Windows and Mac" ["post_excerpt"]=> string(0) "" ["post_status"]=> string(7) "publish" ["comment_status"]=> string(4) "open" ["ping_status"]=> string(4) "open" ["post_password"]=> string(0) "" ["post_name"]=> string(39) "333-excel-shortcuts-for-windows-and-mac" ["to_ping"]=> string(0) "" ["pinged"]=> string(0) "" ["post_modified"]=> string(19) "2020-05-19 09:51:54" ["post_modified_gmt"]=> string(19) "2020-05-19 07:51:54" ["post_content_filtered"]=> string(0) "" ["post_parent"]=> int(0) ["guid"]=> string(38) "https://www.myexcelonline.com/?p=12869" ["menu_order"]=> int(0) ["post_type"]=> string(4) "post" ["post_mime_type"]=> string(0) "" ["comment_count"]=> string(1) "0" ["filter"]=> string(3) "raw" } [6]=> object(WP_Post)#7074 (24) { ["ID"]=> int(11341) ["post_author"]=> string(1) "2" ["post_date"]=> string(19) "2020-02-24 10:44:28" ["post_date_gmt"]=> string(19) "2020-02-24 09:44:28" ["post_content"]=> string(4872) "Have numbers that are degrees but you are having difficulty inserting the degree symbol to each? Excel Macros can modify your numbers and you will add a degree symbol to your selection in a single click! You will learn here on how you can customize text and modify it using Macros. Make sure your Excel has the Developer Tab enabled following this tutorial. I explain how you can do this below step by step! What does it do? Adds a degree symbol to each number in your selection Copy Source Code: [code language="vb"] Sub AddDegreeSymbolToSelection() Dim rng As Range 'Loop through the entire selection For Each rng In Selection 'Set the active cell rng.Select 'For each number, add the degree symbol at the end If ActiveCell <> "" Then If IsNumeric(ActiveCell.Value) Then ActiveCell.Value = ActiveCell.Value & "°" End If End If Next End Sub [/code] Final Result:  Exercise Workbook:

DOWNLOAD EXCEL WORKBOOK


This is our list of numbers: STEP 1: Go to Developer > Code > Visual Basic   STEP 2: Paste in your code and Select Save. Close the window afterwards.   STEP 3: Let us test it out! Open the sheet containing the data. Go to Developer > Code > Macros   Make sure your numbers and macro are selected. Click Run.   With just one click, all your numbers have the degree symbol added to it!   How to Add a Degree Symbol to Selection Using Macros In Excel   HELPFUL RESOURCE: 101 Macros Book" ["post_title"]=> string(54) "Add a Degree Symbol to Selection Using Macros In Excel" ["post_excerpt"]=> string(0) "" ["post_status"]=> string(7) "publish" ["comment_status"]=> string(4) "open" ["ping_status"]=> string(4) "open" ["post_password"]=> string(0) "" ["post_name"]=> string(54) "add-a-degree-symbol-to-selection-using-macros-in-excel" ["to_ping"]=> string(0) "" ["pinged"]=> string(0) "" ["post_modified"]=> string(19) "2020-05-23 23:54:18" ["post_modified_gmt"]=> string(19) "2020-05-23 21:54:18" ["post_content_filtered"]=> string(0) "" ["post_parent"]=> int(0) ["guid"]=> string(38) "https://www.myexcelonline.com/?p=11341" ["menu_order"]=> int(0) ["post_type"]=> string(4) "post" ["post_mime_type"]=> string(0) "" ["comment_count"]=> string(1) "0" ["filter"]=> string(3) "raw" } } ["post_count"]=> int(7) ["current_post"]=> int(-1) ["in_the_loop"]=> bool(false) ["post"]=> object(WP_Post)#6273 (24) { ["ID"]=> int(16024) ["post_author"]=> string(1) "1" ["post_date"]=> string(19) "2020-05-15 04:25:27" ["post_date_gmt"]=> string(19) "2020-05-15 02:25:27" ["post_content"]=> string(27578) "Most organizations find it challenging to create reports or make decisions by analyzing huge amounts of data spread across various Excel spreadsheets. This is where Excel Graphs comes into action!

Learning how to make a Graph in Excel can make your report aesthetically pleasing and easy to analyze!

Graphs can be used to convert a plethora of rows and columns in Excel into simple charts that are easy to evaluate. In this step by step tutorial on How to Make a Graph in Excel, you will learn the basics of an Excel Chart, including: Make sure to download the exercise workbook to follow along and learn how to make a graph in Excel:

DOWNLOAD EXCEL WORKBOOK

 

How to Create a Graph in Excel

Excel Charts are visual representations of data that are used to make sense to the gazillion amounts of data jammed into rows and columns. It is essential to learn how to create a graph in Excel if we want to obtain more information from the data. Charts are extremely useful to:
  • Understand the meaning behind the numbers
  • Summarize large amounts of data
  • Draw comparisons between data sets
  • Spot data outliers that are unrelated to the rest of the data
  • Identify data trends & patterns
You can either work on existing data stored in Excel or you can import data from other applications. Just keep in mind that the data should have headers and no blank rows. In the example below, we have monthly sales data recorded in Excel and want to know which month achieved the highest and lowest sales: how to make a graph in excel Below is the step-by-step tutorial on How to Create a Graph in Excel: Step 1: Select the range of data (B3: C15) in Excel select data in excel Step 2: Go to Insert Tab  insert-tab-in-excel Step 3: Under the Charts group, you can choose from a variety of chart types available or select Recommended Charts to let Excel decide the appropriate chart for your data. recommended-charts-in-excel Step 4: The Insert Chart dialog box will open and from the left panel you can select the chart based on a chart preview. Based on our data, a clustered column chart will be the most appropriate. So, you can select that and click OK.  insert chart dialog box in excel This will create a Graph in Excel with each bar representing a month. From this visual representation of monthly sales data, you can easily spot that the highest sales are achieved in July and the lowest in September.   column-bar-in-excel You can even update the data used to make your Excel graph. Let's say you want to remove months, November and December, from the chart created using the step-by-step tutorial on how to make a graph in Excel above. You can use either of the two methods mentioned below to do so: Method 1:
  • Go to Chart Design Tab and then click on Select Data
  • In the Select Data Source dialog box, under Horizontal Axis Labels, scroll below and un-check November and December.
  • Click OK. You will have the edited chart displayed. how to make graph in excel
Method 2:
  • Select the chart. You can see the data used to make a graph are highlighted. edit data in excel chart
  • Hover the mouse towards the end of the data table and drag up the data handle to exclude the cells containing data for November and December. how to make graph in excel
  • You will have the edited chart displayed. how to make graph in excel
Now that you are familiar with how to make a graph in Excel, let's move on to the different types of Excel Charts available and their uses.

Types of Excel Chart

The different types of Excel Charts are elaborately explained here:
  1. Column Chart - Use this Chart if you have less than 12 data points & want to show trends E.g. Monthly or Annual data points: Column charts are used to visually compare values across a few categories by using vertical bars. In this graph below, each vertical bar represents a month and the vertical axis represents the sales month. This is an example of a Clustered Column chart. Excel offers seven different column chart types: clustered, stacked, 100% stacked, 3-D clustered, 3-D stacked, 3-D 100% stacked, and 3-D.
  2. Line Chart - Use this Chart if you have more than 12 data points & want to show trends E.g. Historic results or Statistical data: Line charts are used to display trends over time. In this graph below, it shows the sales trend over the months. There are 7 different line charts available on Excel - line, stacked line, 100% stacked line, line with markers, stacked line with markers, 100% stacked line with markers, and 3-D line. line chart in excel
  3. Pie Chart - Use this Chart for component comparisons only (Sums to 100%) E.g. Market share: Pie charts are used to display the contribution of each value (slice) to a total (pie). It can quantify items and show them as a percentage where the total of the numbers is 100%. In this graph below, each pie shows the percentage of sales of a particular month. Excel offers 5 different pie chart types: pie, pie of pie (this breaks out one piece of the pie into another pie to show the sub-category), bar of pie, 3-D pie, and doughnut.
  4. Bar Chart - Use this Chart if you have long names and want to compare E.g. Competitor analysis: It is similar to a Column chart but here the constant value is assigned to Y-axis and the variable is assigned to X-axis. In this graph below, each bar represents a sales amount for each month. bar chart in excel.
You can also take a look at this video by Microsoft on how to make a graph in Excel. There are other chart types available in Excel like Area Chart, Surface Chart, Radar Chart, Funnel Chart, etc. You can also create a graph called the Combo Chart that displays multiple sets of data in different ways on the same chart. You can even look at this blog to view our Excel Chart Infographic  and also learn about the new Excel Charts that were introduced in 2016. Now that you have understood How to Make a Graph in Excel and the different types of Excel Charts that are available, it is essential to customize the chart to enhance its appeal.

Customize an Excel chart

When you create a graph in Excel and then click on it, you will see two new tabs appearing on the Excel Ribbon - Chart Design & Format. These two tabs can be used to customize the chart as per your requirements. Chart Design Tab - Chart Desgin Tab in Excel Chart Design Tab in Excel contains design commands including Chart Layout, Chart Styles, Switch Row/Column, Select Data, Change Chart Type & Move Chart. Format Tab - Format Tab in Excel This ribbon contains format commands like Current Selection, Insert Shape, Shape Styles, WordArt Styles, Arrange Group & Size Group. You can also click on the chart and access the Chart Elements button on the upper-right corner of the chart. It contains three options - Chart Elements, Chart Style and Chart Filters Chart Elements button in Excel Chart Element Option (with the + sign icon) Using this option you can customize different chart elements like Chart Title, Axes, Axis Title, Data Labels, Data Table, Error Bars, Legend, Gridlines, and Trendline. To add or remove a chart element, you can simply use the Chart Element Options and check or un-check the boxes you want to show or hide. For example, if you have monthly sales data including sales amount and average sales and you can create a column chart using that data. How to make a graph in excel - column chart Now if you wish to add a Legend key to your chart, follow the steps below: Step 1: Click on the Chart. chart element options Step 2: Click on the + sign icon to access the Chart Elements Options. + sign icon excel chart Step 3: Check the box next to Legend.   How to make a column chart in excel This will add a legend keys next to the chart. To make further customizations like shifting the legend keys to the bottom of the chart: Point to Legend, select the arrow next to it and select bottom. How to make a column chart in excel

Chart Style

After you have learned How to Make a Graph in Excel and customize the elements, you should move on to making the charts more impactful.  You can change the style or color of your chart using this step by step tutorial below: Step 1: Click on the chart you want to change charts in excel Step 2: Click on the Brush icon in the upper right corner of the chart brush icon in excel Step 3: Click Style or Color and pick the option you want. excel chart style        excel chart color Step 4: You can scroll below the different options available and hover over them to get a preview of the styles and colors available and select the one that suits you the best. edited chart in excel This is how your edited chart will look like. Pretty cool right? :) Learning how to make a graph in Excel is not enough. You can match the style and color of the chart as per your company theme colors, font, and layout.

Chart Filters

Ever faced a situation where you want to display only certain data for an Excel Chart. Here is a tutorial on How to Create Graphs in Excel with Filters. Follow the steps below: Step 1: Click anywhere on the chart Step 2: Click on the Chart Filter button on the upper right corner of the chart filter button in excel Step 3: On the Values tab, you can hover the Categories and the corresponding data for that category will be highlighted. In the example below, when you hover over February with your mouse pointer, you can see the bar of February in the Chart being highlighted.  Cool! filtered data in excel Step 4: To filter the data, you can check or uncheck the series or categories you want to show or hide. In the example below, I have checked the first four months (Jan, Feb, Mar, Apr) and clicked on Apply.     This will let you filter the data in your Excel Charts and allow you to select the data you want to display or hide.

Save as Chart Template

Imagine you have around 20 charts that you want to create in Excel. You went through all the steps mentioned above to add Chart Elements, Customize a Layout and Style, and created an excellent Chart. Now, are you ready to repeat all these steps for the remaining 19 Charts? Well, you don't have to do that. You can Save the Chart as Template and then simply apply that template for the remaining charts. Let us follow the steps below and learn how to create a graph in Excel and save it as a template: Step 1: Right Click on your perfect Chart right click on excel chart Step 2: Click on Save as Template Step 3: In the Save Chart Template dialog box, give your template a "name" and click on Save. save chart template dialog box excel   Step 4: Now you move to the next chart where you want to paste this format. make a graph   Step 5: Right-click on that chart and select Change Chart Type make a graph Step 6: In the Change Chart Type dialog box, under the All Charts tab click on Templates and then select the template you have saved. Click OK. change chart type dialog box Your second chart will automatically change to the format of your saved template! Using the steps mentioned above, you can learn not only how to make a graph in Excel but also how to save the created graph as a template and reuse it whenever you want. create a graph

Conclusion

In this tutorial, you have learned how to make a graph in Excel, the different types of Excel charts, how to customize an Excel Chart, and save a chart as a Template. You can learn more about How to Make a Graph in Excel by viewing our Free Excel tutorials on Excel Charts! Make sure to download our FREE PDF on the 333 Excel keyboard Shortcuts here: You can follow our YouTube channel to learn more about How To Create a Graph in Excel!" ["post_title"]=> string(70) "How to Make a Graph in Excel - Make Your Data Talk With Visual Charts!" ["post_excerpt"]=> string(0) "" ["post_status"]=> string(7) "publish" ["comment_status"]=> string(4) "open" ["ping_status"]=> string(4) "open" ["post_password"]=> string(0) "" ["post_name"]=> string(28) "how-to-make-a-graph-in-excel" ["to_ping"]=> string(0) "" ["pinged"]=> string(0) "" ["post_modified"]=> string(19) "2020-05-20 04:58:13" ["post_modified_gmt"]=> string(19) "2020-05-20 02:58:13" ["post_content_filtered"]=> string(0) "" ["post_parent"]=> int(0) ["guid"]=> string(38) "https://www.myexcelonline.com/?p=16024" ["menu_order"]=> int(0) ["post_type"]=> string(4) "post" ["post_mime_type"]=> string(0) "" ["comment_count"]=> string(1) "0" ["filter"]=> string(3) "raw" } ["comment_count"]=> int(0) ["current_comment"]=> int(-1) ["found_posts"]=> string(3) "566" ["max_num_pages"]=> float(81) ["max_num_comment_pages"]=> int(0) ["is_single"]=> bool(false) ["is_preview"]=> bool(false) ["is_page"]=> bool(false) ["is_archive"]=> bool(false) ["is_date"]=> bool(false) ["is_year"]=> bool(false) ["is_month"]=> bool(false) ["is_day"]=> bool(false) ["is_time"]=> bool(false) ["is_author"]=> bool(false) ["is_category"]=> bool(false) ["is_tag"]=> bool(false) ["is_tax"]=> bool(false) ["is_search"]=> bool(false) ["is_feed"]=> bool(false) ["is_comment_feed"]=> bool(false) ["is_trackback"]=> bool(false) ["is_home"]=> bool(true) ["is_privacy_policy"]=> bool(false) ["is_404"]=> bool(false) ["is_embed"]=> bool(false) ["is_paged"]=> bool(false) ["is_admin"]=> bool(false) ["is_attachment"]=> bool(false) ["is_singular"]=> bool(false) ["is_robots"]=> bool(false) ["is_posts_page"]=> bool(false) ["is_post_type_archive"]=> bool(false) ["query_vars_hash":"WP_Query":private]=> string(32) "9bc39cc8b7bab79ff3ae31a0b2479177" ["query_vars_changed":"WP_Query":private]=> bool(false) ["thumbnails_cached"]=> bool(false) ["stopwords":"WP_Query":private]=> NULL ["compat_fields":"WP_Query":private]=> array(2) { [0]=> string(15) "query_vars_hash" [1]=> string(18) "query_vars_changed" } ["compat_methods":"WP_Query":private]=> array(2) { [0]=> string(16) "init_query_flags" [1]=> string(15) "parse_tax_query" } }