SmittyPro - Witticisms, Help, Tutorials and More
Feb
14

Mastering Excel for Analytics Success (with 20% off!)

written by "Smitty"

Post to Twitter Post to Facebook Post to Google Buzz Post to LinkedIn Post to Technorati

Do you want to make Excel work a little harder and faster for you? Do you spend way too much time forcing Excel to bend to your will instead of automating reporting and performing valuable analysis and discovery work? Are you positive that there are better ways to spend your time than manually formatting and configuring your data in Excel? Finally, are you anywhere near the Bay Area?

If so, then you can spend a day with Microsoft MVP’s Jordan Goldmeier and Zack Barresse at the eMetrics conference in San Francisco on Thursday, March 20, 2014.

It’s not a day-long review of Excel’s over 300 formulas and funcitons, but an opportunity to get hands-on, down and dirty with two experts who are passionate about Excel. Whether you get lost in nested formulas, have never worked on a Pivot Table or are a master of macros, you will walk away from this workshop armed with more methods for manipulating data in Excel than you knew existed.

•Capitalize on Pivot Tables
•Control Filters with finesse
•Correct common error messages
•The new face of Business Intelligence
•Conduct 300 million rows in Excel like a boss

Come to this workshop with a laptop full of data and these two Microsoft Excel MVP’s will show you how to overcome the obstacles that are holding you back on a daily basis. If you’re ready to learn how to break old habits, develop new skills and gain a whole new appreciation for the familiar old tool you take for granted then you need to spend some time with these guys.. Get a glimpse into the newest plug-ins that can help you make smarter marketing decisions and save time. Some are sure to impress your co-workers and boss.

I’ve worked personally with Jordan and Zack, and can guarantee that you will walk away with a newfound passion for Excel and putting it to work for you.

If you want to learn more, you can check it out here: http://www.emetrics.org/sanfrancisco/2014/workshops/mastering-excel-for-analytics-success/

Seats will be limited, because these guys are GREAT!  And if you use this Offer Code: EMSSF1415 – you’ll get 20% off.

Post to Twitter Post to Facebook Post to Google Buzz Post to LinkedIn Post to Technorati

Nov
1

Windows 8.1 “Help” Stickies & the “Start” Button…

written by "Smitty"

Post to Twitter Post to Facebook Post to Google Buzz Post to LinkedIn Post to Technorati

If you’ve upgraded to Windows 8.1 you’ve no doubt noticed these really $*&^%@#! irritating stickies that show up if you inadvertently hit a corner with the mouse and they won’t go away. Naturally, in Microsoft’s infinite wisdom (same as with the whole Windows 8 BS), there’s no native option to turn them off, so here’s a Registry edit that will do it:

Disable Windows 8.1 “Help” Stickies

And as you’ve probably noticed, the “new” Start Button is next to useless, but here’s some freeware that will create an actual working Start Button:

Classic Shell

It looks like Steven Sinofsky’s replacement didn’t listen to consumers anymore than he did. The fact that under the hood Windows 8 is better than Windows 7 doesn’t mean squat if it’s still a freaking pain in the ass interface. As far as I’m concerned “Metro” has no place in a business environment.

Post to Twitter Post to Facebook Post to Google Buzz Post to LinkedIn Post to Technorati

Jul
28

Excel 2010 – Business Basics and Beyond

written by "Smitty"

Post to Twitter Post to Facebook Post to Google Buzz Post to LinkedIn Post to Technorati

I’ve been fortunate to have had some fantastic opportunities to travel around the country facilitating Excel training, and along the way found that a lot of people who use Excel in a business environment receive little, if any support for how to actually use Excel. The result is often outright frustration in not knowing the best ways to perform tasks with Excel. If you’re one of the lucky ones who is considered the “Office Excel Guru”, then you know this all to well. In fact, in my life in a Fortune 500 corporate environment, I was just that person, and found a lot of my time spent helping others be more effective in Excel. Another thing that I saw all to often was how poorly the “professional training material” that is provided to many organizations is sorely lacking in terms of business relevance. As a result, I decided to write a book on Excel focused specifically at how to use Excel in a business environment. My overall goal with the book is to show you how to make Excel work for you vs. you working for Excel.

Excel 2010 - Business Basics & Beyond

The true inspiration for the book was my old boss, who for years I tried to help with Excel, but more often than not I simply ended up doing it for him because it was much less frustrating. When he reviewed the book he asked me outright why I hadn’t done it earlier.

Here’s a brief description of what you can expect:

Microsoft Excel is one of the most powerful tools a business owner, manager, or new employee has at their disposal, and this guide teaches how to harness business data and put it to use. Using real-world examples of a small business in operation, the book covers topics such as preparing financial statements, how to best display data for maximum impact with formatting tools, data tables, charts and pivot tables, using customer information to create customized letters with mail merge, importing data from programs such as QuickBooks, calculating the costs of doing business with financial formulas, and much more. Helpful screenshots are spread throughout the text, and the book explains how to find ready-made templates online for free.

The book begins with how to customize Excel so it suits your style/needs (especially with regards to the “Ribbon” user interface, new with Excel 2007), then follows a natural progression of setting up a workbook project as you would in a business environment. This involves discussing intelligent spreadsheet design, entering/editing data and formulas, followed by the analytical tools you have at your disposal, finishing with how to import/export data from/to other sources. There are also downloadable sample workbooks for each chapter, so you can follow along with the content in Excel on your own. As for the content itself, I put a lot of effort into making note of shortcuts, as well as tips and tricks that you can use in your daily Excel work to help you become more efficient. After all, if you can save time, then you become more productive, and hopefully happy with what you’re doing.

Here’s a breakdown of the chapters:

Lesson 1 – Introduction to Excel
Lesson 2 – Basic File Operations & Setting up Excel the way you want it
Lesson 3 – The Ribbon In Depth – The Home Tab
Lesson 4 – The Ribbon In Depth – The Rest of the Ribbon.
Lesson 5 – Entering and Manipulating Data & Basic Rules of Spreadsheet Design
Lesson 6 – Using Functions (you know, formulas and stuff like that)
Lesson 7 – Formatting & Printing
Lesson 8 – Working with Graphics
Lesson 9 – Charts (this is one of the most time consuming elements for most Excel users, and this chapter shows you how to save tons of time!)
Lesson 10 – Excel Tables (an immensely under-utilized Excel feature – Once you start using them you won’t go back!)
Lesson 11 – Pivot Tables (one of the most powerful analytical tools you have at your fingertips)
Lesson 12 – Importing Data from other sources & Mail Merge

Excel 2010 – Business Basics & Beyond is by no means a be-all-end-all book and couldn’t remotely be compared to one of the Excel “Bible” publications, but if you are frustrated with Excel and want to become more productive with it, then this is a very affordable and easy to read start.

You can find it at Amazon (for Kindle), Amazon Paperback, and at the Mr. Excel Bookstore.

Post to Twitter Post to Facebook Post to Google Buzz Post to LinkedIn Post to Technorati

Jul
28

How to Import Access Data to Excel: the Easy Way…

written by "Smitty"

Post to Twitter Post to Facebook Post to Google Buzz Post to LinkedIn Post to Technorati

Many people (especially in Finance) use Excel and Access almost interchangeably, feeding data between both applications. But if you’ve never done it, then how do you? There are several ways to import data from Access to Excel, and you don’t even need to have Access open. You can import Tables, as well as most queries (Crosstab queries aren’t supported), so it’s up to you to determine which you want to import. In general, since you use Access to store large amounts of data in tables, then use its fantastic querying ability to pare that data down into manageable chunks, you’ll find yourself importing query results most of the time.

In this article we’ll discuss the easiest way to import Access data into Excel whether from Tables or Queries. To get started go to Excel’s Data menu–>Get External Data–>From Access (this was a new option with Excel 2007 and the Ribbon Interface), although if you’re familiar with it, the old MSQuery option is still there, which we’ll discuss in another article.

Get External Data

From there, select the database that you want to use as your import source. Excel will then display a list of the available Tables and Queries that can be imported:

Select Table/Query Dialog

In this case we’ll import the Customers table. Once you’ve selected the Table/Query that you want to import, Excel will ask you how you want to import it. For this example we’re just going to import the Access data as a table, although you can see that Excel gives you several options for importing the data. You should note here that while you can’t currently import Crosstab queries from Access, you can import the data that supports the Crosstab as a Pivot Table report (after all, a Pivot Table is essentially Excel’s version of an Access Crosstab query). Another option there is to create a Select query using your Crosstab query as the data source, then import the resulting Select query.

Sidenote: If you’re using Excel 2013 you’ll see a check box at the bottom to “Add this data to the Data Model”. This is an exciting new feature that the Excel team developed that allows you to create relationships between tables (a lot like you’d do in Access when building queries). If you’re interested, then make sure to check out this article from Diego Oppenheimer, Program Manager for the Excel development team at Microsoft: http://blogs.office.com/b/microsoft-excel/archive/2012/08/23/introduction-to-the-data-model-and-relationships.aspx.

Import Data Dialog

Properties – While you can certainly get detailed with Import Properties, for this article I just wanted to point out a feature that’s handy if you have a data set that refreshes at regular intervals: you can tell Excel to automatically refresh your Access data whenever you want.

Connection Properties

Skipping past the Data Model and Properties, if you hit OK your table data will be immediately returned to Excel:

Imported Data in Table Format



If you’re at all familiar with Excel’s Tables, then you’ll recognize this format, which you can modify in the Table Tools Design tab, which will become active when you select any range in the table.
If you haven’t taken advantage of the Refresh options in the Properties dialog, you can refresh on demand from the Table Tools Design tab or the Data tab.

Table Tools Refresh Option

Data Tab Refresh Options

Note: The database examples used in this article were from Microsoft’s Northwind database for Access 2010, which you can find under File–>New–>Samples.

Post to Twitter Post to Facebook Post to Google Buzz Post to LinkedIn Post to Technorati

Microsoft MVP

 

Join In

Tag Cloud

Great Links

Archives

SmittyPro.com

Welcome to SmittyPro.com, your one-stop for solutions concerning anything from beginner to extremely advanced Microsoft Excel issues and programming. Topics covered include Excel, Access, VBA, and (every now and then) some amazing observations or "Smitticisms" that might dumbfound you and send ripples of excitement or intrigue around the world.

Categories

Meta

RSS Top Stories from CNN

Vistor Counter for SmittyPro's Blog