Beginner’s Guide to Microsoft Excel

Most people just start using Microsoft Excel without much thought. After all, it seems so easy to use Excel.

Yet, if you don’t have a strong foundation of Microsoft Excel, you will begin to struggle after a while… Most people begin to feel overwhelmed when they see

  • begin doing data entry,
  • need to sum or count the data quickly,
  • want to format the data properly.

And if you don’t know the shortcuts to do such things quickly, you’ll be wasting a lot of time, causing delays.

Step by Step Beginner’s Tutorial on Microsoft Excel [2022]

In this step-by-step Excel Beginners tutorial by ExcelChamp Founder Vinai Prakash, we teach you to get started with Excel easily.

This video is created for beginners in Excel. You can use any version of Excel, but you can still benefit from the steps and it will help you in using Excel easily.

In this Excel Beginners Tutorial Video, We show you step by step, how to do the following tasks:

  • Start Excel,
  • Open a blank workbook,
  • Get used to the rows, columns, and cells in Excel
  • How to do data entry in Excel
  • How to cut, copy, paste, move and select multiple cells, rows and columns in Excel,
  • Where is the formula bar in Excel,
  • How to use the formula bar in Excel,
  • Using the name box for navigating within an Excel file
  • Adding multiple sheets in Excel
  • Renaming a sheet in Excel
  • Deleting a sheet in Excel,
  • How to do formatting in Excel – Bold, Center etc.
  • How to Merge Headings in Excel
  • How to add borders
  • How to format the values and text in Excel.

Improve Your Speed, Accuracy, and Competence in Microsoft Excel with a strong foundation in Microsoft Excel.

After viewing the video tutorial carefully, a couple of times,

  • you will become an expert in the basic usage of Microsoft Excel,
  • You will be able to finish your data entry in Excel faster,
  • You will be able to work with multiple Excel sheets, rows, columns, and
  • begin to do data formatting in Excel like a Pro!

I am sure you will enjoy this Excel Beginner’s Video tutorial. And let me know what you would like to learn next… I’ll be happy to receive a message from you – Vinai 🙂

Author: Vinai Prakash

I help Excel users on how to properly and effectively use Microsoft Excel formulas and functions, Pivot Tables, Charts, and Shortcuts with Tips & Tricks to get their day-to-day work like data entry, consolidation, analysis, charting, reporting etc. is done quickly and efficiently, without any errors, every time!

Showing The Hidden First Column in Excel

It is quite easy to show or hide columns in Excel. Simply select a column, and press Control + 0 . The column is hidden from the view. You can make it out because of a dark line separating the columns. If you hide the B column, you will only see column A & Column C, and it is apparent that column B is hidden.

To unhide, simply select both column A & Column C. Then right click and select Unhide. Column B is brought back into view.

This works great most of the time. And it works in showing or hiding rows too.

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.

To Get Most out of Excel, Learn the  Pivot Table techniques in our
Pivot Table Masterclass Training
!


But the problem arises when you want to hide the first column – Column A. Now it is difficult to select 2 adjacent columns, and you are unable to Unhide Column A.

Steps to Display the Hidden First Column (Column A) in Excel

  1. Press F5. The Go To dialog box will popup.
  2. Key in the cell A1 in Reference, and press Enter.
  3. The cursor would have moved to the cell A1, even if you can not see it. Do not worry.
  4. Now go to the Home Tab (in Excel 2007, 10 & 2013)
  5. Click on Format button – it is near the far end of the screen… toward the right side of the ribbon.
  6. Choose Visibility > Hide & Unhide. Then select to Unhide Columns.

    Unhide columns or Rows in Excel
    Unhide columns or Rows in Excel
  7. Voila! You will now be able to see the column A. It has been un-hidden.

It is a simple trick. Excel is all about simple tips and tricks… The more you practice, the more you try, the more gold shall ye find!

Cheers,
Vinai Prakash

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!

Quick Tip: Re-Sorting a List or Table in Excel

Of course you know how to sort data in Excel! Simply click on the Sort button, or click on the mini A-Z or Z-A chicklet buttons, and voila! the list is sorted. And if you want more control, you can try Custom Sorting, and Advanced Sorting with this Method.

The problem is that a sorted list often gets out of sync, if you keep adding or editing data in the table. And if you use a Custom Sorting sequence, you can’t simply click on the single column sorting chicklets any longer  🙁

So you have to go to Custom Sort, and click it again to get the list sorted one more time.

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.

To Get Most out of Excel, Learn the  Pivot Table techniques in our
Pivot Table Masterclass Training
!


A Quick Way to Sort Your Custom Sorted Table in Excel
Fortunately, there is a quicker and shorter way. All you need to do is to click on the Reapply button, which shows up once you click on the Sort menu on the Home Tab.

Reapply Sorting in Excel
Reapply Sorting in Excel

There’s a shortcut – Ctrl + Alt + L

This makes the current list or table sorted again. Even if you had multiple columns in the custom sorted list.

What I have done is to put this Reapply button on my Quick Access Toolbar in Excel. This makes it much snappier to apply it anytime, anywhere in Excel.

Reapply Sorting Button in the Quick Access Toolbar
Reapply Sorting Button in the Quick Access Toolbar

Hope you like this quick tip. It has saved me quite a few clicks, and a lot of time searching for something in a not so sorted table…

Cheers,
Vinai Prakash

PS: Check out how to sort in a unique manner – not ascending , not descending. Sorting it Your Way!

Also, let me know if there is anything in Excel that bothers you…. anything where you could get some help. Simply post it into a comment or email it to me, and I’ll see what I can do to help you out 🙂 – Vinai

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!

Top 10 Time Saving Tips in Using Microsoft Excel

If you are a data warrior, chances are that you use Excel extensively in your day to day work. Learn to Analyze Data Quickly With Excel.

And there are many ways to boost productivity within Excel. Use these nifty tricks and improve productivity.

1. View All Formulas in Excel With a Single Click: I wrote about this trick some time back. It allows you to view all the formula with a single click of Control + ` Key. This acts like a toggle key. This can save a lot of time in finding where are the formulas to edit/view them.

2. Clean up Your Data  – Identify the Duplicates, and Remove Duplicates: There is no point in having duplicates in your tables. There are multiple ways to do this. You can identify the duplicates, and then choose to remove them manually,  or you can request Excel to remove the duplicate rows completely, automatically.

3. Colour Alternate Rows and Make it Easier to Read Large Data Sets: Prior to Excel 2007, we used to write formulas to colour alternate rows in Excel. This makes it easier to read the data.

=MOD(ROW(),2)=0

This function would tell us if the row is an even row or an odd row, and then we could colour it.

But since Excel 2007, we can do this in multiple ways. We can convert the data into a table. And in the table options, you can then colour alternate rows or columns as bands.

And then you can also use the formula above, and use conditional formatting.

4. Learn The In-Built Functions in Excel: Many people are amazed at the plethora of in-built functions available in Excel. But you must know that they exist, and also know the syntax to make a good use of them.

5. Learn and Use Pivot Tables Effectively: Pivot tables summarize data quickly. Learn to use them effectively.  Simple techniques like displaying both values and percentages in a Pivot Table adds extra value.

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.

To Get Most out of Excel, Learn the  Pivot Table techniques in our
Pivot Table Masterclass Training
!


Similarly, learn to show different grouping for different pivot tables, even though it is based on the same data source. Also, creating a calculated field in a pivot table adds extra value.

If you are new to pivot tables, this introduction on how to analyze data using Excel pivots will be useful.

6. Charting Techniques Can Make People Notice Your Work: Creating nifty charts like the 2 Axis chart, or the PivotChart can be useful in the board room. Learn these techniques. Similarly, using Slicers in Pivot Charts can make dynamic Charts that can wow the audience. They are simple to use.

7. Get Back to Your Selection: Many times you will select or highlight some cells, but then move about the Excel file to find something. Then your are lost. Now you don’t remember where exactly is your selection. Simply click Control + Backspace. It will take you to the highlighted area in a blink!

8. Use Range Names to Refer to Cells: Instead of using G3, C3 or F3, you can give each cell a name. Not only single cells, you can actually define a name for a group of cells too. Then you can refer this group of cells by a particular name.

So instead of saying =G3+C3-F3, you can now calculate Salary as

=Basic_Pay + Allowance – Tax

This is much easier to read, and comprehend. No need to look up where the formulas are coming from, as the names are self evident.

9. Learn Excel Shortcuts: There are hundreds of shortcut combinations in Excel. It is impossible to remember all of them. But you must know a few to really speed up your work. Some of my favorites are:

  • Control N: New workbook
  • Control `: View All Formulas
  • Control Backspace: Jump to selected Cells
  • Alt + F1: Creates a inserts a chart based on the currently selected data as an embedded chart object.
  • Alt + =: Auto sum formula is inserted
  • Ctrl + Shift + Enter: Enter an Array Formula
  • F9: Calculates all the worksheets in all open workbooks

10. Learn to Use Dates Effectively: In Excel, dates are stored as numbers. They can be displayed in a wide variety of Formats. Understand these formats to display them in the way that suits the applications.

Also, you can calculate using dates, for example, you can find the difference between any two dates. You can add a fixed number of days to a date.

Hope you enjoyed these tips, and will use them to improve your productivity in using Excel. Do post a comment below to talk about your favorite tips in Excel.

If you wish, you can get a good book on Excel from the Excel BookStore. These books are usually quite inexpensive, and will give you many ideas to improve too.

Cheers,
Vinai Prakash, PMP, MBA, ITIL, GAP, Six Sigma

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!

P.S.  : You can contact us to conduct a workshop on Excel Tips and Tricks at your office.

And I hope you subscribed to the ExcelChamp Excel Tips Newsletter already. 🙂

Different Grouping For Multiple Pivot Tables Based on Same DataSet

One of the key features of Pivot Tables is to summarize the information quickly. Another great advantage is its ability to look at percentages – percent of total, percent of grand total, percent of row total, running total in etc.

Yet another fantastic feature is the ability to group data in Excel – either by existing columns, or by creating your own custom logic. Data can be grouped by Text Columns or even Date Columns. And the result is instantly summarized. Amazing stuff!

All is good so far… The problem crops up when you have created some grouping, and then decide to build another pivot table to get another view of the same data, while keeping the original pivot table in place.

The second pivot table automatically groups the data based on the first pivot’s grouping. And if you change the grouping on the second pivot, the first pivot table changes too.. Bo hoo hoo 🙁

This can be frustrating and sometimes difficult to troubleshoot or fix.

Why does a Pivot Table share its Grouping with another Pivot Table?

That’s because both the pivot tables are sharing the same pivot cache. To understand better, when Excel creates a pivot table, it makes a copy of the entire source data, and creates a temporary pivot cache in the memory. This duplicated cache is now stored with the Excel file, doubling its size. If your source data was huge, the excel file soon soars in size too.

To save hard drive space and memory, when the second pivot table was created, it used the same cache as the first pivot table. This makes sense, but then since the cache is shared, change in the cache for one pivot table affects the other pivot table too.

The solution is to have a separate pivot cache for the second pivot table.

How to Create a Separate Cache for the Second Pivot Table?

Although this method will use up more memory, it is a good solution, works well, and hardly takes any time to implement.

Select the source data, go to the Formulas tab, and click on Define Name button.

Key in a unique name in the popup. Let’s say you call this DataSet1. This creates a Unique Named Range.

Now click on the Define Name button once more, and create another name for the same data set. Let’s call this DataSet2.

Create Pivot Tables with Unique Data Sets

Now create the first pivot table based on the first Data set (DataSet1). Group on whatever fields you want. Once you are happy with the result, do the same thing for the second pivot table.

Just remember to use the second data set for the second pivot table (DataSet2). Group the data on a different field.

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.

To Get Most out of Excel, Learn the  Pivot Table techniques in our
Pivot Table Masterclass Training
!


You will notice that the grouping of the second table stays, and it does not alter the grouping order of the first pivot table. That’s because we are using different defined names.

Thus, Excel creates two different pivot caches, and even though both refer to the same data set, it is transparent to Excel, and is of no consequence.

Now you can enjoy the benefits of two different views, one with one set of grouping, and another with another set of grouping.

Although this method inflates the size of the file, it is a quick and dirty method, that works well. Try it out… and let me know how it goes…

Cheers,Vinai Prakash,
Founder of ExcelChamp.Net

Additional Resources:
If you found this tip useful, you may want to subscribe to the ExcelChamp Weekly Excel Tips Newsletter.

And check out these cool Excel Tips too…

Cheers – Vinai

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!