October « 2009 « Contextures Blog

See Formulas on an Excel Worksheet

Last week I was testing a client's workbook, and had filled in all the data entry cells, to make sure everything was working correctly.

Before sending the workbook back to my client, I wanted to clear all the data entry cells. Instead of selecting each cell individually, and clearing it, it would be easier to clear groups of adjacent cells where possible.

However, some cells had formulas, and I didn't want to accidentally clear any of those. If the formulas are visible, that would prevent the problem.

To see the formulas in Excel 2003: On the Tools menu, click Options On the View tab, add a check mark to Formulas.

To see the formulas in Excel 2007

Click the Office button, then click Excel Options

Click the Advanced category

In the Display Options for This Worksheet section, add a check mark to Show formulas in cells instead of their

Continue reading See Formulas on an Excel Worksheet

Create a Chart from Excel Data in Tableau

Last month, I wrote about Tableau dashboards, and my first experiences with creating a Tableau workbook from Excel data. In this post, I've included another sample dashboard, where you can experiment with the user controls. Select one or more days of the week, or highlight a sales representative.

Also, there's a short video, that shows how quick and easy it is to connect to data in Excel, and create a chart.

First, here's the dashboard, that focuses on Parts sales for each sales representative. You can see the sales amounts per day of the week, average price compared to quantity sold, and the top three sales reps for parts.

_________________

Continue reading Create a Chart from Excel Data in Tableau

Show Total Hours in a Pivot Table

In an Excel file, you might record the time that employee work on specific projects. For example, an employee worked on Project A for 8 hours Monday, 8 hours Tuesday, 6 hours Wednesday and 3 hours Thursday, for a total of 25 hours.

If you create a pivot table from the time data, and show the total time per project, Project A shows 1:00 as the total, instead of 25:00.

Why is the total one hour, instead of 25? The pivot table subtotals are shown as time rather than total hours. From the 25 hours, the first 24 hours are counted as one day, and the remaining hour is 1:00 AM of the second day. The 1:00 in the Project A Total represents the 1:00 AM time.

Apply a custom time format

To fix the project subtotals, you can format the cells with a custom number format – [h]:mm

Continue reading Show Total Hours in a Pivot Table

Recent Posts

Categories

Archives

See Formulas on an Excel Worksheet

Create a Chart from Excel Data in Tableau

Show Total Hours in a Pivot Table

Recent Comments

Contextures

Calendar

Subscribe

Email Updates