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.
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.
Do you know that Microsoft Excel comes with a plethora of new features. It is worth investing the time to learn exciting Excel features, and make the most of the latest and greatest spreadsheet software by Microsoft.
So what is so great about Microsoft Excel? Plenty. Our Master Trainer, Vinai Prakash take you to a tour of the new features in Excel.
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, 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 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 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 features like Vlookup, Macros, Pivot Tables, Pivot Charts, Tables, Advanced Functions and Formulas, Sharing, Collaboration, Removing Duplicates, and much more…
If you’re searching for a more flexible data management system, a database might be just the salvation you’re looking for and Microsoft Access provides an excellent option. With Access you will experience new interface with different look and feel. It has got sleeker look and it has more colors to make it more modern style, which makes Access still relevant in 2021 and beyond.
You can not only save the document which you can access anywhere but at the same time you can collaborate it with other people.
Features of Access 2013
Access 2013 has changed the tabs of ribbons and made it capitalized which was not there before.
Also if you have not worked with SkyDrive before that’s something which is going to be new for you. Want to explore this?
So when you are trying to open any new or existing document you don’t only have a option of choosing it from Recent but also you can also select it from SkyDrive.
After entering your account details it enters into your SkyDrive and then you can browse your database the same way as you browse in windows explorer. Like downloading we can also upload our local database to the SkyDrive.
Access 2013 has moved towards the Cloud and can now produce Web Apps which can be accessed through a browser.
There’s a quantity of Wizard help available in constructing these, so you’re not working from the skretch up when constructing one. Navigation and different views are pre-constructed, as long as the Web App you’re after can be based on one of the database templates provided.
Here are the top features you should explore in Access 2013.
Using Templates in Access 2013
Apps in Access 2013
A Focus on the Web: Office 365, SharePoint 2013, and Access 2013
Using GetPivotData in Excel To Get Pivot Values Instantly
Pivot tables from Microsoft 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. If you haven’t used Pivots, this is a great reason to go for Advanced Excel Training.
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.
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 required 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.
Vinai teaches Advanced Excel Techniques, Dashboard Techniques using Excel, Data Interpretation and Analysis Training courses at Intellisoft. He has trained over 10,000 students in over 18 countries, and regularly conducts Excel Workshops in Singapore, Malaysia, Indonesia, Australia, India, Dubai, Egypt, Zimbabwe, South Africa etc.
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.