One simple way to reduce worksheet crowding is to rotate your column labels so that they read up, down, or vertically.
What are Advanced Excel Skills?
Add them to the Format toolbar, as follows:
Choose Tools + Customize.
Then Click the Commands tab.
Under Categories, choose Format.
Under Commands, find the Vertical Text button and drag it into place on your Formatting toolbar.
Repeat Step 4 to drag the Rotate Text Up, Rotate Text Down, and, if needed Angle Text Upward and Angle Text Downward buttons to the Formatting toolbar
Now, whenever you want to angle or rotate text, just select the cell(s) and click the appropriate button. It will save you space, and make it easier to read and avoid eye strain.
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, first we will look at the details of the Sparklines and slicers features of Excel.
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
Excel Slicers Sample
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.
He has conducted over 700 live workshops, and trained over 5,000 students in over 18 countries, and regularly conducts Excel Workshops in Singapore, Malaysia, Indonesia, Australia, India, Dubai, Egypt, Zimbabwe, South Africa etc.
I bet you know how to sort data in Excel. It is pretty easy. 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, thanks to Microsoft‘s intuitive sorting options.
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
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 in Excel
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.
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:
Finding the Top 10 Customers or Finding the Bottom 10 Performers in the organization
Highlight values that are above or below a certain threshold – like all sales above $25,000 to be highlighted
Sort the values in Ascending, Descending or any customized order – like sorting in order of Manufacturing, Accounts, Sales departments.
Give Names to Range of Cells, and then use them in formulas for easy referencing and decoding
Exploit Pivot Tables to Summarize the data and slice & dice it in any way – finding sales by product groups, or calculating productivity by department
Write Macros to automate routine things that save you a huge amount of time – example creating pivots, charts, tables, and doing complex calculations automatically.
Use advanced filtering conditions, and be able to filter data using multiple different criteria
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.
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
Use Excel’s advanced What-If analysis to do projections for future, forecasting, trend analysis etc. with ease
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.
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-6250-3575 for the next available schedule of Microsoft Excel Training in Singapore.
Chances are that you can only click on the Autosum button to add a few numbers. If you have never written a single formula in Microsoft Excel, fret not. It is not difficult at all. In fact, with our step by step Excel training, you will be able to write Excel formulas in no time.
Advanced Excel training at Intellisoft in Singapore
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.
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.
Excel VLookup Sample Setup
For Columns, it uses alphabet combination – like A, B, C, and when it comes to Z, it starts 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?
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:
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. You are on your way to write Excel Formulas and get better at computing using Microsoft Excel.
Cheers,
Vinai Prakash
founder & principal trainer at Intellisoft Systems Singapore.
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?
Summarize Data Quickly With Microsoft 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.
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.
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.
Excel Macro Sample
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.
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, Python, Power BI, Project Management, IT, Soft Skills & more with our Email Newsletter. Plus get the latest news on Grants. Join Today!
