Conditional Formatting in Microsoft Excel

Microsoft Excel training at Intellisoft

In Microsoft Excel, 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. In the previous post we had a look at the Sparklines and slicers features of Excel 2010 and Pivot table features of excel 2010, so now we will look at the improved pivot table feature of excel 2010.

Improved conditional formatting

Conditional formatting makes it easy to emphasize important cells or ranges of cells, highlight unusual values, and visualize data by using data bars, color scales, and icon sets. Excel 2010 includes further more formatting flexibility.

New icon sets: In Excel, we  can access to more icon sets, including triangles, stars, and boxes. We can also mix and match icons from different sets and more easily hide icons.

For example, we might choose to display icons only for high profit values and remove them for middle and lower values.

More options for data bars: Excel 2010 comes with new formatting options for data bars. You can apply solid fills or borders to the data bar, or set the bar direction from right-to-left instead of left-to-right.

Not only that, data bars for negative values appear on the opposite side of an axis from positive values.

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

If you have any further questions then contact us through email training@intellisoft.com.sg or call at +65 6296-2995!!!

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.

New Features in Excel

Do you know that Microsoft Excel 2013 comes with a plethora of new features. It is worth investing the time to learn these features, and make the most of the latest and greatest spreadsheet software by Microsoft.

So what is so great about Microsoft Excel 2013? Plenty. Our Master Trainer, Vinai Prakash take you to a tour of the new features in Excel 2013.

1. Quick Analysis Tool: The new Quick Analysis tool allows you to convert your data into a chart or a table in just two steps. Preview your data with conditional formatting, spark lines, or charts, and make your choice stick in just one click.

2. Instant Flash Fill: Flash Fill enters the rest of your data in one fell swoop, following the pattern it recognizes in your data.

3. Chart Recommendations: With Chart recommendations, Excel recommends the most suitable charts for your data. Get a quick peek to see how your data looks in the different charts, and then simply pick the one that shows the insights you want to present.

4. New Functions: There’s a whole slew of  new functions in the math and trigonometry, statistical, engineering, date and time, lookup and reference, logical, and text function categories.

5. Smart Pivot Tables: In Excel 2013, When you create a PivotTable, Excel recommends several ways to summarize your data, and shows you a quick preview of the field layouts so you can pick the one that gives you the insights you’re looking for.

In the new Excel Data Model, you’ll be able to navigate to different levels more easily. Use Drill Down into a Pivot Table or Pivot Chart hierarchy to see granular levels of detail, and Drill Up to go to a higher level for “big picture” insights.

6. Improved Collaboration Tools: Working with other people on shared files in real time is a double-edged sword. While it’s useful to do this, you will face problems when two people try to change the same item at the same time. In Excel 2013 you can share and work collaboratively on files with others via SkyDrive using the Excel WebApp, and multiple people can work on the same file at the same time.

7. Standalone Pivot Charts: A PivotChart no longer has to be associated with a PivotTable. A standalone or de-coupled PivotChart lets you experience new ways to navigate to data details by using the new Drill Down, and Drill Up features. It’s also much easier to copy or move a de-coupled PivotChart.

Learn Excel 2013 in 2-3 Short Day Training: With so many new features, it is important that you learn these features, to improve your efficiency, productivity, and make use of these new  features. After all, what is the point of using the latest software if you do not use its latest and greatest features.

Learn to use Excel 2013 features like Vlookup, Macros, Pivot Tables, Pivot Charts, Tables, Advanced Functions and Formulas, Sharing, Collaboration, Removing Duplicates, and much more…

Do contact us at Intellisoft to attend our Basic Excel 2013, or Advanced Excel 2013 training courses at our training centre, or have a Customized Corporate Training on Excel 2013 at your office.

We also provide WSQ Funded courses for Excel 2013, which means that up to 95% of the course fee is funded by the Singapore Government. Terms & Conditions apply, so contact us for more information on training fees, Grants and customized solutions for your company.

Sparklines Microsoft Excel

Practical hands-on basic Excel training

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

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.

Using Pivot Table Data in Summary Sheets

Learn Pivot Tables Easily

Pivot tables are a great boon in Excel. Previously, getting IT departments to write custom reports used to take ages. And even if you could get a report written for your needs, by the time you got it, it was too late, or you wanted to look at information from another angle.

With Pivot tables, you can now create your own reports in no time. And you can slice and dice information pretty easily, with just a few clicks.

However, even though Pivot tables are great, they are not the best tool  for presenting information for the senior management. There may be times when you want to pick up certain information from the pivot table, format it nicely, and present it with other summary figures.

To use the summarized data from the pivot table, but make it more presentable,  you can use an extremely useful function of Excel called the GetPivotData.

Enabling GetPivotData Button
It is very easy to get this function to work. This little gem is hidden right within the Pivot Toolbar. Just right click on the Pivot Toolbar, right at the end, and select customize. Pick Add/Remove Buttons.

Select GetPivotData button. This is a toggle button – click it once and it gets enabled, and another click disables it. You can see a slight change in the icon when it is enabled or disabled.

Once the button is highlighted, you can begin writing your formula. Start with a = sign in a black cell where you want a pivot table value. then point to any cell in the Pivot Table. Its value is captured in your formula.

As long as the data is available and visible in the Pivot table, you can move the data around from rows to columns or page fields, but it will still appear correctly in the presentation area.

Go ahead. Give it a try and make your data presentation summaries more dynamic and presentable. Any questions or comments, do let me know.

Cheers,
Vinai
Editor, Microsoft Excel Tips Blog

Pivot Tables in Microsoft Excel

Learn Pivot Tables Easily

In Microsoft Excel , new features are sparklines and slicers, and improvements to Pivot Tables and other existing features, can help us to discover patterns or trends in the data. In the previous post we had a look at the Sparklines and Slicers features of Excel 2010 so now we will look at the improved pivot table feature of excel.

Improved Pivot Tables

PivotTables are now easier to use and more responsive. Key improvements include:

  • Performance enhancements: In Excel, Multi-threading helps advanced  sorting, data retrieval and filtering in Pivot Tables.
  • Write-back support: In Excel, we can update values in the OLAP PivotTable Values area and then transferred to the Analysis Services cube on the OLAP server. We can use the write-back feature in what-if mode and then roll back the changes when we no longer need them, or we can save the changes. We can use the write-back feature with any OLAP provider that supports the UPDATE CUBE statement.
  • Enhanced filtering: We can use slicers to quickly het the reqiured data in a PivotTable and see which filters are applied without having to open additional menus. In addition, the filter interface includes a handy search box that can help us to find what we need among potentially thousands (or even millions) of items in the PivotTables.
  • Pivot Table labels: We can add labels in a Pivot Table and also replicate them in the Pivot Tables. This will help us to display item captions of nested fields in all rows and columns.
  • PivotChart enhancements: It has made things easy to interact with PivotChart reports. Specifically, it’s easier to get the required data directly in a PivotChart and to reorganize the layout of a PivotChart by adding and deleting fields. Similarly, we can hide all field buttons on the PivotChart report.
  • Show Values As feature: The ‘show values as’ feature includes a number of new, automatic calculations, such as % of Parent Row Total, % of Parent Column Total, % of Parent Total, % Running Total, Rank Smallest to Largest, and Rank Largest to Smallest.

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

If you have any further questions then contact us through email training@intellisoft.com.sg Systems or call at +65 62962995!!!

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.

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

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!