A Beginner’s Guide to Microsoft Excel

Excel Beginners Guide by ExcelChamp Vinai Prakash

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

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!

To Join our Excel Course for Beginners, you can visit our Excel Essentials: From Beginner To Pro Excel Course Page, and join it. 

How To Learn Advanced Excel Fast

How To Learn Advanced Excel Fast

Advanced Excel skills are required in most managerial & analyst roles. Yet most people have no clue of how to learn Advanced Excel fast. They don’t know the key features of Microsoft Excel that can help them save time and money.

It’s about time you pick the Most Important & Useful features of Excel quickly. This post coveres the following topics in detail.

  • What is Advanced Excel,
  • Why should you learn it,
  • How to learn Advanced Excel Fast!

Welcome To This Short Guide to Being an Expert in Advanced Excel Skills

Every organization uses Microsoft Excel in their day to day work. Most employees & managers know Excel to some extent. And they are able to survive the day by doing things in one way or another… often the long and inefficient way.

Learning to use Microsoft Excel well goes beyond the basics. That’s where the Advanced Excel skills come in handy.

Microsoft has bundled in hundreds of useful functions and features, that can do wonders, save a lot of time, and improve your efficiency & productivity.

Each new version of Excel is packed with ever richer functionality, and provides more ways to use Excel to its utmost at any workplace.

Microsoft is striving to add those features that can simplify complex things, and make it easier to do data entry, perform computations, and even analyze data & present the insights into actionable information useful for clients and colleagues or the management in nicely created reports and dashboards.

So how come very few people are familiar with these Advanced Excel Features & Functions?

List of Advanced Excel Features
List of Advanced Excel Features

Partly because Microsoft is a software company, and not so much an education company. They add new Excel formulas, features, shortcuts, buttons, charts types, and options, but Microsoft doesn’t spend the time in educating everyone about these new enhancements in the time they deserve.

Microsoft simply blog about it, updates the documentation, and then wait for you to figure it how somehow. Most greatly useful features languish, forgotten in the documentation, hardly ever used…

Also, partly to blame is our education system, which does not start teaching us the key Excel skills that are essential in the job. Almost every student now works on a laptop or a tablet, using documents & spreadsheets for every report, presentation or assignment they do. But our schools often leave you to figure how to be productive with these basic tools.

Very few schools or colleges have mandatory Excel or Word Training.  It’s no wonder that when a fresh graduate joins the workforce, they often take ages to do simple things, stumbling and faltering along their journey to do even basic things in Excel, let alone the Advanced Excel Techniques that are required to be productive.

Why Should I learn Advanced Excel?

That’s a common question for people using Excel at workplace. If you don’t even know what Excel can do, why will you be interested in learning it. You need to see the features to believe it, and to see your own blind spots.

Why Should I learn Advanced Excel?
Why Should I learn Advanced Excel?

Increased Productivity With Advanced Excel Tips & Tricks

If you care about getting the job done faster, without any errors, then it is in your interest to improve your competence in Excel. You don’t have to learn all the 500 plus functions to master Excel. In fact, you can already be more productive if we can know and use more than 10-15% of the Advanced Excel features and functions we have listed below.

Better Job Prospects With Better Excel Skills

New job prospects & Career switch options also open up for those who can manipulate and juggle data easily in Excel. Many higher end analyst jobs in the financial and accounting, sales, marketing, management & consultancy areas require good analytical & decision making skills.

Better Presentations With Excel Charts, Reports & Dashboards

Plus, With Advanced Excel Charts & Reporting features, you can be a star in the boardroom too. Most client presentations will need some amount of data and analysis to be presented, which can easily be analyzed and tabulated in Microsoft Excel, provided you know how to do it quickly.

So now you know the key reasons why you should learn Advanced Excel, you may be wondering, what are the key features of Excel that can considered as “Advanced“.

What Really Are Advanced Excel Skills?

Knowledge of multiple useful features and functions, plus the ability to use them at short notice is what we can call as Advanced Excel skills. To name a few, you must be able to know and perform the following things in Excel well.

Benefits of Learning Advanced Excel
Benefits of Learning Advanced Excel

FASTER SETUP & DATA ENTRY WITHIN EXCEL

Setup Columns Quickly using Auto fill options. For example, you can fill Months or Quarters easily by filling in just the first value. Similarly, you can generate sequence numbers from any starting point to any ending number.

Do Quicker Data Entry by using Auto complete of repeating values as Excel picks up the filling values using pattern recognition
Generate Sequence Numbers quickly With Auto Fill & FlashFill options

LOADING EXTERNAL FILES & DATA

You must be able to Bring External data into Excel from any kind of source – be it Text files, CSV files, XML, Web Data or Database files. Plus, Excel now makes it easier to bring in data from the Cloud Apps like SalesForce, Zendesk, QuickBooks & over 200 app integrations, from PowerQuery, now built into Excel, from version 2013 onwards.

All is not good just by loading the data. You will have to clean & de-duplicate it. Fill in blanks, handle null or missing values, and then fix the dates to become useable. You’ve often got to convert dates formatted as YYYYMMDD or DDMMYY into something that’s more humane – DD-MMM-YY or MM/DD/YY. Obviously the actual settings will depend on your country, regional settings & preferences. But it is often required. This requires you to use PowerQuery, or use Text functions to extract the date, month or year from strangely formatted dates.

EASIER DATA FORMATTING

After cleaning the data comes the job of making it easier to read and identify the key data points.
Here comes the Data Formatting options. Formatting Data makes it easier to read and present data. With Conditional formatting options, it is easier to highlight data based on any simple or complex condition or criteria. By highlighting data, you can make it easy to the winners and losers & spot issues and errors. Highlight the highest values, lowest values, values between a range, values outside a range, or set up your own rules, based on calculations.

LEVERAGE ON IN-BUILT EXCEL FUNCTIONS

Good knowledge of Advanced Excel Functions is essential to get more mileage out of Excel. Microsoft’s Excel functions are divided into multiple categories:

Function Groups within Excel
Function Groups within Excel

Lookup Functions like VLookup, Index, Match, Offset, Indirect allow you to find anything from within Excel tables and Master data. Pick the employees name based on Code, or find out who secured the highest or lowest sales numbers.

Statistical Functions like Median, Mode, Standard Deviation, Variance allow you to analyze data statistically. You can find out the median, standard deviation or variance, allowing you better insights into the data as to what happened, why it happened, and what is most likely going to happen.

Analysis Functions like Correlation & Regression, Trend Analysis & Forecasting further the statistical functions into forecasting, and allowing you to make better projections, and better decisions based on the happening trends.

Logical functions like If, Sumif, Countif, Iferror allow you do things conditionally – check if a condition is met, add or count based on conditions being met or not met, and even check and handle errors from happening.

Date & Time functions for finding todays date, time, difference between dates, hours, year, month, days, weekdays. This allows you to do time based calculations, buckets of date ranges, and even create ageing analysis based on range values.

Database functions that treat the entire data set as a database, and allow you to aggregate results using Dsum, DAverage, Dmax, Dstdev. This can be useful when working with big data

Text Functions to extract, clean and manipulate data – Left, Right, Mid, Char, Len, Fixed, Trim, TextJoin help in fixing erroneous data, and picking certain batch codes, lot numbers, region or product codes from within serial numbers.

Financial Functions like PV, NPV, PMT, IRR, Accrued Interest, Future Value, Mirr etc. are useful for analyzing the current, present and future value of things. Interest calculation, accruals, monthly installments, interest rates etc. can be easily worked out by using these advanced Financial functions of Excel.

PIVOT TABLES FOR QUICK ANALYSIS

Use the Pivot Table feature of Microsoft Excel, which is the fastest way to get some high level summary from your data set. Pivots allow you to slice and dice the data in numerous ways, and analyze it using different dimensions easily, without writing any formulas or macros. A pivot table is the first thing people turn to when they want some quick analysis or summary on the data.

With the help of Slicers, Timelines, Report Filter Pages, you can look at the same data in multiple ways, multiple dimensions, and in multiple filter flavors.

These are all great ways to analyze information quickly with Excel – a strong reason to learn Advanced Pivot Table techniques of Microsoft Excel.

Use Excel Pivot Tables For Quick Analysis
Use Excel Pivot Tables For Quick Analysis

In fact, knowledge of Pivot tables is often tested in interview questions for jobs requiring a good amount of business analytics. Even in days where most companies world class ERP software with hundreds of canned reports, often raw data is extracted from these ERP packages and combined with external market data, manual forecasts and then analyzed using Pivots.

Competence in Advanced Pivot Table analysis techniques are are must if you want to get into business analytics. Strangely, for the amazing things pivots can do, they are surprisingly easy to master. I often see managers and senior executives surprised at the simplicity, and lament that they missed out on this easy feature for the past several years, relying on junior executives to churn out the reports.

No harm in getting the ground staff to run the reports, but sometimes they do not have the acumen or sensory acuity of understanding the big picture. The juniors often report the obvious, without being able to get that helicopter view of the data.

Pivots are the easy, low hanging fruit that you should begin with, for it brings the biggest bang for the buck. You can easily master it in an afternoon, or in a pivot table masterclass and win an edge with this winning Advanced Excel trick up your sleeve.

CHARTS TO VISUALIZE INFORMATION

Use Excel Pivot Tables For Quick Analysis
Use Excel Pivot Tables For Quick Analysis

Create Charts from raw or summarized data to visualize the information quickly. Multiple columns and thousands of rows make it very difficult to see the big picture and spot a trend.

How To Convert Excel Data into Charts
How To Convert Excel Data into Charts

A visual is worth a thousand words. An Excel chart can depict the past sales of many months or quarters, returned products or problem tickets created/solved each month, and it becomes much easier to spot a trend by looking at a high level chart more than by looking at a sea of rows and columns

There are multiple kinds of charts in Excel that can make boring data look stunning. Choose from Bar & Column charts, Pie & Donuts charts, Waterfall charts or Line Charts. You can also create combination charts showing column and lines at the same time, allowing you to measure 2 different metrics at one time.

Excel charts are easy to master, and there’s a lot to choose from. You can easily format them, add legend, titles, colors, and just about tweak every aspect with a mouse click. Mastering such Excel Charting tips can take you far in the boardroom, with better looking charts & visuals.

MACROS IN EXCEL TO RECORD & AUTOMATE STEPS

Macros are the one Advanced Excel Feature that allows you to extend Microsoft’s products and take them to newer heights. You can create your own functions & automations in Excel to perform multiple steps in a short time, at a single button click.

Macros are heavily used in Banks, financial institution, and in accounts departments of almost each and every company. They are the staple of the data enthusiasts who like to do things just once.

Excel VBA Macros allow you to load new data automatically each month, collate and tabulate multiple sheets & multiple workbooks, and create reports & charts automatically for each new period.

Macros are recorded or written and edited in a special language created by MicrosoftVisual Basic for Applications (VBA) in short.

While learning VBA may take some time, this is the secret weapon that separates the wizards of Excel from the amateurs. Once an Excel macro is written and tested, it can easily be deployed to the masses. Your colleagues and users needn’t know the complexities or the logic of how things are done.

Visualize Data With Excel Dashboards
Visualize Data With Excel Dashboards

For example, you could create your own custom Financial Accounting Software, just by using Excel. Using Forms, you can get the users to key in the sales, expenses, and generate invoices or receipts at a click. And a Cash Flow Statement, a Profit & Loss Statement, or a Balance Sheet could be generated at any time, collating all the data keyed in so far.

This allows the users to get more done with Excel, and everyone doesn’t have to learn the technicalities of generating such reports at any time.

VBA Macro writing and editing skills are considered Advanced, because it requires you to learn the specific way Excel refers objects like workbooks, sheets, rows and columns. VBA is a full blown programming language – allowing you to write loops, conditions, procedures, functions, and tag them to buttons, mouse movements etc.

This one advanced Excel skills can make you indispensable in the whole department. I have known several people who have a clout in the company because of their deep knowledge of the system, and that they have written the backend systems that the company uses in its day to day operations. Such deep knowledge is always in demand.

SHARING & PROTECTION OF DATA

Today Microsoft Excel is improved and enhanced to allow multiple colleagues and friends to work together on the same file. You can track changes of who did what, and you can even protect the information in such a way that only those authorized to see or edit can do so, protecting information from prying eyes.

Automate Excel With VBA Macros
Automate Excel With VBA Macros

With Advanced Data Protection techniques in Microsoft Excel, you can hide sheets, write protect them to make them view only, or allow only certain rows, columns or cells to be editable. This gives a tremendous advantage while working with multiple people and multiple sheets.

With the integration of OneDrive, you can truly collaborate with your team, having multiple edits and track changes as they happen. It is much easier to edit, pick the changes you like, or remove/revoke changes that you do not approve of.

If you haven’t visited the Review menu of Excel, you’d be surprised with the multiple options available under the hood, that allow for Collaboration, Sharing, Editing & Protection of Documents.

SIMPLE, COMPLEX, ADVANCED TEMPLATES

Almost everything you do in Excel has been done before. So if you are making a calendar, or a cash flow report, a monthly report, attendance report, Result of Students, Invoices or Statement of accounts, Expense claims or Employee Leave forms for the team, there is a ready made template to do so.

Not just one, you have hundreds of templates to choose from. These ready made templates are available to any Microsoft Office user to use and save time.

Examples of Excel Templates
Examples of Excel Templates

On top of this, you can create your own company wide or departmental templates, that can be used month after month, quarter after quarter without any changes. Consolidation becomes a breeze if you all use the same template.

Plus, tracking & merging of multiple changes can be done with the hidden Track & Merge option. You can’t find this button in the standard toolbar, and need to enable it separately. A golden gem of a function. Even seasoned pros have been unaware of this super cool advanced excel functionality.

Print Beautiful Reports & Charts With Advanced Excel Techniques of Page Setup

With Advanced Page setup, you can decide what you want printed and what not. You can add headers, footers, page numbers, logos etc. straight out of the box. But Excel goes beyond this into giving you fine control over the rows, columns, and area that will be printed.

You can control whether you want to print grid lines, draft copy or are you printing the final copy. The current date or time can be printed too, along with a lot of meta data – file names, sheet names, page numbers etc. provide you with a fine control. Printing order – collated or by page, and auto fit to handle orphan printing of some columns can be a real paper saver.

Trees will love you for learning and using the page setup options within Excel well.

ADVANCED SORTING & FILTERING

Advanced Sorting in Excel With Multiple Levels

Almost everyone figures out how to sort data on any column – in either ascending or descending order. But Excel allows you to perform custom formatting – based on your values, and your defined order.
Sorting for multiple, unlimited levels is a boon too. This breaks the limited 3 level sorting of previous versions, allowing you to sort as many levels as you please.

Filters have improved much in the past 10 years. Now you can easily filter the Top 10 or Bottom 10 values, filter by color, filter by values, and even filter by specific text or dates. There filter feature helps you to actually define the period of data or values that you want to focus on, and eliminate the rest. Master this simple feature, and get to your important data points quickly.

Filtering the values to focus on at any given time removes clutter and makes it easy to visualize information in Excel.

FORMATTING DATA INTO TABLES – A SUPER SIMPLE WAY TO ADD MAMMOTH FUNCTIONALITY, For Free!

Microsoft added the functionality to treat data as a table in Excel 2007. But the name they gave to the button that begins this functionality is a showstopper.

When you look at “Format As Table”, all you’ll see is multiple coloured data tables. And many an Excel enthusiasts pull away, thinking its just colors… They fail to learn the mammoth hidden functionality of Excel beneath this mis-labeled button.

But once you go over this hump, you are on your way to explore gold with Formatted Tables.

Since 2007, Excel Tables have come a long way. Now they have smart range names, auto fill and auto spill ranges, and use range names in calculations. There are several magic cells in Excel Tables, that can perform additional tasks too.

Becoming adept at using Table features of Excel will speed up your analysis.

Further, tables can be filtered, sorted, sliced. For date based data, you can add a Timeline, which is a slicer based of dates, but much better.

There you have it… These are just some of the advanced features of Excel. Learning about additional things like Custom Formatting, Range Naming, Working With multiple Worksheets, or combining data from multiple workbooks, consolidation, What-if analysis, Scenario Manager, Data Tables, Data Validation etc. will take your Advanced Excel knowledge to a much higher level. There is simply too much functionality to talk about in one article.

Suffice is to say that if you want to get done more, cheaper & faster, then learn some of these Advanced Excel Features, pronto!

How long does it take to learn excel?

There is no simple answer when you are beginning to learn a new skills. To learn Advanced Excel tricks is going to be the same too. It also depends on your interest, commitment, and the amount of time you are wiling to spend in learning it.

I would say that learning Advanced Excel tips and tricks is more of a journey.

You learn some concept, begin to apply it, and then learn some more. While learning, you will come across new concepts, see new problems, and seek newer ways to handle these challenges. This step by step approach will open your eyes, develop a keener sense of Excel capability, and develop your Excel muscle step by step, day by day!

I have been a student of Microsoft Excel for the past 30 years. And still I find new things, and new ways of doing the same things. It has been a fun and exciting journey and I love challenges in Excel.

Every once in a while, someone will send me a long and complex looking formula, and dissecting it, understanding it, and learning from it makes us all better. Helping others with their Excel has been one good way that has helped me grow my Excel competence.

Can you teach yourself Excel?

Yes, of course you can teach yourself Excel. And you can even learn Excel at home. All the same, I would recommend a step by step approach in self-learning of Advanced Excel . Based on your interest, it is safe to divide Excel Training into a few sections.

First begin with understanding Range Names, Conditional Formatting, Tables and Pivot Tables.

Then pick up more complex Logical & Lookup Functions to delve deeper.

After this, you can then focus on Financial or Statistical or Date Functions based on interest or usage within your organization.

No point in learning Excel for the sake of learning. You must apply it first. So find a challenging situation within your company or department, and seek to build a solution to fix it is a good way to get started in building your Excel muscles.

It is safe to say that if you begin learning Excel techniques by using this method, in 2-3 months you will see a big improvement in your understanding of Excel.

And in 6 months time you can be at a pretty advanced level in your Excel usage and your added competence will give you more confidence within your organization.

How Can I Learn Advanced Excel Faster?

If you find this route of self-learning difficult or too long, it may be better to develop and learn advanced excel skills in a more systematic and methodical manner.

I would recommend going for a formal training on Microsoft Excel. Based on your level, and interest, you can choose an Advanced Excel Training in your city or suburb. This is the best way to learn advanced Excel.

Most Advanced Excel courses are 2-3 days long, depending on their coverage.

Make sure you join a workshop where lots of exercises and hands-on is provided, and not just a demo of Excel functionality.

It’s because we all learn better by doing it ourselves, rather than just watching someone else do it.

Plus, doing the exercises yourself will expose you to the common pitfalls and mistakes, which can then be rectified with the trainer/facilitator, and with worked examples and samples, you will gain a better understanding of the topics.

Since 2003, ExcelChamp & Intellisoft Systems has been providing short courses on Excel at all levels:-

We also have Excel training for creating management charts and reports – called the Excel Dashboard MasterClass. For those looking to automate Excel, you may choose to enroll in the 3 Day Practical, hands-on, VBA Macro Programming workshop.

What is Advanced Excel Training?

A short Excel training of 2-3 days will cover the key concepts. In such a formal Excel training program, the notes, handouts, exercises & sample examples are readily available for you to begin using immediately.

I personally find learning anything in a short course to be more beneficial. It covers the concepts quickly, and then I can focus on the details based on my interest areas.

Plus the best thing for a formal training is that we have a trainer or facilitator available to ask questions along the way.

Learning in a sheltered environment is better as newbies often stop when they stumble upon initial concepts and often give up completely.

What are the Topics in Advanced Excel Training?

Make sure your chosen Excel training at least covers the most important topics, at the very least. Our 2 day Advanced Excel course in Singapore covers all these, and much more, with practical examples and exercises.

  • Absolute & Relative Referencing
  • Using Range Names
  • Advanced Formulas & Functions
  • Advanced Charting Techniques
  • Using Tables
  • Using Pivot Tables & Pivot Charts
  • Sharing & Protection of Data/sheets
  • Consolidating Data From Multiple Sheets/Books
  • Recording and Running Simple Macros

This much can be covered in a 2 full day training if you can attend such a short course. And it can be an eye opener to the rich functionality of Excel.

What is the Best Excel Training Course?

If you are looking for the Best Excel training, look at some key things to consider, like time, speed, convenience & availability. If you can attend classroom training, I’d absolutely recommend it.

But if you are not able to find one in your town, you can opt for online training for Analyzing Business Data by Mastering Pivot Tables to get started first. There are plenty of Online Trainings for Microsoft Excel available. You can also checkout YouTube videos on Excel.

At ExcelChamp.Net, we provide both Classroom and e-learning via Zoom classes for Advanced Excel. You can choose from several dates available. These are extremely popular, and we have over 20+ years of running Excel classes.

All of our trainers come with years of industry experience. They have a passion for training and sharing their tips and tricks of Excel with you. You’d absolutely love our best advanced excel training course.

How Do I Get Excel Certified?

Most Advanced Excel courses will come with a certificate of Attendance. This is sufficient for most people, for real competence in Excel is more important that a certificate. Intellisoft offers such certificate of attendance for all of its 2 day Excel courses & workshops in Singapore.

To boost your resume & build your LinkedIn profile, a well recognized  official certification in Excel is required!

There are 2 major certifications you can choose from

Microsoft Certified Professional (MCP) in several Microsoft technologies. For Excel, you can go for the Microsoft Office Specialist (MOS) certification. There are 2 levels – Associate and an Expert level. Better to go for the MOS Excel Associate level first, which is an easier Excel Exam. Then opt for the Expert MOS certificate in Excel. Beware that Microsoft certifications are pretty expensive.

Another option is to go for the extremely popular certification from the International Computer Driving License (ICDL Foundation). In Asia, this certificate is available at the Foundation and Advanced levels.

At Intellisoft Training, we are the official partners with Microsoft, and ICDL Asia, and are authorized to administer both the certifications in Excel. You can choose the Microsoft one, or the ICDL one, based on your preference.

The ICDL Certification is widely recognized by the Singapore government ministries. It is a tad cheaper in terms of the exam assessment fee too.

Plus, the Singapore government subsidizes the Advanced Excel Training Fee & Certification fee, allowing permanent residents and Singapore citizens to get certified in Advanced Excel skills. It is considered an Essential skill for office use, and is considered a must have for all office executives, analysts & managers. Do contact us for more information on this.

Next Steps: For Enhancing Your Spreadsheet Skills With Advanced Excel Training

With so much demand for Advanced Excel skills, so rich & useful functionality in Excel, and an easy path to victory with Excel, what are you waiting for. Grab the next chance to explore Excel in greater depths. Enroll in a classroom training, an e-learning training, or attend a Zoom class. Whatever it takes, just get started, right away.

Excel is the secret Swiss Army knife in your data analysis toolbox.

Just imagine, how far you can go with a proper, formal Advanced Excel Training! The opportunities are limitless, and so is your future!

Cheers,
Vinai Prakash
Founder & Master Trainer of ExcelChamp.Net

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. 🙂

Additional Resources for Learning Excel & Become A Pro

  1. Top 12 Most Important Excel Functions You Absolutely Must Know
  2. Excel Beginners Guide
  3. Excel Essentials: From Beginner To Pro in Just 6 Weeks