EXCEL BASICS - Begin Your Excel Learning here
If you're an Excel beginner and want to start your Excel learning, this is the perfect place to start.
This page will introduce you to various basic and advanced Excel features. The page is divided into various sections where you will find useful Excel tutorials and links.
Understanding Excel Layout
It could be a bit intimidating for someone to open a workbook and look at so many things in Excel. Here are the five things you need to know in the Excel layout.
1. Excel Ribbon: The ribbon area is where you have all the tools and options to work in Excel. There are different tabs at the top and you can click on a tab to see all the options within it. For example, Home is a tab and within it there are various options.
This ribbon layout was introduced in Excel 2007 and has been there since then.
2. Quick Access Toolbar (QAT): Quick Access toolbar gives you quick access to various useful tools and features. For example, if you often need to filter data in Excel, you can add that the QAT and it will always be visible and just a click away.
3. Worksheet Area or Spreadsheet Grid: This is where you enter, analyze, and report data. The entire area is divided into rows and columns. These rows and columns are further divided into cells. These cells can hold data (such as numbers, text, dates) and formulas.
4. Formula Bar: When you work with formulas in Excel, you can enter it in a cell, or you can select a cell and enter it in the formula bar. In case you already have a formula in a cell and you select it, the formula would be visible in the formula. It helps you create and edit formulas.
5. Status Bar: Status bar, as the name suggests, would let you know of the current status. For example, if you're doing a calculation, it will show calculating. If you're recording a macro, it will show that. A more useful feature of the status bar is that if you select a set of cells, it will show some basic statistics. For example, if you select cells with numbers, it will show the sum and the count of it.
Data Entry and Formatting
Once you know are comfortable with the layout of Excel, the next step is to learn how to enter and edit data in Excel.
As mentioned, you can enter three types of data in Excel - numeric, text, and formulas.
To enter data in any cell, just select the cell and start typing. Once done, press enter to move to the cell below. In case you are entering dates, enter it in a format that Excel recognizes as a date (such as 01-01-2020 or 01 Jan 2020 or 01/01/2020).
You can also format data to make it look better and to highlight some data points. For example, if you have dataset and you want to highlight the negative numbers, you can simply give a red color to it.
Here are some of the common formatting I use to make my data look better and more readable
- Applying borders to all cells
- Making headers bold
- Giving a background cell color to headers.
- Center align the headers
You can find these options in Home tab in the Font category.
Basic Excel Tutorials
Excel is full of various tools and options that can help you get work done faster and save time.
Excel Data Filters: While working with large data sets, there is a need to drill down and analyze a specific part of the data. For example, if you have sales trasanction records and you want to see the records for a specific product, you can do that using data filters.
Read More: Using Excel Data Filter - An Introduction
Sorting Data in Excel: Data sorting is another thing you can get done in Excel. You can sort the data alphabetically, numerically, or based on a custom list.
Read More: Sorting Data in Excel
Find and Replace Data in Excel: In Excel, you can use find and replace options to find and, if needed, replace the data. For example, if you are working with a dataset and you want to find all the instanced of ABC and change it to ABC Ltd., you can do that using Find and Replace.
Read More: Using Find and Replace in Excel
Excel Freeze Panes: While working with large datasets, if you scroll to the right or scroll down, the headers disappear. This can be inconvenient as you may lose track of what a data point means without being able to map it to the headers. By using Excel Freeze Panes option, you can lock the rows and columns in such a way that these are always visible.
You Excel learning can not be complete without knowing how to use Excel functions efficiently. There are 450+ Excel functions in Excel, but in normal day to day work, you're likely to rely only 20 odd functions.
You can begin learning about the basic functions such as SUM, COUNT, AVERAGE, MAX, MIN, etc.
Once you're comfortable with these, you can move on to more advanced functions such as VLOOKUP, INDEX, MATCH, OFFSET, SUMIF, SUMPRODUCT, etc.
Click here to access more than 100 Excel functions tutorials explained with examples and video.
Advanced Excel Tutorials
Once you're comfortable with Excel's layout and basic tools in Excel, you can think of getting your hands dirty with some advanced tools and techniques.
Here are some powerful advanced Excel features that can do a lot of heavy lifting for you and make you highly productive.
Excel Table: If you work with tabular data, you must use Excel tables going forward. I call it a hidden treasure as a lot of people don't use it. When you use Excel Tables, it makes it easier to manage and use the data in calculations.
Conditional Formatting: As the name suggests, conditional formatting formats a cell when a specified condition is met. For example, if you want to highlight the names of all the students who scored above 90, you can do that using conditional formatting.
Paste Special: When you copy and paste a cell, it copies everything (including the color, font, borders, formula, value). But what if you only want to copy the value or only the formatting. In such cases you can use the Paste Special options.
Pivot Table: Pivot Table is one of the best features in Excel. It allows you to analyze huge amount of data and quikly summarize it in seconds. The best part about using Pivot Tables is that even if you don't know anything else in Excel, you can still use it. It has a simple drag and drop interface that makes it really easy to use Pivot Tables.
Here are some useful tutorials on using Pivot Tables in Excel:
- Creating a Pivot Table in Excel - A Step by Step Tutorial
- How to Prepare the Source Data for Pivot Tables
- How to Filter Data in a Pivot Table
- How to Use Slicers in Excel Pivot Table
- How to Group Numbers in a Pivot Table in Excel
- How to Group Dates in a Pivot Table in Excel
- How to Refresh a Pivot Table in Excel
- How to Replace Blank Cells with Zeros in Excel Pivot Tables
- How to Apply Conditional Formatting in a Pivot Table
- Pivot Cache in Excel - What is it and How to Use it
Drop Down Lists in Excel: Drop Down lists can be useful while enabling data entry or while creating interactive charts/reports. A drop down list allows you to select from a pre-specified list of items. It can be also be used to extract records for the selected item or update charts/dashboards based on the selection.
Here are some additional useful advanced Excel tutorials:
Excel has various chart types that you can use to visually show your data. You can also combine charts to create combination charts (such as bullet chart, actual vs target chart, etc.).
- How to Create a Gantt Chart in Excel
- How to Create a Milestone Chart in Excel
- How to Create a Pareto Chart in Excel
- How to Create a Waffle Chart in Excel
- How to Create a Step Chart in Excel
- How to Create a Bullet Chart in Excel
- How to Make a Histogram in Excel
- How to Create a Sales Funnel Chart in Excel
- How to Create a Thermometer Chart in Excel
- Actual Vs. Target Chart in Excel
If you're looking for online Excel training, consider joining one of my training programs. These are 100% online Excel training that you can access anytime and learn at your comfortable pace.