How To Use Custom Sort in Microsoft Excel

Excel training in Singapore at Intellisoft

Most of the time an ascending sorting is what we need – letters and numbers listed in the ascending order a to z, 1 to 100 etc. And just in case you need to sort in the reverse order, you have the Z to A sort, also called the Sort in Descending order. Between the two, most people are quite happy.

However, there arises a time when you don’t want either the sort in Ascending order or the Descending order in Excel.

Examples where a Standard Sorting won’t work:
For example, if the departments in your organization are Finance, Marketing, Sales & Engineering. And you want the Sales department to be listed first, followed by Marketing, Engineering, and Finance being the last.

Now how would you sort the departments in this order? Ascending or descending sort is not going to work.

Do not despair however. Here is where the power of Microsoft Excel Custom sort shines.

Another scenario is the Sorting of Months – say you want to sort April, May & June, in this order. Or maybe you want to sort regions by East, West, North & South. This EWNS order also needs a custom sorting in Excel.

Or if you have a completely random order – which defies any kind of sorting. Say you want to list Oranges, then Apples, then Grapes, and finally Bananas. You can go nuts without custom sorting criteria in Excel.

Using Custom Sort in Excel 2003

First, let’s create the custom list in Excel.
Go to Tools, Options, Custom Lists.
You can key in your list and click Add. Or you can import your list from another area of the spreadsheet, where you list the options in the sorted order.

 

 

 

 

 

 

 

Once you have imported the list in the correct order, you can go to Data, Sort, and then click on Options at the bottom of this popup window. Choose your custom sorted list from the list of First Key Sort Order.

Voila! Your list is now sorted in your very own custom order.

 

 

 

 

 

 

 

 

Alternatives to Custom Sort
Of course, if you don’t want to use Custom Sort, there are other alternatives. I have often used a Lookup Table

Fruit                Sorting
Oranges             1
Apples               2
Grapes               3
Bananas            4

I then use the inbuilt Lookup function of Excel called VLOOKUP function and pick the correct value, and then do an Ascending sort. This is a quick cheat trick.

But it would be tough if you did not know how to use the Lookup functions of Excel in the first place. More on this lookup function in another post.

Let me know if this neat trick help you. Till then…

Cheers,
Vinai

Dynamic Dashboard Creation using Excel 2016, 2013 or 2010

Most people take Excel to be a number crunching tool… boring and unattractive.

Actually, Microsoft Excel has been slowly transforming… adding more and more features that make presentation of number much better with every new edition of Excel.

The Excel 2010 version is itself extremely powerful and you can create dynamic dashboards with buttons, check boxes, drop downs, scroll bars, conditional formatting, pivot charts, slicers, and a host of other features.

A dashboard is a simple tool for the management, allowing them to get a glance at the business in just a single screen – without complicated numbers.

See the dashboard created above in the picture. You may not even realize that it was created in Microsoft Excel.

Plus, it is not just a pretty picture. There are things that can change – with  the click of a button, you can change the division, product, year, and the entire set of graphs, numbers, charts will change dynamically. Try that in PowerPoint….

If you would like to learn about these advanced featured of Excel, and how to create such dynamic dashboards using Excel, you can attend our 1 day Excel Dashboards Workshop – it starts at the basics, and covers step by step features required to create such dashboards, on your own.

Plus we provide you with lunch, snacks, tea/coffee breaks, so everything required is available… and you can focus on learning and building the dashboards quickly, and easily.

Intellisoft runs Dashboard MasterClass in Singapore, Malaysia, Indonesia, Hong Kong, Dubai, Qatar, India, and many other countries.

If you would like to join, simply contact us, and we will advice you of the dates, training schedule in the city of your choice.

Contact us to enroll for this practical, hands on training.

We will email you the Course Brochure too, so you can see the topics covered in detail.

Customized Corporate Training is available for this Dashboard MasterClass. So far we have conducted a number of such classes for the staff of large MNC companies, teaching them how to create such reports and dashboards with ease, and they have been highly popular and successful… requiring us to do multiple repeat sessions for other divisions, countries also.

Do learn these simple techniques to create useful, and interactive Dashboards using Excel and beyond.

Cheers,
Intellisoft Team
Singapore

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

Printing Gridlines in Excel

Gridlines in excel


 

 

 

 

It is much easier to type in Excel when you see the grid lines to demark lines & columns.  However if you print your beautiful Excel file, it may not seem easy to read, because the lines that separate the rows and columns are not printed by default.Now there has always been a way to get the grid lines to print, since Excel 2000, and it is still available in Microsoft Excel 2003 in the same place. In  Excel 2007, you have many ways to get to the Print Grid lines option.

Go to Print > Print Preview & Click on Page Setup

You will see a popup box come up. Click on the 4th tab and select Sheet. You should see something like this screen shot below.

As you can see, there is a checkbox which says – Gridlines in the Print section. Select the Checkbox, and then click OK.

You will see tiny gridlines appear on the screen. Now when you print the sheet, you will see gridlines being printed, and it is much easier to view the figures on the printed sheet with gridlines printed.

Hope you find this tiny tip useful…

Cheers,
Vinai

How To Write Excel Formulas

The real power of Microsoft Excel is the usage of Formulas. This is what differentiates it from Paper or Typewritten Reports.

With the use of Formulas, Excel can calculate and update the totals, and complex calculation or computations on the fly.

How Excel identifies Rows and Columns
Excel uses a simple numbering system to differentiate between cells. Each cell or box that you see in an open Excel file has a unique number – pretty much like the pigeon hole letter box that each house has. Each letterbox number is unique, making the life of postmen easier.

In Excel, Rows are numbered from 1 onwards. These Rows appear on the Left of your screen. For Columns, it uses alphabet combination – like A, B, C, and when it comes to Z, it sharts with AA, AB, AC and so on.

So the first cell cross-section of column A and row 1 is called A1, column M and row 5 is called M5. Simple huh?

Using Rows & Columns in a Formula

How does these unique references to each cell make a difference? Well, let’s say you want to add the total of A1 and A2  in the cell A10. All you need to do is to go to A10 cell, and then start to write a formula.  A formula begins with the = sign.

So you are at A10. Key in =A1+A2 and hit the enter key.

That’s it. You see the totals of A1 & A2 show there.

Go ahead. Fire up Excel. You can’t learn Excel by looking at tutorials. You have got to try it out, and practice. Practice makes you perfect.

Exercise:
1. Key in 20 in cell C1, 30 in cell C2, and 40 in cell C3. Then go to cell C4, and key in the formula to calculate the total. If it matches 90, you have got it correct! Congratulations.

Cheers,
Vinai

Looking for Microsoft Advanced Excel Training? You have come to the right place

Contact us at +65 6296-2995 for any questions, or to join our upcoming Advanced Excel Training classes in Multiple Versions.

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

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!