What’s New With Excel 2016?

Untitled Document

Every business relies on controlling data—from organizing to analyzing. It is especially critical now more than ever as changes occur at a rapid pace, driving the need for more capabilities, command over every process, and flexibility. Add the increasing volume in data, and companies that fail to take full control over it could experience unfavorable results.

Thankfully, resources are on hand to help companies—big and small—organize and analyze data relevant to the business. One such resource is the new and improved Excel 2016.

New and Enhanced Charts In Excel 2016

Data visualization is key to presenting financial as well as hierarchical information. Most people process images better than text. The 2016 version now comes with six new or enhanced charts. The addition of more graphic charts allows businesses and anyone receiving the data to digest the information better – faster.

Some of these visually appealing charts include the Waterfall, Histogram, Pareto (sorted Histogram), Sunburst, Box & Whisker, and Treemap. Source.

sample excel 2016 waterfall chart

Waterfall Chart – Excel 2016

 

Creating a histogram chart is much simpler in Excel 2016. Instead of using Analysis Tools, Histogram charts are now found on the Insert tab and come installed with Excel 2016.

Excel 2016 Histogram Charts

Histogram Chart – Excel 2016

Sample Pareto Charts in Excel 2016

Pareto Charts – Excel 2016

 

Sunburst Chart Sample - Excel 2016

Sunburst Chart – Excel 2016

 

Sample Box and Whiskers Chart - Excel 2016

Box and Whiskers Chart – Excel 2016

Source

sample treemap chart Excel 2016

Excel 2016 – Treemap Chart (Source)

 

PowerPivot Enhancements in Excel 2016

Speaking of faster, PowerPivot in Excel 2016 could also enable speedier training for workers who have not used the feature before.

The 2013 of Power Pivot didn’t come with Measures icons; you couldn’t search in the field list, and you couldn’t right click and edit Measures in the field list. These were in the 2010 version and somehow left out in the 2013. Also in the 2013 version, Measures was renamed to Calculated Fields. They all return for the 2016 version, with Calculated Fields reverting to Measures.

Other enhancements for the Power Pivot include:

  • Automatic Time Grouping
  • Multiple Usability Improvements
  • Automatic Relationship Detection
  • Smart Rename

 

One-Click Forecasting

Another key improvement that will make working with this Excel version easier is the one-click forecasting. Users can now create forecast charts using historical data and predict future trends. The innovative feature uses the standard Exponential Smoothing (ETS) algorithm that generates reliable forecasting data. Previous versions used linear forecasting.

Calendar Insights in Excel 2016

Business owners can also take advantage of the Calendar Insights, which detail how decision makers spend most of their time (e.g., frequency of meetings, persons usually met, etc.). The new feature also identifies ways to maximize work hours.

Touch Enhanced Excel Slicers

In keeping with mobile technology, the latest version of Excel slicer now also works with a touch device. Previous versions allowed users to only select single items.

Report Sharing

Excel reports can be shared with groups or clients using the Publish to Power BI feature, which requires an Excel online support. Sharing on SharePoint and OneDrive for Business is also easily done.

 

Insights by Bing

Right-click any cell in Excel and select Insights by Bing. Excel will search for the cell value in Bing and return the results to a research panel docked to the right side of the Excel screen.

Tell Me What You Want to Do

The “Tell Me What You Want to Do” search box, which would help you identify the commands you need to accomplish a task, sounds like a good idea, but it doesn’t recognize most phrases. If you can’t remember that the Filter icon is on the Data tab, it could be helpful. But if you don’t remember the feature is called Filter, it won’t help.

Bye-Bye to Slot Machine Calculations

Another annoyance in Excel 2013 was that any numbers that changed as the result of a calculation would appear to roll down into the cell, as if you were looking at a slot machine. This feature was so unpopular in Excel 2013 that it has been removed.

Excel 2016 Training in Atlanta, GA

 

About the Author

Jackie Kiadii and her team of subject matter expert (SME) trainers provide Microsoft software training that delivers measurable results – increasing productivity, reducing inefficiency, and improving the bottom line. Topics include: Project, Access, Excel, Excel VBA, PowerPoint. Upcoming Excel courses are on our calendar, click here.

Need onsite group training? Call 770.498.7333 or email us. Our courses can be customized to your needs. Jackie has provided software training for employees of The Coca-Cola Company, Emory University, Novelis, and more. This training is available for groups of 4 or more students.

Atlanta Computer Training on Facebook Atlanta Computer Training - LinkedIn Software Tips on Twitter

Click to Grab Your Excel Ebook – Free!

Get the free Excel ebook

12 Tips to Empower Excel Users

 

MS Project 2013 Templates – How to Find (and Set) the Default Location

Untitled Document

In MS Project 2013 Professional, finding the location of your templates is not a straightforward process. Once you find out where MS Project stores templates on your hard drive, you can adjust your settings so that any templates you create are also saved to the same location.

MS Project 2013 – Finding Template Location

Before following these steps, when I click the File tab and New, I don’t see any option to select templates stored on my hard drive.

 

project 2013 file new screen before default template location set

 

Do a search for *.mpt files. (Mpt is the file extension for Microsoft Project templates.)

Look for the Global file, which will probably be in a Folder named 1033.

project 2013 global template location

Right-click the file and select Properties. The following dialog box will pop up.

Right-click next to Location, and select Select All.

project 2013 global properties display screen
Copy the location by clicking CTRL C.

Hit the OK button.

MS Project 2013 – Set Default Personal Templates Location

 

Open MS Project (if necessary), and launch a blank file.

Click the File tab.

Click Options, then Save.

In the Save templates section, click next to Default personal templates location section.

project 2013 file options save default personal templates location

Hit  CTRL V to paste the location.

Click OK at the box at the bottom of the dialog box.

Once you’ve set the default template location, you’ll see two tabs when you click the File tab, and click New: Featured and Personal.

(Instead of Featured, you might see Enterprise).

project 2013 file new after default template location set

Now you can access any personal templates you’ve created by clicking the Personal tab.

Free! – The Best MS Project Keyboard Shortcuts

Contact us for your free list of handy MS Project keyboard shortcuts.

Want More? MS Project Training in Atlanta, GA

MS Project 2007, 2010, 2013 Training - Atlanta & OnlineThe next MS Project class is happening soon. 2 days of MS Project  training by expert instructors in a small-class setting so that you get the attention you require. Practical, undocumented tips & tricks.Click here for registration and details

.Prefer group training at your site? Call 770.498.7333. Our courses are affordable and customized to your needs. Available for groups of 4 or more students.

 

About the Author of this  MS Project Tutorial.

Since 2001, Jackie Kiadii has taught employees of some of the largest employers in the Southeastern United States how to use MS Project. For more information, visit ProjectTrainingAtlanta.com.

Atlanta Computer Training on Facebook Atlanta Computer Training - LinkedIn Software Tips on Twitter

 

 

Excel VBA, Access, Project Training – Feb 2016

Untitled Document

pcc-banner-linkedinExcel VBA, Access, Project Training – Atlanta, GA

Schedule of February 2016 Courses

The only thing more expensive than education – is ignorance. Ben Franklin

To download our Computer Training Schedule in PDF format, please click here.

Computer Training Atlanta - Schedule of Classes

Want us to bring training to you?

We can provide on-site training for groups of as few as 4 people. Our on-site courses are a great value. Call 770.498.7333 or email us for information.

Since 2001, we’ve provided training for employers large & small, in the public and private sectors.

Microsoft Training Atlanta by Subject Matter Experts

What Students Say:

Jackie helped us develop our personal Access database for internal use. This custom training was invaluable to the success of our new initiatives.

Great presenter. Extremely engaging and knowledgeable of the subject matter. I personally enjoyed hearing real world experience w/ the product and the challenges faced.

Our office experienced numerous problems and downtime when we transitioned to Outlook 2010. In a few hours, Jackie was able to bring us up to speed – her training helped us save countless hours in wasted time.

 

Computer Training Atlanta

Schedule of February 2016 Courses

Access 2013 Application Development & Automation (VBA)
Access 2013 Training - Atlanta, GADate: Mon Feb 01 2016 – Thu Feb 04 2016
Time: 9:00 am – 5:00 pm
Place: .1100 Peachtree Street Suite 200 Atlanta, GA 30309
For Info: http://www.probiztechnology.com/access2013applicationdevelopmenttrainingatlanta.htm
Please bring your laptop (or let us know if we need to provide one).

Excel 2010 Pivot Tables Training – Atlanta
Excel 2010 Training - Atlanta, GADate: Fri Feb 05 2016 – Fri Feb 05 2016
Time: 9:00 am – 1:30 pm
Place: .1100 Peachtree Street Suite 200 Atlanta, GA 30309
For Info: http://www.probiztechnology.com/c_excel-pivot-tables.htm
Please bring your laptop (or let us know if we need to provide one).

Excel 2013 Comprehensive Course – MCAS Certification Prep
Excel 2013 Training by Subject Matter Experts - Atlanta, GADate: Mon Feb 08 2016 – Wed Feb 10 2016
Time: 9:00 am – 5:00 pm
Place: .1100 Peachtree Street Suite 200 Atlanta, GA 30309
For Info: http://www.probiztechnology.com/excel2013trainingatlanta.htm
Please bring your laptop (or let us know if we need to provide one).

Excel Macros & VBA – 2 Days (Visual Basic for Applications)
Excel VBA Training - Atlanta, GADate: Thu Feb 11 2016 – Fri Feb 12 2016
Time: 9:00 am- 5:00 pm
Place: .1100 Peachtree Street Suite 200 Atlanta, GA 30309
For Info: http://www.probiztechnology.com/excel-2010-vba-macros-training.htm
Please bring your laptop (or let us know if we need to provide one).

Excel 2013 Pivot Tables Training – Atlanta
Excel 2013 Training by Subject Matter Experts - Atlanta, GADate: Mon Feb 15 2016 – Mon Feb 15 2016
Time: 9:00 am – 1:30 pm
Place: .1100 Peachtree Street Suite 200 Atlanta, GA 30309
For Info: http://www.probiztechnology.com/c_excel-pivot-tables.htm
Please bring your laptop (or let us know if we need to provide one).

Access 2013 Comprehensive Training – MCAS Certification Prep
Access 2013 Training - Atlanta, GADate: Tue Feb 23 2016 – Fri Feb 26 2016
Time: 9:00 am – 5:00 pm
Place: .1100 Peachtree Street Suite 200 Atlanta, GA 30309
For Info: http://www.probiztechnology.com/access2013trainingatlanta.htm
Please bring your laptop (or let us know if we need to provide one).

Access Database 2010 Basics (Atlanta, GA) In-Depth, 2-Day Class
Access 2010 Training - Atlanta, GADate: Tue Feb 16 2016 – Wed Feb 17 2016
Time: 9:00 am – 5:00 pm
Place: 3355 Lenox Road, 6th Floor, Atlanta, GA 30326
For Info: http://www.probiztechnology.com/access-essentials-2daytraining.htm
Please bring your laptop (or let us know if we need to provide one).

Access Database 2010 Intermediate (Atlanta, GA)
Access 2010 Training - Atlanta, GADate: Thu Feb 18 2016 – Thu Feb 18 2016
Time: 9:00 am – 5:00 pm
Place: .3355 Lenox Road, 6th Floor, Atlanta, GA 30326
For Info: http://www.probiztechnology.com/access-int-trainingatlanta.htm
Please bring your laptop (or let us know if we need to provide one).

Access 2010 Advanced (Atlanta, GA) 1 Day Training
Access 2010 Training - Atlanta, GADate: Fri Feb 19 2016 – Fri Feb 19 2016
Time: 9:00 am – 5:00 pm
Place: 3355 Lenox Road, 6th Floor, Atlanta, GA 30326
For Info: http://www.probiztechnology.com/accesstrainingatlanta-2010advanced.htm
Please bring your laptop (or let us know if we need to provide one).

Excel 2010 Comprehensive Course – MCAS Certification Prep
Excel 2010 Training - Atlanta, GADate: Mon Feb 22 2016 – Wed Feb 24 2016
Time: 9:00 am – 5:00 pm
Place: .1100 Peachtree Street Suite 200 Atlanta, GA 30309
For Info: http://www.probiztechnology.com/excel2010trainingatlanta.htm
Please bring your laptop (or let us know if we need to provide one).

Excel 2010 PowerPivot Training
Excel 2010 Training - Atlanta, GADate: Thu Feb 25 2016 – Thu Feb 25 2016
Time: 9:00 am – 5:00 pm
Place: .1100 Peachtree Street Suite 200 Atlanta, GA 30309
For Info: http://www.probiztechnology.com/power-pivot-training-atlanta.htm
Please bring your laptop (or let us know if we need to provide one).

MS Project 2010 Basic Training – Atlanta
MS Project 2010 Training - Atlanta, GADate: Thu Feb 25 2016 – Thu Feb 25 2016
Time: 9:00 am – 5:00 pm
Place: .1100 Peachtree Street Suite 200 Atlanta, GA 30309
For Info: http://www.probiztechnology.com/msproject-basic-atlanta-v2010.htm
Please bring your laptop (or let us know if we need to provide one).

Excel 2013 PowerPivot Training
Excel 2013 Training by Subject Matter Experts - Atlanta, GADate: Fri Feb 26 2016 – Fri Feb 26 2016
Time: 9:00 am – 5:00 pm
Place: .1100 Peachtree Street Suite 200 Atlanta, GA 30309
For Info: http://www.probiztechnology.com/power-pivot-training-atlanta.htm
Please bring your laptop (or let us know if we need to provide one).

MS Project 2010 Advanced Training – Atlanta
MS Project 2010 Training - Atlanta, GADate: Fri Feb 26 2016 – Fri Feb 26 2016
Time: 9:00 am – 5:00 pm
Place: .1100 Peachtree Street Suite 200 Atlanta, GA 30309
For Info: http://www.probiztechnology.com/msproject-advanced-atlanta-v2010.htm
Please bring your laptop (or let us know if we need to provide one).

MS Project 2013 Training – Atlanta
project training atlanta gaDate: Mon Feb 29 2016 – Tue Mar 01 2016
Time: 9:00 am – 5:00 pm
Place: .1100 Peachtree Street Suite 200 Atlanta, GA 30309
For Info: http://www.probiztechnology.com/project-training-atlanta-v2013.htm
Please bring your laptop (or let us know if we need to provide one).
7 Reasons to Attend Our Computer Training in Atlanta, GA:

  1. An investment in yourself yields the best return. In addition to being a potential tax-deductible expense, the investment you make in training yields returns in making you more productive and competitive.
  2. Passion – We go the extra mile. We choose instructors who are passionately committed to each student’s success, and we take the time to provide follow-up coaching (often at no extra cost) to make sure you are equipped to use the software. As one student says: Even after the class was over, Jackie has been an invaluable resource by showing her continuous quest to ensure that Microsoft Access is demystified.
  3. Small Class size: This course is limited to 8 students, providing you the opportunity to get 1-on-1 assistance.
  4. Expertise. We only hire subject matter experts (SME) Since they know the subject matter inside and out, our instructors are able to adapt the training “on the fly” to meet students’ needs. To request instructor credentials, please click here.
  5. Affordable – a great value..
  6. The MS Office training manuals are approved as preparation materials for  the Microsoft Certified Application Specialist exams.
  7. Flexibility. Can’t make it to our location? No worries. Our courses are now conveniently offered online.  This means you can attend, no matter where you are, as long as you have high speed Internet access.

To download our Computer Training Schedule in PDF format, please click here.

Computer Training Atlanta - Schedule of Classes

 

Excel File Corrupted? Here’s 1 Way to Recover Data

Untitled Document

Do you think your Excel workbook is corrupted? Are you having trouble opening or saving it? Recovering corrupted Excel files can be a challenging process.

If you are able to open the workbook, this method may save you time and headaches. I encourage you to take these steps sooner rather than later in order to have the best chance of recovering your data, formulas and functions.

Step 1 – Select Worksheet(s)

Right-click the sheet’s tab, then click Select All Sheets.

Excel 2013 Select All Sheets

 

Step 2 – Show all Formulas

To display all formulas, click the Formula tab on the ribbon. In the Formula Auditing group, select Show Formulas. (Shortcut: CTRL ~)

Excel 2013 Show Formulas

Step 3 – Export the Worksheet(s)

The next step is to export each worksheet  to a text file.

Excel 2010 Instructions

Select a worksheet and click the File tab, then Save As.  Next to the Save as type select Text (tab delimited). Click the Save button.

excel-save-file-as

When the pop-up box asks if you want to save the active sheet, click the OK button. Another pop-up box will ask if our want to keep the workbook in this format. Click the Yes button.

Excel 2013 Instructions

There are at least two ways to export a worksheet to a text file in Excel 2013.

  1. Select the File tab, then Export. Select Change File Type. Under Other File Types, Select Text (Tab delimited). Then click the Save As button.excel2013-file-export-change-file-typeexcel2013-file-export-text
  2. Select the File tab, then Save As. Click Computer, then Browse. Under Other File Types, Select Text (Tab delimited). Then click the Save As button.excel2013-file-save-as

When the pop-up box asks if you want to save the active sheet, click the OK button. Another pop-up box will ask if you want to keep the workbook in this format. Click the Yes button.

Shortcut (all Excel versions):

F12, then select Text (Tab delimited). (If you have a newer keyboard, you might have to hold down the FN key while hitting F12)

When the pop-up box asks if you want to save the active sheet, click the OK button. Another pop-up box will ask if our want to keep the workbook in this format. Click the Yes button.

Remember: you will have to do these steps for each worksheet.

Step 3 – Create a New Workbook based on the text file.

Close the workbook you suspect is corrupted.

Create a blank workbook (Shortcut: CTRL N).

Click the File Tab, then select Open (Shortcut: CTRL O).

In the dialog box, change the file type to Text Files.
excel-open-text-files

Select your text file, and click the Open button.

This launches the Text Import Wizard.

In step 1, select Delimited, then click Next.

Excel Text Import Wizard - Step 1

In step 2 of the import wizard, make sure a checkmark is next to Tab, and click Next.

Excel Text Import Wizard - Step 2

In step 3 of the import wizard, you can choose the format of each column (this step is usually unnecessary). When you are done, click Finish.

Your text file will be imported into Excel with all of your data, formulas and functions restored.

You can save the text file as a workbook and continue opening the other text files you created from exported worksheets in Step 2.

Once you’ve imported all of the data, save  your new Excel workbook.

Do you have an Excel question?

Comment below and it just might make it to a blog post!

About the Author

Jackie Kiadii and her team of subject matter expert (SME) trainers provide Microsoft software training that delivers measurable results – increasing productivity, reducing inefficiency, and improving the bottom line. Topics include: Project, Access, Excel, Excel VBA, PowerPoint. Upcoming Excel courses are on our calendar, click here.

Need onsite group training? Call 770.498.7333 or email us. Our courses can be customized to your needs. Jackie has provided software training for employees of The Coca-Cola Company, Emory University, Novelis, and more. This training is available for groups of 4 or more students.

Atlanta Computer Training on Facebook Atlanta Computer Training - LinkedIn Software Tips on Twitter

Click to Grab Your Excel Ebook – Free!

Get the free Excel ebook

12 Tips to Empower Excel Users

 

PowerPoint Picture Album – Quickly Create a Slide Deck from Pictures

Untitled Document

 

Have you ever needed to create a PowerPoint slide deck that includes a lot of pictures? Do you know that you can bypass the tedious steps of inserting or copying and pasting each picture? PowerPoint’s Photo Album feature makes creating a slide show from pictures quick and easy.

How to Use PowerPoint’s Photo Album to Create a Slide Deck

Make sure all of the pictures are in the same folder.

Launch PowerPoint (if necessary).

How to Add Pictures in PowerPoint’s Photo Album

Click the Insert tab on the ribbon. In the Images group, click Photo Album.

Click New Photo Album to launch the Photo Album dialog box (shown below).

Click the File/Disk button.

powerpoint new photo album

Select the folder that contains your pictures.

Select your pictures. To select all of the pictures, click the first one, hold down the SHIFT key, and click the last one. To cherry-pick pictures, hold the CTRL key while selecting.

Click the Insert button to return to the Photo Album dialog box.

How to Add Caption Placeholders in PowerPoint’s Photo Album

To put a caption beneath each picture, which allows you to create a descriptive phrase, you must change the Picture layout from the default (Fit to Slide) to one of the other options.

powerpoint changing picture layout

Once you do this, you can place a check-mark next to Captions below ALL pictures under Picture Options. 

powerpoint photo album captions

 

How to Rearrange Pictures in PowerPoint’s Photo Album

You may also rearrange the pictures. To move an item up or down on the list, put a check mark next to it and click the up and down arrows to rearrange it. You may also delete a picture by clicking the remove button.

powerpoint arranging deleting pictures

How to Change Picture Shapes in PowerPoint’s Photo Album

 

You can also change the shape of the picture frame from the default (Rectangle) to a number of other options.

powerpoint picture album frame shape

 

Once you are done, click the Create button.

You can now edit your slide deck.

About the Author – Atlanta-Based PowerPoint Trainer Jackie Kiadii

Jackie Kiadii is an Information Technology professional with a degree in Computer Information Systems. She has provided PowerPoint training for business professionals since 2001. Need more help with PowerPoint? In addition to classes in versions 2007, 2010 and 2013, we now offer Customized PowerPoint Training for Sales Professionals.

Need onsite group training? Call 770.498.7333 or email us. Our courses can be customized to your needs. Jackie has provided software training for employees of The Coca-Cola Company, Emory University, Novelis, and more. This training is available for groups of 4 or more students.

Atlanta Computer Training on Facebook Atlanta Computer Training - LinkedIn Software Tips on Twitter

 

 

MS Project Custom Fields: Creating Drop Down Lists

Untitled Document

by Jackie Kiadii, Microsoft Project Trainer

Microsoft Project comes with hundreds of built in fields.  Did you know it is possible to create your own custom fields? There are a number of types of custom fields you can create, including fields based on formulas, as well as fields that show icons instead of values. (The icon fields are similar to conditional formatting icon sets in Excel).

In this blog post, you will learn how to create a text field that contains a drop down list.

What is a practical use for this skill? You can populate the list with a list of department, Project Managers, or any other data that’s specific to your organization.

So let’s get started.

Project – Creating a Custom Field as a Drop-Down List

Click the Format tab on the ribbon, and then click Custom Fields (in the Columns group). 

 

project-custom-fields

 

This will open the Custom Field Dialog box, which is shown below.

 

project-custom-fields-dialog-box

In the Field section, make sure Task is selected on the left hand side, and Text  is selected on the right.

Make sure Text1 is highlighted.

Click the Rename button.

Type Manager and click OK.

project-rename-custom-text-field

This will take you back to the Custom Fields dialog box. Notice Text1 is now renamed Manager (Text1).

project-custom-text-field-renamed

Make sure Manager (Text1) is selected.

In the Custom attributes section, click the Lookup button, which pops up the Edit Lookup Table for Manager dialog box.

Under Value, type the items you want to appear in your drop-down list (one item per line), then click the Close button.

project-custom-fields-lookup-table

Click OK.

 

Project – How to Use a Custom Field

 

Now that you’ve created your custom field, you can use it in your Project Schedule.

Switch to Gantt Chart view.

To add it as another field in the grid, click the Add New Column heading, and start to type the name of  your custom field (in this case, Manager). It should pop up on your list. Click the name of the custom field (Manager) to select it.

project-select-custom-field-gantt-chart-view

To replace a current field in your grid with a custom field, simply double-click the heading (ex., Resource Names). Start to type the name of the field (Manager), and select it.

This tutorial barely scratches the surface of what you can do with MS Project’s custom fields, but it’s a good start.

Free! – The Best MS Project Keyboard Shortcuts

Contact us for your free list of handy MS Project keyboard shortcuts.

Want More? MS Project Training in Atlanta, GA

MS Project 2007, 2010, 2013 Training - Atlanta & OnlineThe next MS Project class is happening soon. 2 days of MS Project  training by expert instructors in a small-class setting so that you get the attention you require. Practical, undocumented tips & tricks. Click here for registration and details

.Prefer group training at your site? Call 770.498.7333. Our courses are affordable and customized to your needs. Available for groups of 4 or more students.

 

About the Author of this  MS Project Tutorial.

Since 2001, Jackie Kiadii has taught employees of some of the largest employers in the Southeastern United States how to use MS Project. For more information, visit ProjectTrainingAtlanta.com.

Atlanta Computer Training on Facebook Atlanta Computer Training - LinkedIn Software Tips on Twitter

 

 

 

#1 Cause of Pivot Table Headaches

Untitled Document

With Excel’s pivot table tool, users can quickly consolidate, analyze and summarize large amounts of data.

In some cases, however, users are frustrated to find information missing from their pivot table’s results. Most problems with pivot tables can be traced to one source: the source data used in the pivot table.

The good news is most massive pivot table headaches can be solved with a few simple steps up front.

7 Pivot Table Source Data Rules

Here are 7 rules that will lead to fewer pivot table headaches.

Rule 1 – Unique, Descriptive Headings

Each column in your dataset must have a unique heading. For example, you should not have two column headings named sales. If your source data contains a lot of columns, it is particularly important that your headings are as descriptive as possible.

 

Pivot Table Data - Headings Incorrect

Pivot Table Data – Headings Incorrect

 

Rule 2 – No Gaps

The headings must be in the top row of your source data, and data should be immediately below the headings.

Rule 3 – Clean Up Your Formatting

If it is important to use data in a specific way, make sure it is formatted properly. For example, if you have dates in your source data that are formatted as text, it is best to convert them to dates before creating your pivot table.

Rule 4 – Don’t Mix Formats

All data in a column should have the same format. This is especially true when dealing with dates: the biggest problem I see is mixing dates (11/11/16) and text (TBD)  in the same column. If you want to maximize pivot table’s powerful abilities to analyze and group date information, make sure that a column that contains dates doesn’t also contain text.

Pivot Table Data - Incorrect Formats

Pivot Table Data – Incorrect Formats

 

Rule 5 – Blank Rows Are A No-No

Avoid blank rows in your source data.

Rule 6 – Leave Totaling to the Experts

Remove all totals from your source data – pivot tables do a much better job.

Rule 7 – Start with a Table

Instead of basing your pivot table on raw source data, convert your data to an Excel table before creating the pivot table. If your data changes constantly, this quick step will save you a lot of time.  Creating a table makes your data source dynamic –  all you have to do is hit the refresh button to grab the most recent records. Without a dynamic data source you have to use the Change Data Source command to make sure your pivot table is accessing current data.

pivot table data correct headings format

Pivot Table Data – Corrected

 

The key to getting reliable answers from Excel’s extremely powerful pivot table tool is making sure your data is properly formatted before you begin.

Interested in learning about pivot tables? Learn more about our Pivot Tables and PowerPivot courses.

Do you have an Excel question?

Comment below and it just might make it to a blog post!

About the Author

Jackie Kiadii and her team of subject matter expert (SME) trainers provide Microsoft software training that delivers measurable results – increasing productivity, reducing inefficiency, and improving the bottom line. Topics include: Project, Access, Excel, Excel VBA, PowerPoint. Upcoming Excel courses are on our calendar, click here.

Need onsite group training? Call 770.498.7333 or email us. Our courses can be customized to your needs. Jackie has provided software training for employees of The Coca-Cola Company, Emory University, Novelis, and more. This training is available for groups of 4 or more students.

Atlanta Computer Training on Facebook Atlanta Computer Training - LinkedIn Software Tips on Twitter

Click to Grab Your Excel Ebook – Free!

Get the free Excel ebook

12 Tips to Empower Excel Users

Excel VBA, Access, Project Training – Jan 2016

Untitled Document

Microsoft Training AtlantaExcel VBA, Access, Project Training – Atlanta, GA

Schedule of January 2016 Courses

The only thing more expensive than education – is ignorance. Ben Franklin

To download our Computer Training Schedule in PDF format, please click here.

Computer Training Atlanta - Schedule of Classes

Want us to bring training to you?

We can provide on-site training for groups of as few as 4 people. Our on-site courses are a great value. Call 770.498.7333 or email us for information.

Since 2001, we’ve provided training for employers large & small, in the public and private sectors.

Microsoft Training Atlanta by Subject Matter Experts

What Students Say:

Jackie helped us develop our personal Access database for internal use. This custom training was invaluable to the success of our new initiatives.

Great presenter. Extremely engaging and knowledgeable of the subject matter. I personally enjoyed hearing real world experience w/ the product and the challenges faced.

Our office experienced numerous problems and downtime when we transitioned to Outlook 2010. In a few hours, Jackie was able to bring us up to speed – her training helped us save countless hours in wasted time.

 

Computer Training Atlanta

Schedule of January 2016 Courses

Access 2013 Comprehensive Training – MCAS Certification Prep
Access 2013 Training - Atlanta, GADate: Mon Jan 04 2016 – Thu Jan 07 2016
Time: 9:00 am – 5:00 pm
Place: .1100 Peachtree Street Suite 200 Atlanta, GA 30309
For Info: http://www.probiztechnology.com/access2013trainingatlanta.htm
Please bring your laptop (or let us know if we need to provide one).

Excel 2013 Pivot Tables Training – Atlanta
Excel 2013 Training by Subject Matter Experts - Atlanta, GADate: Fri Jan 08 2016 – Fri Jan 08 2016
Time: 9:00 am – 1:30 pm
Place: .1100 Peachtree Street Suite 200 Atlanta, GA 30309
For Info: http://www.probiztechnology.com/c_excel-pivot-tables.htm
Please bring your laptop (or let us know if we need to provide one).

Excel 2013 Comprehensive Course – MCAS Certification Prep
Excel 2013 Training by Subject Matter Experts - Atlanta, GADate: Mon Jan 11 2016 – Wed Jan 13 2016
Time: 9:00 am – 5:00 pm
Place: .1100 Peachtree Street Suite 200 Atlanta, GA 30309
For Info: http://www.probiztechnology.com/excel2013trainingatlanta.htm
Please bring your laptop (or let us know if we need to provide one).

Access Database 2010 Basics (Atlanta, GA) In-Depth, 2-Day Class
Access 2010 Training - Atlanta, GADate: Tue Jan 12 2016 – Wed Jan 13 2016
Time: 9:00 am – 5:00 pm
Place: .1100 Peachtree Street Suite 200 Atlanta, GA 30309
For Info: http://www.probiztechnology.com/access-essentials-2daytraining.htm
Please bring your laptop (or let us know if we need to provide one).

Access Database 2010 Intermediate (Atlanta, GA)
Access 2010 Training - Atlanta, GADate: Thu Jan 14 2016 – Thu Jan 14 2016
Time: 9:00 am – 5:00 pm
Place: .1100 Peachtree Street Suite 200 Atlanta, GA 30309
For Info: http://www.probiztechnology.com/access-int-trainingatlanta.htm
Please bring your laptop (or let us know if we need to provide one).

Excel 2010 Pivot Tables Training – Atlanta
Excel 2010 Training - Atlanta, GADate: Thu Jan 14 2016 – Thu Jan 14, 2016
Time: 9:00 am – 1:30 pm
Place: .1100 Peachtree Street Suite 200 Atlanta, GA 30309
For Info: http://www.probiztechnology.com/c_excel-pivot-tables.htm
Please bring your laptop (or let us know if we need to provide one).

Access 2010 Advanced (Atlanta, GA) 1 Day Training
Access 2010 Training - Atlanta, GADate: Fri Jan 15 2016 – Fri Jan 15 2016
Time: 9:00 am – 5:00 pm
Place: .1100 Peachtree Street Suite 200 Atlanta, GA 30309
For Info: http://www.probiztechnology.com/accesstrainingatlanta-2010advanced.htm
Please bring your laptop (or let us know if we need to provide one).

MS Project 2010 Basic Training – Atlanta
MS Project 2010 Training - Atlanta, GADate: Tue Jan 19 2016 – Tue Jan 19 2016
Time: 9:00 am – 5:00 pm
Place: .1100 Peachtree Street Suite 200 Atlanta, GA 30309
For Info: http://www.probiztechnology.com/msproject-basic-atlanta-v2010.htm
Please bring your laptop (or let us know if we need to provide one).

MS Project 2010 Advanced Training – Atlanta
MS Project 2010 Training - Atlanta, GADate: Wed Jan 20 2016 – Wed Jan 20 2016
Time: 9:00 am – 5:00 pm
Place: .1100 Peachtree Street Suite 200 Atlanta, GA 30309
For Info: http://www.probiztechnology.com/msproject-advanced-atlanta-v2010.htm
Please bring your laptop (or let us know if we need to provide one).

MS Project 2013 Training – Atlanta
project training atlanta gaDate: Thu Jan 21 2016 – Fri Jan 22 2016
Time: 9:00 am – 5:00 pm
Place: .1100 Peachtree Street Suite 200 Atlanta, GA 30309
For Info: http://www.probiztechnology.com/project-training-atlanta-v2013.htm
Please bring your laptop (or let us know if we need to provide one).

Excel Macros & VBA – 2 Days (Visual Basic for Applications)
Excel VBA Training - Atlanta, GADate: Mon Jan 25 2016 – Tue Jan 26 2016
Time: 9:00 am- 5:00 pm
Place: .1100 Peachtree Street Suite 200 Atlanta, GA 30309
For Info: http://www.probiztechnology.com/excel-2010-vba-macros-training.htm
Please bring your laptop (or let us know if we need to provide one).

Excel 2010 Comprehensive Course – MCAS Certification Prep
Excel 2010 Training - Atlanta, GADate: Wed Jan 27 2016 – Fri Jan 29 2016
Time: 9:00 am – 5:00 pm
Place: .1100 Peachtree Street Suite 200 Atlanta, GA 30309
For Info: http://www.probiztechnology.com/excel2010trainingatlanta.htm
Please bring your laptop (or let us know if we need to provide one).

Excel 2010 PowerPivot Training
Excel 2010 Training - Atlanta, GADate: Wed Jan 27 2016 – Wed Jan 27 2016
Time: 9:00 am – 5:00 pm
Place: .1100 Peachtree Street Suite 200 Atlanta, GA 30309
For Info: http://www.probiztechnology.com/power-pivot-training-atlanta.htm
Please bring your laptop (or let us know if we need to provide one).

Excel 2013 PowerPivot Training
Excel 2013 Training by Subject Matter Experts - Atlanta, GADate: Thu Jan 28 2016 – Thu Jan 28 2016
Time: 9:00 am – 5:00 pm
Place: .1100 Peachtree Street Suite 200 Atlanta, GA 30309
For Info: http://www.probiztechnology.com/power-pivot-training-atlanta.htm
Please bring your laptop (or let us know if we need to provide one).
7 Reasons to Attend Our Computer Training in Atlanta, GA:

  1. An investment in yourself yields the best return. In addition to being a potential tax-deductible expense, the investment you make in training yields returns in making you more productive and competitive.
  2. Passion – We go the extra mile. We choose instructors who are passionately committed to each student’s success, and we take the time to provide follow-up coaching (often at no extra cost) to make sure you are equipped to use the software. As one student says: Even after the class was over, Jackie has been an invaluable resource by showing her continuous quest to ensure that Microsoft Access is demystified.
  3. Small Class size: This course is limited to 8 students, providing you the opportunity to get 1-on-1 assistance.
  4. Expertise. We only hire subject matter experts (SME) Since they know the subject matter inside and out, our instructors are able to adapt the training “on the fly” to meet students’ needs. To request instructor credentials, please click here.
  5. Affordable – a great value..
  6. The MS Office training manuals are approved as preparation materials for  the Microsoft Certified Application Specialist exams.
  7. Flexibility. Can’t make it to our location? No worries. Our courses are now conveniently offered online.  This means you can attend, no matter where you are, as long as you have high speed Internet access.

To download our Computer Training Schedule in PDF format, please click here.

Computer Training Atlanta - Schedule of Classes

 

MS Access Query Tip: How to Extract Titles and Last Names

Untitled Document

This question comes from a blog reader in Athens, Greece. While reading the blog post on splitting first and last names, he had a follow-up question:

I have a problem. I would like to know how to isolate title and last name in access. For example there is name, let’s say, Mr. John Brown and I want to isolate and to make it look like Mr. Brown. Could you help?

access query results - title and last name extracted

 

Answer:

In order to split the data in a field, you will need to use a combination of the following Access functions:

 

Left
Syntax: Left (string, length)
Returns a specified number of characters from the left side of a string.

Mid
Syntax: Right (string, length)
Returns a specified number of characters from the right side of a string.

InStr
Syntax: InStr([start, ]string1, string2[, compare])
Allows you to search for a character or string within a string, and returns a number telling you the first occurence of one string within another, starting from the beginning of the string.

InStrRev
Syntax: InStrRev(«string_to_search», «stringmatch», «start», «compare»)
Allows you to find the position of the first occurrence of a string in another string, starting from the end of the string.

 

Our sample table has a field (strName) that shows the names of employees.

Extract the Title (ex., Mr.)

Create a query. In the query, create a field and use a combination of Left and InStr functions to extract the title.

strTitle: Left([strName],InStr([strName],” “)-1)

Here’s a look at Title field in the query design grid.

access query functions to extract title

Extract the Last Name (ex., Brown)

The second field in your query will use a combination of the Mid and InStrRev functions to extract the last name. Note: this combination can also extract the last word in any string.

strLName: Mid([strName],InStrRev([strName],” “)+1)

Here’s a look at the Last Name field in the query design grid.

access-extract-last-name

 

Join the Title and Last Name (ex., Mr. Brown)

Once you’ve extracted the title and last name, joining them together is simple. Just use the concatenation symbol (&).

strTitleLName: [strTitle] & ” ” & [strLName]

access query concatenate title last name

 

 

Need more Access tips? Subscribe to our email newsletter so that you never miss another one.

About the author.

Jackie Kiadii has 20 years of Access database development experience, and teaches Basic through Advanced Access courses, including an in-depth 4-day Access immersion course.

Access Database Training in Atlanta, GA and Online

Need more MS Access help? Here are your options.

Atlanta Computer Training on Facebook Atlanta Computer Training - LinkedIn Software Tips on Twitter

Excel Tip – How to Protect Specific Cells

Untitled Document

This question came from a student during an Excel course.

I share a workbook with my co-workers. I have some very complex functions in this workbook. From time to time, I find that my functions no longer work as intended because someone has tried to change them. I don’t want to protect the entire worksheet or workbook because my co-workers do need to be able to change some cells. Is there any way to prevent people from changing my functions?

Answer – Protect Specific Cells

Yes, you have several options. You can use Visual Basic for Applications to create your own functions. You can also hide the functions so that users see your results but not the functions. Your simplest path, however, is to probably protect specific cells. Users will be able to view, but not edit the protected cells in your worksheet.

 

Step 1 – Select the Unprotected Cells

The first step in the process of protecting cells in a worksheet is to select the areas you want people to be able to edit. You can select individual cells or entire columns.

(Quick Tip: To select non-contiguous areas, select the first area and press the CTRL key while selecting other areas).

Step 2 – Unlock the Selected Cells

Right-click the selected cells, then click Format Cells.

protect cells excel - format cells

When the Format Cells dialog box pops up, select the Protection tab.

Remove the check mark next to Locked.

protect cells excel unlock cells

Step 3 – Protect the Worksheet

Now that you’ve unlocked the cells you do not want to protect, you are going to protect your worksheet. This will, in effect, protect everything except for the cells you selected in the previous step.

Click the Review tab on your ribbon.

In the Changes group, select Protect Sheet.

protect cells worksheet excel

When the Protect Sheet dialog box pops up, type your password (optional), then click the OK button. You will be prompted to enter your password again.

excel protect sheet dialog box

Note:  If you do not create a password before clicking the OK button, users will be able to turn the protection off by simply clicking a button.

 

Do you have an Excel question?

Comment below and it just might make it to a blog post!

About the Author

Jackie Kiadii and her team of subject matter expert (SME) trainers provide Microsoft software training that delivers measurable results – increasing productivity, reducing inefficiency, and improving the bottom line. Topics include: Project, Access, Excel, Excel VBA, PowerPoint, Word, Outlook and Publisher. Upcoming Excel courses are on our calendar, click here.

Need onsite group training? Call 770.498.7333 or email us. Our courses can be customized to your needs. Jackie has provided software training for employees of The Coca-Cola Company, Emory University, Novelis, City of Atlanta and more. This training is available for groups of 4 or more students.

Atlanta Computer Training on Facebook Atlanta Computer Training - LinkedIn Software Tips on Twitter

Click to Grab Your Excel Ebook – Free!

Get the free Excel ebook

12 Tips to Empower Excel Users

 

 

 

 

 

 

 

 

 

 

Older posts «