Category Archives for "Excel Tips"
Tips on how to use Microsoft Excel
How to Find and Remove Duplicates in Excel
In working with data from any source, you need to make sure you don’t have duplicate values. In many systems, this can be particularly cumbersome. In my favorite manual data processing tool, Excel, there are a few ways to simplify this process. I’ve used this technique to remove duplicates from Excel for years for its […]
Never See Errors Again in Excel
We’ve already looked at how to use VLookup to return a corresponding value from another sheet here (VLookup Basics). Now we’re going to expand upon that and add some conditional logic to determine if a value exists in another sheet. Our example is looking up order numbers on a separate sheet that contains all returns. By […]
Excel Tip – VLookup Basics
The VLookup is the most useful and powerful function in Excel. I don’t say that lightly. I’ve been working in Excel for nearly 20 years and have not used any other function as much or with as much utility and success.This tutorial is going to get you setup with the basics of building a VLookup. […]
Excel Dashboard Training
In my latest course, Business Dashboard Fundamentals, I cover step-by-step how to make Excel Dashboards. These Excel Dashboards are just one of the two practical methods I provide of building Business Dashboards. Beyond just the practical examples of building Excel Dashboards I cover the fundamental topics of Visual Perception, Common Charts, Dashboard Planning, Dashboard Design, […]
Keyboard Settings for Excel via VMFusion
Ever have problems using your standard keyboard shortcuts in Excel when running it on a Mac in VMFusion? Me too… So, after some digging and testing I found some of my basic Excel navigation functions would trigger rather heinous responses by my Mac (currently running Lion). Here is what I was able to do to […]
Excel Tip – Hacking Excel Web Queries
If you are unfamiliar with web queries its OK, their easy, and I’m gonna explain how they work and how to manipulate them beyond their intended use to increase their flexibility. Excel Web Queries Overview Briefly, an Excel Web Query is a great way to automate the mundane task of going to a web page […]
Democratizing Business Intelligence using Gemini
If you’ve not heard of Gemini yet you’re in for a treat. I found these videos on youtube yesterday of the Gemini demo from the BI conference last year that you can view below. Gemini is a new part of the MS BI stack that is an add-in for Excel that allows power-users the ability […]
Building a Project Timeline in MS Excel
Below is a recent screencast I recorded showing how to build a project timeline (gannt chart style) using MS Excel. It’s simple and easy and works great for porting it around to other office apps (eg. Word, PowerPoint). Let me know if you have questions by posting a comment and enjoy!
Excel Tip – Defaulting to Non-Compatibility Mode
I was recently fighting with Excel because I was sick of it wanting to run in Compatibility Mode by default. Everything I found out there said that you simply need to change the option for Save-As to be the 2007 format. This only seemed to work on the workbooks I had open. If I closed […]
Excelsius and the MS BI Stack
Recently I was on a call with a client and one of their IT guys asked if the Microsoft Business Intelligence stack can do everything Exclesius can do. In short, the answer is sorta. Through reporting services the MS BI stack can provide similar functionality however, it cannot provide the flash animations you get in […]