Excel Tutorial for Beginners

Summary

In this informative video, Kevin introduces viewers to Microsoft Excel, highlighting its capabilities for data analysis and providing a step-by-step guide on how to get started with the software. He covers various features such as data entry, formatting, table creation, and powerful tools like conditional formatting, pivot tables, and charting. The video also touches on Excel's analytical functions, including sum and average, and concludes with sharing options for collaboration. This comprehensive overview aims to equip users with the skills to analyze and present data effectively.

Takeaways

• 📈 Access Excel via excel.new in your web browser for a free version, or install it on your desktop with Microsoft 365.
• 📊 Excel's interface consists of cells organized into columns (letters) and rows (numbers), with each intersection identified by its column and row (e.g., E7).
• 🔍 Navigating Excel's start page reveals options for new workbooks, templates, and recent files, as well as search functionality.
• 📌 Zoom in and out of your spreadsheet using the zoom controls or by pressing control and scrolling the mouse wheel.
• 📋 Entering data is straightforward; Excel can automatically fill in patterns like dates or sequences of numbers.
• 🔢 Use the thousands separator and remove decimals for easier readability of numerical data.
• 📏 Adjust column widths and row heights to fit content or for better visibility of data.
• 🔧 Easily move, insert, or delete columns and rows to organize your data as needed.
• 🎨 Format your data with tables and conditional formatting for improved readability and visual appeal.
• 📊 Utilize Excel's powerful functions like SUM and pivot tables for data analysis without complex formulas.
• 🔍 Sort and filter data within your table to focus on specific segments or to view data in different orders.
• 📈 Create visual representations of your data with charts and pivot tables for a quick understanding of trends and patterns.

Q & A

What are the two ways to get started with Excel as mentioned in the transcript?

The first way is to navigate to excel.new in your web browser, log in for free, and start using Excel on the web. The second way is to install Excel on your desktop by purchasing Microsoft 365.

What is the purpose of the templates available on the start page of Excel?

The templates provide a pre-structured format that can be used to meet specific needs, such as budgeting, project management, or invoicing, streamlining the process of starting a new document.

How are the rectangles on the screen in Excel referred to?

The rectangles on the screen are referred to as cells, which are the basic units for data entry and manipulation in Excel.

What is the significance of the letters across the top and numbers down the side in Excel?

The letters across the top represent the columns, and the numbers down the side represent the rows. Their intersection, such as cell E7, helps identify the specific location of data within the spreadsheet.

How does Excel detect a pattern when entering data?

Excel detects a pattern based on the sequence and type of data entered. For example, if a user enters a series of dates or numbers in a particular order, Excel recognizes this pattern and can auto-fill the subsequent cells accordingly.

What is the purpose of the 'thousands separator' and how is it applied in Excel?

The 'thousands separator' is used to make large numbers more readable by inserting a comma every three digits. It can be applied by highlighting the cells, going to the 'Home' tab, and clicking on the appropriate icon to add the separator.

What is the benefit of using a table in Excel?

Using a table in Excel provides a structured way to organize data, making it easier to read and analyze. Tables also come with additional features like banded rows, the ability to easily add or remove columns or rows, and the option to use filters and sort data.

How can you move a column in Excel?

To move a column, you highlight the entire column, press the shift key, hover over the edge until the cursor changes, then click and drag the column to the desired position.

What is the function of the 'conditional formatting' feature in Excel?

Conditional formatting allows cells to be formatted based on their content or values. For example, you can apply different colors or data bars to indicate high or low values, making it easier to visually identify trends or patterns in the data.

How does the 'analyze data' pane in Excel assist with data analysis?

The 'analyze data' pane enables users to ask questions about their data, and Excel provides insights or answers without the need for manual calculations or formulas. This feature simplifies the process of data analysis and interpretation.

What is a pivot table in Excel and how is it used?

A pivot table is a powerful tool used for summarizing and analyzing data by organizing it into a dynamic, interactive table. Users can easily rearrange fields, apply filters, and calculate various summaries like sums or averages, providing a flexible way to view and analyze data from multiple angles.

How can you share an Excel workbook with a team?

To share an Excel workbook, the workbook must be saved in OneDrive. Then, by clicking the 'Share' button in the top right corner, you can select team members to share the workbook with and send them an invitation to access the document.

Outlines

00:00

📊 Introduction to Microsoft Excel and Basics

This paragraph introduces viewers to Microsoft Excel, emphasizing its capabilities for data analysis and insight extraction. It outlines two methods for starting with Excel: using the free web version at excel.new or purchasing Microsoft 365 for desktop access. The video then guides users through the initial interface, including the start page with templates and recent workbooks, and explains the fundamentals of cells, columns, and rows. The importance of understanding the Excel environment is highlighted to effectively interpret data stories.

05:04

🔢 Data Entry and Formatting in Excel

The second paragraph focuses on entering and formatting data within Excel. It demonstrates how to input headers and data, such as dates and sales figures, and how Excel's auto-fill feature can assist with entering repetitive information. The paragraph also covers adjusting cell formatting, including adding thousands separators and removing decimal places, to enhance data readability. Additionally, it introduces the use of context menus for managing columns and rows, such as hiding or deleting them, and the ability to move and auto-fit columns for better presentation.

10:10

📊 Enhancing Data Visualization with Tables and Charts

This section delves into improving the visual presentation of data through the use of tables and charts. It explains how to insert and style tables for better readability, including the use of banded rows and the table design options. The paragraph also demonstrates how to insert and customize charts for a visual representation of sales data. Furthermore, it introduces conditional formatting to highlight trends based on cell values, and the use of functions like SUM for calculations, providing a more interactive and insightful analysis of the data.

15:12

🔧 Advanced Data Analysis with Pivot Tables and Sharing

The final paragraph covers the advanced data analysis capabilities of Excel through pivot tables. It illustrates how to create and manipulate pivot tables to quickly summarize and analyze data in various ways, such as calculating sums, averages, and percentages, and filtering data based on specific criteria. The paragraph also touches on the power of pivot tables to transform data presentation without complex formulas. Lastly, it briefly explains how to share the workbook through OneDrive, ensuring collaboration and data sharing among team members.

🚀 Wrapping Up and Further Learning

In the concluding paragraph, the video wraps up with a brief recap of the Excel overview provided and encourages viewers to continue their learning journey. It offers a playlist of free YouTube videos for further exploration and mentions a structured course for in-depth learning. The video ends with a call to action for viewers to subscribe for more content, highlighting the goal of empowering viewers to enhance their data analysis skills.

Keywords

💡Microsoft Excel

Microsoft Excel is a powerful spreadsheet application developed by Microsoft. It is widely used for data analysis, budgeting, and various other data manipulation tasks. In the video, Excel is introduced as a tool to analyze and gain insights from data, with the presenter guiding viewers on how to navigate and utilize its features effectively within a short timeframe.

💡Spreadsheet

A spreadsheet is a digital document that organizes data in a grid of rows and columns, allowing users to perform calculations, analyze information, and track data. In the context of the video, the presenter explains how to create a new spreadsheet in Excel, which is the fundamental workspace where users input and manipulate data.

💡Cells

In Excel, a cell is the intersection of a single row and a single column, where data is entered or manipulated. Cells are referenced by a combination of a column letter and a row number, such as 'E7'. They form the basic unit of data organization within a spreadsheet and are essential for data entry and calculations.

💡Data Analysis

Data analysis involves the process of inspecting, cleaning, transforming, and modeling data to discover useful information, suggest conclusions, and support decision-making. In the video, the presenter aims to teach viewers how to use Excel as a tool for data analysis, enabling them to extract meaningful insights from their data sets.

💡Templates

Templates in Excel are pre-built spreadsheets that provide a structured format for specific types of data or projects. They save time by offering a starting point with formats and formulas already in place, allowing users to focus on inputting their unique data rather than setting up the structure from scratch.

💡Conditional Formatting

Conditional formatting is a feature in Excel that allows cells to be formatted based on their values or the rules set by the user. This can include changing the cell's color, font, or borders to highlight important data, making it easier to identify trends or significant values at a glance.

💡Pivot Tables

Pivot tables are a powerful data summarization tool in Excel that allows users to reorganize, analyze, and interpret data from multiple perspectives without altering the source data. They enable the creation of dynamic summaries that can be refreshed as new data is added, making them ideal for in-depth data analysis.

💡Formulas and Functions

Formulas and functions in Excel are mathematical expressions or predefined formulas that perform calculations on data within the spreadsheet. Functions like SUM, AVERAGE, and others automate complex calculations, allowing users to manipulate data more efficiently and accurately.

💡Data Visualization

Data visualization refers to the process of representing data and information graphically, making it easier to understand and interpret. In Excel, this can be achieved through charts, graphs, and other visual representations that transform raw data into a format that can be quickly comprehended.

💡Sharing and Collaboration

Sharing and collaboration in the context of Excel refer to the ability to share workbooks with others for review, editing, or feedback. This feature allows teams to work together on spreadsheets, ensuring that everyone has access to the most current version of the data and can contribute to the analysis and decision-making process.

💡AutoFit

AutoFit is a feature in Excel that automatically adjusts the width of columns or the height of rows to fit the content within the cells. This ensures that all data is visible without having to manually resize each column or row, improving the readability and presentation of the spreadsheet.

Highlights

Introduction to Microsoft Excel and its capabilities for data analysis.

Two ways to get started with Excel: online via excel.new and desktop with Microsoft 365.

Explaining the layout of Excel, including cells, columns, and rows.

Entering and managing data efficiently, including auto-filling dates and numbers.

Formatting cells with thousands separators and removing decimals for clarity.

Adding context to data with headers and notes for better understanding.

Adjusting column widths and row heights for optimal viewing.

Rearranging and moving columns for better data organization.

Inserting and using tables in Excel for improved data presentation.

Utilizing conditional formatting to visually represent data trends.

Analyzing data with simple calculations and functions like SUM.

Sorting and filtering data to focus on specific information.

Creating charts for visual data representation.

Exploring the power of pivot tables for dynamic data analysis.

Sharing Excel workbooks with a team for collaboration.

Transcripts

Hi everyone, Kevin here. Today, we are going  to learn how to use Microsoft Excel in just  

15 minutes. Excel is the perfect tool to  analyze and to get insights from your data,  

but there are so many different menus and so  many different buttons. What do they all do?  

In this video, we're going to make sense of them  so you can start understanding the story behind  

your data. Let's start with how you can even get  Excel. There are two different ways that you can  

get started with Excel. The first way, you can  navigate to excel.new in your web browser. You'll  

need to log in, but that's entirely free, and  that'll drop you into a brand-new spreadsheet  

directly in your web browser. Excel on the web has  most of the functionality that you'll find in the  

desktop app and typically new features hit the web  first. Second, you can also install Excel on your  

desktop, but you will need to purchase something  called Microsoft 365. If you're interested in  

that, you can check out the product tagged to this  video, and that helps support this channel. When  

you launch Excel for the first time, you'll land  on the start page. In the top left-hand corner,  

you can jump into a blank new workbook, and in a  moment, we'll do this. Over on the right, you'll  

also see a whole host of different templates.  It's well worth looking through these to see if  

maybe one of these meets your needs. Down below,  you can get back to recent workbooks that you  

worked on. Right up on top, you can search for a  workbook and down below, you'll see all of your  

recents. In the top left-hand corner, let's click  into a blank, new workbook. This drops us into a  

brand-new workbook, and at first glance, you might  notice that, wow, we have a lot of rectangles on  

the screen. These are all referred to as cells.  Across the top, you'll notice that we have all  

these different letters. These are referred  to as columns and over on the left-hand side,  

you'll see that we have all these numbers going  down the screen. These are referred to as rows.  

The intersection of the column and the row, this  is referred to cell E7. You start with the column  

and then you follow with the row. In the top  left-hand corner, you'll see it's referred to here  

as E7 in the name box. You can also change the  name, but for this we'll stick with the default.  

To make things easier to see, we can zoom in and  out. In the bottom right-hand corner, we can zoom  

in or here you can zoom out. You can also press  control and then move your mouse wheel up or down,  

and that will also zoom in and out. My eyes aren't  what they used to be. Let's start now by entering  

in some data. I'll click into cell A1 and here  I'll type in a header sales. To move over to  

the next cell, I can click on it with my mouse. I  could also press tab, or the right arrow key on my  

keyboard and that'll move me over to the next cell  and here I'll type in date. To move down here,  

I could press the down arrow key or I could  press enter. I want to track cookie sales  

here at the Kevin Cookie Company starting in  January 2023. Here I'll type in January 2023,  

and then hit enter. So that's the first month I'll  track. And I want to track all the way through  

November 2023. Now, of course I could go through  and type in every single month, but Excel is smart  

and it detects a pattern. Here it sees that I  entered a date. When I click into cell B2, you'll  

notice that there's this rectangle in the bottom  right-hand corner, and when I hover over it,  

my cursor changes. I can press and hold on that  and then I could drag it down and here that'll  

fill in all the different months. This works  with dates. It also works with numbers as long  

as Excel can detect a pattern. Next, we need to  enter in some data for how many cookies we sold.  

January is by far the worst month for the cookie  business. Everyone has a new year's resolution  

that they just don't want to eat cookies. And I'll  go through and fill in numbers for the rest of the  

months, and feel free to follow along. I've now  entered in all of our cookie sales. Like I've  

always said, the cookie business is a good one  to be in. It's a little difficult to parse these  

numbers just at a glance. Ideally, I could have a  thousands separator. I'll highlight all of these  

cells and up on the home tab within the home  ribbon, in the center, here I can click on this  

icon to add a thousands separator. That makes it  much easier to tell which numbers are larger and  

which are smaller. But I don't need this decimal  place. We don't sell fractions of cookies here. Up  

on top, I'll click on this icon and I can remove  those decimal places. Now as a neat little pro  

tip, you can press control together with the one  key on your keyboard, and this opens up the format  

cells dialogue. Here you have full control over  what the format of the cell is. If you'd like  

to learn all about the different shortcut keys  available in Excel, I've included a link down  

below that'll show you every possible option.  Looking over at my table, I think it would be  

helpful to provide some context for why January  was such a low sale month. I'll click into cell  

C1 and add another header titled notes. And here  in C2, I'll provide an explanation. New Year's  

resolution depresses sales. You'll notice that the  explanation bleeds over into the adjacent columns,  

and ideally, I would like for all of it to fit  within column C. I can click on this line in  

between C and D and I can double click and that'll  auto fit the contents, so there it expands C. If  

you have many different columns or many different  rows that you would like to fit to the content,  

you can click on this icon and then click on any  line in between two columns and that'll auto fit  

everything. It's a handy little trick. On second  thought, I don't know if I need this column.  

The management here should already know this  information. At least I would hope. On column C,  

I'll right click, and this shows me a context menu  with different actions that I can take. And right  

down here, I can delete the column, but I don't  know if I want to get rid of this information.  

Maybe someone will ask some questions. Down  at the very bottom, I can also hide a column.  

I'll click on that and it doesn't remove the data.  It's just hidden on the sheet. To show it again,  

I'll highlight these two columns, right click,  and here I have the option to unhide. I can also  

do this with rows as well. Looking at all of my  data, I actually think it would be better to show  

the date first followed by the sales. The good  news is it's very easy to move columns in Excel.  

I'll highlight all of this data and then press the  shift key on my keyboard and hover over the edge.  

You'll see that my cursor changes. I can now press  my left mouse button and I can move this column  

to a new position. I'll place it to the left of  sales, and now we see the date first. Of course,  

we can't see all of the sales. The cell just isn't  wide enough. Right up on top, I'll click on this  

icon again and here I can expand it, so it fits  all of the contents. That's exactly how I want it.  

I also think it would be nice to just format this  table, so it looks better. Up on the top tabs,  

let's click on insert, and here we have the option  to insert a table. I'll click on that. And here  

it automatically identifies all this data. We  have headers. I'll make sure that's checked and  

then click on okay. And look at that. I now have  banded rows. It's a lot easier to read this table.  

Over on the right-hand side under table  design, here we have all different types  

of styles that we can choose. I'll stick  with the default. The benefits of tables  

go beyond just the look and feel. Right here, I  could also add what's called a total row. Here,  

you see the total down at the bottom, and if I  click on this little dropdown arrow, here I could  

choose what I want to total up. Let's get a sum  of all these sales. Look at that. 75,000 sales.  

We're doing really well. Now I almost forgot to  include December data. That's by far our best  

month of the year. It's amazing how people forget  about calories during the holidays. Luckily,  

it's very easy to add either rows or columns.  I'll click on row 13 and then right click,  

and here's the option to insert. I'll select that.  Click into this cell. Here I could drag down and  

that'll fill in December and let's type in the  sales. That was a great month. That feels about  

right now. Of course, December was a great month  and January not so good, but just glancing at this  

table, it's a little hard to tell very quickly.  I'll highlight all of these cells and up on the  

home tab in the center, there's something called  conditional formatting. This allows us to format  

the cells based on a condition or the underlying  data. And we have all sorts of different options  

here. You could show data bars, color scales.  You could even define your own rules. For this,  

let's go with color scales and let me try this  one. This will apply red for lower numbers and  

green for higher numbers. Now, when I look at this  data, I could very quickly tell that December was  

by far the best month of the year for us. I now  want to start analyzing my data and luckily Excel  

makes this really easy. First, I want to know what  were total sales in Q1. So, January through March,  

I can simply highlight these three cells, and down  at the bottom on something called the status bar,  

here I can see that total sales were about 12,000.  Not bad. I can also go up to the home tab and over  

on the right-hand side, let's click on analyze  data. This opens up the analyze data pane,  

and the really neat thing here is I can simply  ask questions about my data and then Excel will  

provide back insights. Let's ask what were total  sales in Q1, and I want to see that as a table.  

And right here, I can see sales and there it  was 12,000. That makes analysis really easy,  

and I didn't even have to enter in a function or  a formula. I'll close out of this pane. Of course,  

we can also calculate this on our own. I'll click  down into this cell and let's add up Q1. I'll  

enter in the equal sign. This lets Excel know  that we're about to enter in a formula. Next,  

I'll click into cell B2 and there you see it in  my formula. And I want to add this. I'll enter in  

the plus sign to cell B3 and I want to add that  to cell B4. Here you see my formula down below.  

I'll press enter and there too, you also see  that the total was 12,000. Along with addition,  

you could also do subtraction, multiplication, and  also division. Now that was a little cumbersome to  

click into each individual cell that I wanted to  add up. Alternatively, we can also use something  

called a function. One of the most popular  functions is sum. Again, I'll enter in the equal  

sign and type in the function name, sum. Then I'll  open up the parenthesis and here I need to pass in  

an argument or basically all the numbers that  we want to sum up. Here, I'll simply highlight  

these three cells, so here you see B2 through  B4, and then close the parenthesis, hit enter,  

and there too, we also see that the sum is 12,000.  Excel has many different functions available.  

Up at the very top, let's click on the formulas  tab and over here, we can see some of the most  

popular functions that you might want to use and  over here, we'll see many different options for  

functions, so it's well worth looking through to  see what you can do with functions. As we've been  

going through this, you might've noticed that we  have these arrows that appear next to our headers  

in the table. I'll click on one of the headers  and then let's click on the data tab. Over here,  

you can toggle that on or off, but  let's leave it on to see what they do.  

Now over here, let's click on this arrow and this  opens up a context menu and here I can sort my  

data. So, let's sort from largest to smallest. So  here I see December, which had the greatest sales,  

and then we have January down at the very bottom.  But let's say I want to restore it to the original  

order. Here, I'll click on date and let's sort  from oldest to newest, and right here, we're back  

to where we were. Let's say I only want to look  at Q1, I can click on this and along with sorting,  

I can also filter my data. I'll click on this  to deselect all of these dates and let's just  

select Q1 January, February, March, click on okay  and here I see my first quarter and look at the  

total row. It also tells me that it was 12,000.  So, another way to calculate that. I'll click on  

this again and here I can clear the filter. Up  to this point, we've just been looking at all  

of our data in a tabular format, but sometimes  a picture or a chart is worth a thousand words.  

Let's go up to the insert tab up on top and  right here in the center, you'll see the option  

for charts. We could insert a recommended chart,  or we could choose one of these many different  

options. I'll click on recommended charts and here  it recommends a line chart, which works well with  

this type of data. I'll click on okay and this  inserts a chart in and now we can visually see  

what sales were like throughout the year. That's  a lot easier to parse the data. Up on top, we have  

all sorts of different tools that we can use to  customize the way this chart looks. We've covered  

quite a bit of content so far, but we're going to  finish up with one of the most powerful analysis  

tools available in Excel. And you can analyze data  just by dragging and dropping your mouse. Let's  

click into the table of data over on the left-hand  side, then go up to the insert tab and here's the  

option to insert a pivot table. Let's click on  that. Here, it's identified all of our data and  

let's place it on a new worksheet and then click  on okay. This now drops us into a new worksheet.  

Right down below, you see that we're in sheet  two, and if we click into sheet one, this will  

bring us back to our original data. Let's click  into the pivot table. Over on the right-hand side,  

you'll notice that we have something called pivot  table fields with all these different items.  

You might recognize these. These are all the  different columns that we had in our data table.  

Now check out what you could do with pivot  tables. I'll press and hold on sales and drag  

that down into values. You typically place  something in values if you want to calculate  

something. And over here on the left-hand side,  I'll zoom in and here we see the sum of sales,  

almost a hundred thousand. That's how many  sales we had. Now, one of the neat things is,  

here I could right click on that and I can go down  to summarize values by. Currently it's set to sum,  

but let's see the average sales over the course  of the year and there we sold about 8,200 or so  

per month. I'll right click and let's go back to  sum. Over on the right-hand side, I could take  

another item, like let's say the date and I could  drag that down into filters. And here I can click  

on this dropdown, just like that dropdown we saw  earlier, and let's filter just to January. I'll  

click on okay and here we see those 1000 sales  that we had in January. Let's remove the filter  

over here. I'll drag that out. Again, you could do  all this analysis just by dragging and dropping.  

Let's take the date and drag it down into the  rows. Now this looks very similar to what we  

had on sheet one, where we have the date and then  also the sales. Now, instead of putting it down  

this way, I can also drag out the date and let's  put the date as columns and here we see it going  

across the columns. So, you could very quickly  visualize your data in different ways and it  

doesn't require much effort to do that. I'll pull  these out and let's pull the date back into the  

rows. Now here's one more really neat thing  you could do. Here we see the sum of sales,  

but let's say I want to know the percent of sales  that that month made up. Here I could right click  

on one of the cells and we could go down to show  values as and let's select percent of grand total  

and there I see that January made up about 1% of  our sales for the year and here December made up  

24%, so quite a bit more. Pivot tables are such a  powerful tool. If you'd like to learn more about  

them, be sure to check out the Excel playlist  that I've included in the description and it  

walks through them in depth. Now that we've  done all this analysis on our cookie sales,  

I want to share it out with the team. In the  top right-hand corner, let's click on this share  

button and then let's select share. You'll have to  make sure that your workbook is saved in OneDrive,  

but once you do that, right here, I can click  on this and I can now select people from my team  

who I want to share this workbook with. Once I'm  all done, I can click on send. All right, well,  

that was just a really quick overview of Excel,  but hopefully that gives you enough to start  

building that muscle in data analysis. To continue  your learning journey, I've included a playlist  

with all of my free videos on YouTube down below.  If you're interested in a more structured way of  

learning about Excel, I also have a course that  you could click on in the top right-hand corner,  

and that walks through all of the fundamentals  of Excel. To watch more videos like this one,  

please consider subscribing and  I'll see you in the next video.