Enter Complex Excel Formulas Fast

Enter Complex Excel Formulas Fast

On Chandoo's blog, Hui is running a series of articles with Excel Tips. There were some great tips in this week's list, but my favourite one was down in the comments section – how to enter complex Excel formulas fast.

I've been using Excel for a long time, and I've never seen this suggestion before. And it's a real time saver!

Continue reading Enter Complex Excel Formulas Fast

Free Excel Dashboard Tools

DataPig Excel Dashboard Tools

Mike Alexander, from the Bacon Bits Excel blog, has just released a major update to his Dashboard Tools add-in. And the great news is that you can get a free copy for the next few days! Mike is making this available until May 7th, at midnight (Central Time Zone).

Continue reading Free Excel Dashboard Tools

Use Running Total to Compare Years in Excel

Running Total to compare years

If you're analyzing sales data from year to year, you can make a line chart that shows each month's sales. That lets you see if there were any months with big differences, and shows how sales went up and down over the year. Another option is to use a Running Total to compare years in Excel. It's quick and easy to set up with a pivot table and pivot chart.

Continue reading Use Running Total to Compare Years in Excel

Choose From a List to Change Excel Data

TEXT function select language

Do you need a quick way to show different data in a worksheet? For example, on an order form, you could let people select a region, and automatically include the shipping cost or tax rate for the selected region. I added an English/French selector to a workbook last week, so a formula would work correctly, in either language.

Continue reading Choose From a List to Change Excel Data

Create an Excel Hyperlink With Drag and Drop

create an Excel hyperlink with drag and drop

In a big Excel file, it's handy to have hyperlinks that take you from one sheet to another. Maybe you need a table of contents, or a single link on a summary sheet that takes you to a data entry sheet. You can use the Insert Hyperlink command on the Ribbon, but it's even quicker to create an Excel hyperlink with drag and drop. Here are a couple of ways to do that.

And there are a couple of Excel announcements too.

Continue reading Create an Excel Hyperlink With Drag and Drop

Pivot Table Time Problems

pivot table time rounding

It's pivot table time! First, we'll take a look two common pivot table time problems - rounding and totals. Then I'll show you a couple of ways to save time when working with pivot tables.

Continue reading Pivot Table Time Problems

Use Slicers to Set Filter Criteria in Excel

Use Slicers to Set Filter Criteria in Excel

In most cases, it's best if you keep people away from your data in Excel. It's too easy to mess things up! Instead, set up a worksheet where they can use Slicers to set filter criteria in Excel.

Then click a button, and a macro dumps the data onto the worksheet. In the screen shot below, you can see how it works – easy, quick, and safe!

Continue reading Use Slicers to Set Filter Criteria in Excel

Highlight the Latest Lottery Number Matches

Highlight Latest Lottery Picks in Excel http://blog.contextures.com/

Did you win the big prize? Have you ever picked those winning numbers? If you're keeping track of lottery ticket numbers, use Excel conditional formatting to highlight the winning numbers in a list. Each week, enter the numbers from the latest draw, and Excel will colour all the cells that have matching numbers. You can use a different colour to highlight the latest lottery number matches. The screen shot below shows an example.

Continue reading Highlight the Latest Lottery Number Matches

Excel Roundup 20160321

Excel Roundup http://blog.contextures.com/

In this week's roundup, Data Analysis, Power BI, combining functions, and more Excel tips.

NOTE: There won't be an Excel Roundup next week (March 28th), due to the Easter Monday holiday.

Continue reading Excel Roundup 20160321

Excel Sheet Selector With No Macros

If you're setting up a workbook for other people to use, they'll appreciate it if you make it easy to move around in the file. You can create a table of contents on the first sheet, and that will get them off to a good start. But then what?

Once they're on one of the other sheets, how can they get back to the table of contents, or go directly to a different sheet? We'll add a drop down list of sheets, to make it easy to go to the one that you need.

Continue reading Excel Sheet Selector With No Macros