Do You Use These Advanced Features in Microsoft Excel?

Advanced Excel training at Intellisoft
Practical hands-on advanced excel training at Intellisoft
Practical hands-on advanced excel training at Intellisoft

Most people hardly use the huge number of features available in Microsoft Excel. Many are just using Excel as a calculator. This is a gross under use of Excel’s vast potential and feature rich functionality.

Do a quick check, and see if you use these advanced features of Microsoft Excel in your day to day work.

Some of the common things that can be done easily with Excel are:

  1. Finding the Top 10 Customers or Finding the Bottom 10 Performers in the organization
  2. Highlight values that are above or below a certain threshold – like all sales above $25,000 to be highlighted
  3. Sort the values in Ascending, Descending or any customized order – like sorting in order of Manufacturing, Accounts, Sales departments.
  4. Give Names to Range of Cells, and then use them in formulas for easy referencing and decoding
  5. Exploit Pivot Tables to Summarize the data and slice & dice it in any way – finding sales by product groups, or calculating productivity by department
  6. Write Macros to automate routine things that save you a huge amount of time – example creating pivots, charts, tables, and doing complex calculations automatically.
  7. Use advanced filtering conditions, and be able to filter data using multiple different criteria
  8. Create fantastic charts that portray the given business situation perfectly. There are over 50 different types of charts to choose from, and each has its edge, advantages and a reason.
  9. Create management dashboard that are dynamic, and provide a complete snapshot of the key business KPIs in the company – change the chart values at the click of a checkbox or change in a drop-down value
  10. Use Excel’s advanced What-If analysis to do projections for future, forecasting, trend analysis etc. with ease
  11. Use Lookup tables to find any value or corresponding value from a table using advanced functions and formulas

This is just the tip of the iceberg. Microsoft Excel is really extremely powerful. Each version of Microsoft Excel – be it Excel 2007, or Excel 2010 or Excel 2013 adds more and more features to the already powerful dynamite of a package.

At Intellisoft, we teach people how to leverage the maximum power out of Microsoft Excel in short training courses. Some of the popular courses are:

We have a number of Public Classes each month, and we also provided In-House Training to your staff and team at your office, if you have a group of 10+ people, and have a room to hold the training.

So what are you waiting for? If you would like to learn any one or more of such useful features of Microsoft Excel, come for a short Excel Training at Intellisoft.

Go ahead, equip your team with the right skills. Get everyone on board to learn the basic and advanced features of Microsoft Excel, and Be Awesome in Excel!

Email to training@intellisoft.com.sg or call +65-6296-2995 for the next available schedule of Microsoft Excel Training in Singapore.

We are located at Beach Road, in Singapore! Location Map of Intellisoft

Cheers,
Vinai Prakash, PMP, ITIL, Six Sigma, GAP,
Master Trainer

Sparklines Microsoft Excel

In Microsoft Excel, some of the new features are sparklines and slicers, and improvements to PivotTables and other existing features, can help us to discover patterns or trends in the data. To get started with the features of Excel 2010, first we will look at the details of the  Sparklines and slicers features of Excel.

Sparklines

Sparklines are tiny charts that is used to fit in a cell to visually summarize trends beside the data.

Since sparklines show trends occupies less space, they are exclusively useful for dashboards and other places where we need to show a glimpse of the business in an simple practical visual format.

In the image to the left, the sparklines that appear in the Trend column lets us have a quick look of the performance of each department in the month of May.

Slicers

Slicers are visual controls. They let us quickly refine data in a PivotTable in an interactive, automatic manner. If we insert a slicer, we can use buttons to quickly segment and refine the data to display appropriate results.

Not only that, when we apply more than one filter to the PivotTable, we no longer have to open a list to see which filters are enforced to the data. Rather, it is displayed on the screen in the slicer.

We can make slicers relate to the workbook formatting and easily reuse them in other PivotTables & PivotCharts.

excel trainingIf you would like to learn more about these new features of Microsoft Excel 2010 or 2013, or would like to attend the Advanced Microsoft Excel Training, do contact us at Intellisoft Systems.

If you have any further questions or want to join a training on how to use Sparklines, contact Intellisoft for Corporate Training on Excel 2010 or call at +65 6296-2995

Trainer: We have certified trainers who excel in imparting their knowledge and are very patient. Master Trainer Vinai teaches Advanced Excel Techniques, Dashboard Techniques using Excel, Data Interpretation and Analysis Training courses at Intellisoft.

He has trained over 5000 students in over 18 countries, and regularly conducts Excel Workshops in Singapore, Malaysia, Indonesia, Australia, India, Dubai, Egypt, Zimbabwe, South Africa etc.

What are you using Microsoft Excel for?

excel_2013_chart

Most people learn Microsoft Excel by hit and trial. As soon as someone explains, or they figure out how to write a simple formula to add two numbers, they think that they know everything about Microsoft Excel.

Nothing could be far from the truth! There is much more to Microsoft Excel than just adding numbers.

And I have even seen people using a Calculator even when a Microsoft Excel spreadsheet is open.

Right out of the box, Microsoft Excel, whether Excel 2007, Excel 2010 or Excel 2013 can do wonders! Let’s see some examples of what Microsoft Excel can do.

Basic Things that Microsoft Excel is great to use for are:

  • Creating Tables
  • Finding Duplicates
  • Eliminating Duplicates

Advanced Excel Techniques

  • Using Range Names
  • Formulas and Function like Vlookup, Text Functions, Date Functions
  • Pivot Tables
  • Pivot Charts
  • Macros to automate routine tasks
  • Sharing Worksheets and Workbooks
  • Protecting Worksheet, Workbook, individual Cells

Advanced Analysis Techniques and Data Analysis using Microsoft Excel:

Are you using Excel to your advantage, or just using it as a basic calculator?

Cheers,
Vinai Prakash
Intellisoft Systems

Top 3 Features of Microsoft Excel You Must Know

top_excel_features_in_interviews

Microsoft Excel is heavily used in Banking, Sales, Finance, Marketing, Customer Service… you name it, it is used by people at operations level, supervisory level and management level for data entry, data analysis, tracking and reporting data.

No wonder in job interviews, Excel features heavily for such job roles.

The Top 3 features often asked in the Job Interviews are regarding Pivot Tables, VLookup Functions and Macros.

Do you know Pivot Tables in Excel?

Pivot tables are used to summarize multiple data rows in one or multiple sheets, and create a summary report. It is a fantastic tools that makes it much easier to view the data at a high level – by category, by division, by department, by area and by country etc… based on your data.

It is best if you master pivot tables, and its nuances, its options, its hidden features and become an expert at using Pivot tables.

Here are some articles I wrote about using Pivot Tables in Excel, and its advanced options of getting pivot data in summary sheets within Excel.

But you may want to attend the Advanced Excel classroom training, and even avail government grants, SkillsFuture, SDF funding etc. to get subsidized fees.

Do you know how to use the Vlookup Function in Excel?

Vlookup and Hlookup are 2 of the Lookup functions within Excel. They help to lookup prices of parts, employee names etc. from tables where you know the part number, employee number, IC number etc.

It is like looking up the meaning of a word in a dictionary. These are extremely powerful functions, and you must know them well.

Do note that there are couple of variations of the Lookup functions – Exact Match or Range Lookup (Approximate match). You must know what to use, and when to use which option.

Knowledge of VLookup is a  must for most industries using Excel, like the Banking & Finance industry.

Again, at Intellisoft, we cover the Lookup Functions in the Advanced Excel and the Excel Dashboard MasterClass, where you learn how to create Management Reports and Dashboards using Microsoft Excel.

Do you know how to write Macros in Excel?

To save time in doing repeated steps, Microsoft introduced the Visual Basic for Applications programming language. IT is popularly called as VBA Macro programming. With VBA programming, you can extend Excel to create routines that can do the basic, mundane steps, quickly, and correctly, so you can spend more time with the more important stuff.

Excel macros are used in creating specific user forms, creating conditional logic, creating work flow within a n organization.

The end user can simple execute multiple steps without knowing how to do the intermediate steps, simply by clicking a button, which in turn can run a complex macro. It is so simple, and magical to use and execute macros within Excel.

You could use it to generate a profit and loss statement, a balance-sheet, a leave approval form, a cash flow statement, pivots and charts automatically, without doing multiple steps.

Read more about VBA Macro Programming here. And you can attend our 3 day VBA Macro programming training in Singapore.

Conclusion:

These are the most important and most used features of Microsoft Excel. Master these, and you will be very popular in your company, and you will improve your job prospects significantly by learning these 3 most important things in Excel.

Cheers,
Vinai Prakash,
Founder & Principal Trainer, Intellisoft Systems

Free Tips, Tutorials & Training Grants Info

Learn from expert tips, tricks and resources for Excel, PowerPoint, Photoshop, Project Management, IT, Soft Skills & more with our Email Newsletter.
Plus get the latest news on Grants. Join Today!

Found What You Were
Looking For?

Just Tell us...

We're Here To Help You!