Excel Tutorial for Beginners
Summary
TLDRIn 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
📊 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.
🔢 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.
📊 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.
🔧 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.
Mindmap
Keywords
💡Microsoft Excel
💡Spreadsheet
💡Cells
💡Data Analysis
💡Templates
💡Conditional Formatting
💡Pivot Tables
💡Formulas and Functions
💡Data Visualization
💡Sharing and Collaboration
💡AutoFit
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.
Browse More Related Video
TLE 7- MATATAG CURRICULUM LESSON (1ST QTR)- CREATING SPREADSHEETS W/CONDITIONAL FORMATTING
Introduction to Power BI: What is it?
Tabelle Pivot - EXCEL TUTORIAL ITALIANO 50
Microsoft Excel 2019 - Full Tutorial for Beginners in 17 MINUTES!
MATATAG TLE7 ICT: WEEK 8 SPREADSHEET SOFTWARE(EXCEL) Conditional formatting and Analyze Data
Interactive Excel Dashboard Tutorial in 3 Steps (+ FREE Template)
5.0 / 5 (0 votes)