All articles in 'Excel Howtos' Category
Ever wondered how we can use Excel to send emails thru Outlook? In this article we well learn how to use VBA and Microsoft Outlook to send emails with your reports as attachment.
Scenario: We have an excel based reporting template. We want to update this template using VBA code to create a static version and email it to a list of people. We will define the recipient list in a separate sheet.
Read on…Continue »
I have imported some data that comes in as a number that I need to convert to h:mm. The data string will be either 1,3,4,5,6 integers long and looks like this…
Often we have 2 workbooks with same data structure but different data. We want to compare both and see how they differ. Lets talk about view side by side mode in Excel and how we can use it in situations like these.Continue »
Last week, we learned how to use SQL and query data inside Excel. This week, lets talk about how we can use VBA to consolidate multiple data sheets from different workbooks into one single worksheet.Continue »
Often I have thought, if I could have write “Select EmployeeName From Sheet Where EmployeeID=123” and use this on my excel sheet, my life would be simpler. So today we will learn how to do this.
People spend a lot of time thinking whether to use Excel as their database or not. Eventually they start using Access or SQL Server etc.
Today we will learn how to use Excel as a Database and how we to use SQL statements to get what we want. We will learn how to build a form like above.Continue »
Is Excel acting slow & taking ages? As part of our Speedy Spreadsheet Week, today lets talk about optimizing & speeding up Excel by formatting & charting better. Use these tips & ideas to super-charge your sluggish workbook.
No matter how much data you got, how many formulas you wrote, the end users seldom see them on your workbook. They see the finalized dashboard, they play with the model, they look at the report. And if you make poor choices, your end users will thing your workbook is slow.
So let me present you 7 charting & formatting tips to optimize & speed up Excel. Read on…,Continue »
Excel formulas acting slow? As part of our Speedy Spreadsheet Week, today lets talk about optimizing & speeding up Excel formulas. Use these tips & ideas to super-charge your sluggish workbook. Use the best practices & formula guidelines described in this post to optimize your complex worksheet models & make them faster.
1. Use tables to hold the data
2. Use named ranges & named formulas
3. Use pivot tables
4. Sort your data
5. Use manual calculation mode
… and more. Read on to learn these top 10 tips & ideas to improve performance of your excel formulas.Continue »
In recent installment of Customer Service Dashboard post, our reader Salmon asked an interesting question,
I am struggling with data size with my dashboards…so many SQL data pulls and formulas to generate the Dashboard, the entire file is massive and sluggish. Perhaps a few tips from Chandoo Master for all us rookie dashboard designers regarding how to minimize file size and maximize calc speeds. #
Dan l & others chipped in and shared their ideas on speeding up Excel. But the topic is wide & has many solutions. So I am dedicating an entire week to discuss this. Welcome to Speedy Spreadsheet Week.Continue »
We briefly covered Excel’s Go To Special function in the Managing Spreadsheet Risk series and in this post, we are going to explore Go to special feature in detail and learn how to use it.
What is Go To Special?
Go To Special is a tool within Microsoft Excel that enables you to quickly select cells of a specified type within your Excel worksheet. Once you get to grips with this function and what it can be used for you will wonder how you ever lived without it. Read on…,Continue »
Did you know you can apply any text effect to a single character or group of text characters within a cell ?
This post will show you 2 quick tips to assist you in this technique.Continue »
Today is February 29th, and that means, this year we have one more day to be awesome. So lets celebrate it in Excel style!
Lets learn 14 different ways to tell if an year is leap year, using Excel Formulas.
Why 14? because, we are awesome like that.Continue »
Do you know that in Excel 2010 you can create your own Ribbon tabs and add anything to them, including your own macros? Today, we are going to take a look at this useful feature and learn how to add your own macros as buttons to Excel Ribbon. Steps to Add your own macros to […]Continue »
Today we look at a very neat way of doing a complex Nested If or Vlookup style problem with a simple but beautiful Sumproduct based formula.
Here is a quick tip to add awesome to your Wednesday.
If you want to enter numbers like 00023 or 023.340 or 23.34500 in your Excel sheet, you would notice that Excel magically removes leading zeros and trailing zeros (after decimal point) as the number 23 is same as 00023. But sometime, we want 00023, not 23. Then what?!?
Very simple, we use TEXT format instead of number format. Just select the cells where you are going to enter these numbers, and from Home ribbon > Number area, select “Text” as cell type. This tells Excel to treat any value you enter as Text, not as number. So when you type 00023, it will appear as 00023.Continue »
Project Managers often report financial numbers to the management. In a dynamic world, these numbers are usually based on a lot of factors that may or may not be under your control. So the top management demands that the numbers be reported as per different economic scenarios – Optimistic, Normal or Pessimistic. It is important […]Continue »