{"id":6582,"date":"2020-09-10T14:00:00","date_gmt":"2020-09-10T12:00:00","guid":{"rendered":"https:\/\/beebole.com\/blog\/?p=6582"},"modified":"2025-09-18T22:19:50","modified_gmt":"2025-09-18T20:19:50","slug":"how-build-timesheet-automated-reports-in-excel-power-query","status":"publish","type":"post","link":"https:\/\/beebole.com\/blog\/how-build-timesheet-automated-reports-in-excel-power-query","title":{"rendered":"How to build an automated report in Excel with Power Query: The time tracking dashboard"},"content":{"rendered":"\n

When it comes to maximizing productivity and saving time, automated reports in Excel with Power Query<\/a> are key<\/strong>. That’s why we created this tutorial on YouTube<\/a>, where you can see how to build a time tracking dashboard with Power Query. Reporting automation allows managers and controllers to have business metrics dashboards up to date in a predefined format. Report automation<\/em> means that the whole process of the report creation and update <\/strong>must be enabled through some sort of software, app or API, so it becomes faster and more efficient. The main goal of these automatic reports is to save you time and money. And yet, up to 62% of employees surveyed here can identify at least three major inefficiencies at work that could be<\/em> automated<\/a>.<\/p>\n\n\n\n

With that data in mind, we\u2019re excited to publish this tutorial<\/a>, inspired by one of our very own customers (Marek Laanbek, Beebole customer and financial controller at CES Estonia) who took the employee and project data in Beebole and turned it into a beautiful monthly automated timesheets report. While he used Beebole’s API to create his dashboard, we decided to go the route of report automation<\/strong> with Excel Power Query<\/strong>, also known as Get & Transform<\/em>. You can watch the tutorial on YouTube here<\/a>.<\/p>\n\n\n\n

\n
\n
\n

What is Excel Power Query? <\/h2>\n <\/div>\n
\n

As the Microsoft support site indicates, “Power Query is a data discovery and query tool, good for\u00a0shaping and mashing up data<\/a>.”<\/p>\n

It is a powerful feature that allows you to import, manipulate, and transform data from a variety of sources, such as databases, text files, web pages, and other data formats, and then load that data into Excel for analysis and reporting. Power Query is part of Microsoft’s business intelligence (BI) suite and is often used for data preparation and cleansing tasks. It\u2019s a valuable tool for anyone who needs to work with and manipulate data from various sources within Excel as it helps streamline the data preparation process, reduces the risk of errors, and enhances productivity when working with large datasets or complex data sources.<\/p>\n <\/div>\n <\/div>\n<\/div>\n\n\n

Time tracking tools that integrate with Excel<\/h2>\n\n\n\n

Beebole’s dashboards are powerful and make it easy to create multiple views for your timesheet data. However, we know many users are more comfortable working in Excel, which is why Beebole makes it easy to export your raw data from the app so that you can review and analyze it in any tool. Don’t miss our add-in for Excel<\/a> that allows you to connect spreadsheets directly to your Beebole account. This simplifies building reports and analyzing data in Excel<\/a>. If you’d like to see it in action, have a look at how to use Beebole for overtime cost analysis<\/a>.<\/p>\n\n\n\n

\"A
An example of an Excel dashboard with data from a timesheet app like Beebole<\/svg><\/span><\/a>.<\/mark><\/em><\/figcaption><\/figure>\n\n\n\n

Beebole\u2019s easy-to-use export feature and free Excel integration<\/svg><\/span><\/a> make them perfect complementary tools to work with your timesheet data. In this tutorial, we\u2019re going to use Beebole timesheet data with Excel to create an automated employee time report dashboard<\/strong>. Of course, in order for this dashboard to be fully functioning, employees must actually fill out their timesheets! That’s exactly why we’ve put together this quick overview of examples of timesheet reminders and templates<\/a>. You can learn more about other types of time tracking integrations<\/a> here.<\/p>\n\n\n\n\n \n \n <\/svg>\n\n \n \n <\/svg>\n\n

\n \n
\n

Taptu stopped wasting time on manual reporting\u2014why haven\u2019t you? <\/strong>The Australian IT consultancy connected Beebole with Office 365 and Xero to automate everything from timesheets to payroll. The result? A nearly hands-free workflow that scales with their business.<\/p>\n

Follow Taptu\u2019s footsteps if you\u2019d like to:<\/p>\n

\u26a1Eliminate error-prone manual reporting
\n\u26a1Keep timesheet, invoicing, and payroll data in sync automatically
\n\u26a1Free your team from admin so they can focus on clients
\n\u26a1Build a scalable operations workflow that grows with your business<\/p>\n <\/div>\n\n \n Read the case study\n \n \n \n <\/svg>\n <\/a>\n <\/div>\n<\/div>\n\n\n

\n
\n
I generate several reports to look at timesheet data and easily export them to Excel.<\/div>\n
\n Robert P. , \n Bookkeeper at Apex Analytics <\/span>\n <\/div>\n <\/div>\n
\n
I have found the tool very effective in creating monthly reports for team leads and finance. Also, applying some post-processing (by means of a spreadsheet) is very easy thanks to all the metadata that can be added to time entries such as custom field values and groups.<\/span><\/div>\n
\n Filippo C. , \n Software project manager <\/span>\n <\/div>\n <\/div>\n
\n
I have been using Beebole for the last 10 years and I love how it’s simple and quick to use. Their report tool is very useful for annual summary.<\/div>\n
\n Dominic H. , \n Co-founder at JoliYeti <\/span>\n <\/div>\n <\/div>\n <\/div>\n\n\n\n

Tutorial: Learn to build a time tracking dashboard in Excel<\/h2>\n\n\n\n

In the video below, we show you exactly how to build a time tracking dashboard in Microsoft Excel, a game-changer when it comes to understanding your time tracking data.<\/p>\n\n\n\n

\n\"How<\/path><\/path><\/svg><\/span><\/span>There's more where that came from.<\/span>Don\u2019t miss a single video.<\/span><\/span>Subscribe<\/a><\/span><\/span>\n<\/div><\/figure>\n\n\n
\n
\n

Create Your Own Powerful Time Tracking Reports<\/h4>\n

We'd love to show you the best reports for your business needs.<\/p>\n

\n Book a Call<\/a>\n Sign Up<\/a>\n <\/div>\n <\/div>\n<\/div>\n\n\n

Step-by step guide: Building an automated time tracking dashboard<\/h2>\n\n\n\n

1. Download your Beebole timesheet data<\/h3>\n\n\n\n

First, we need to download the employees’ timesheet data from Beebole. After you log into your Beebole dashboard, find the Reports <\/strong>section on the right side. We need to create a report that gets data from Beebole to export.<\/p>\n\n\n\n

Then, let\u2019s give our report a name. I set mine to \u201cExport to Excel\u201d <\/strong>to make it easy to remember. Then, let\u2019s start playing with the fields that we want to include. <\/p>\n\n\n\n

What works well for Excel is to grab every possible field you might need. If you don\u2019t want to see that field in your Excel dashboard, simply exclude it from the report we\u2019ll build later on. It\u2019s better to include everything you might <\/em>want to work with.<\/p>\n\n\n\n

Excel works well with each row of data as its own \u201crecord.\u201d That means each line should represent a single time entry. See the screenshot below for a suggested data view that works well for a dashboard. One optional setting: There\u2019s a dropdown box labeled \u201cWhat information do you want to show?\u201d<\/em> Switch between options like Working Time <\/strong>only and Working Time and Absences <\/strong>based on the purpose of your report.<\/p>\n\n\n\n

\"A
Identify the timesheet data you want to export to Microsoft Excel.<\/span><\/em><\/figcaption><\/figure>\n\n\n\n

Finally, make sure to set the time period for your data. It’s best to download an entire year\u2019s worth of data. That way, you can simply replace the file that Excel uses for the dashboard. You won\u2019t risk duplicating records by downloading a week at a time, for example.<\/p>\n\n\n\n

\"When
Selecting the dates to prepare the timesheet export for Excel.<\/span><\/em><\/figcaption><\/figure>\n\n\n\n

Now, it\u2019s time to download our finished report. Click on the CSV <\/strong>button. <\/p>\n\n\n\n

\"You'll
Download the CSV to a designated timekeeping folder.<\/span><\/em><\/figcaption><\/figure>\n\n\n\n

After that, create a new folder, then place your downloaded CSV file in it. We\u2019ll tell Excel to watch this folder for timekeeping data.<\/p>\n\n\n\n

2. Use Power Query in Excel to import data from a folder and automate the process<\/h3>\n\n\n\n

To work with the data in Excel, we need to point it to our Beebole data<\/strong>. We\u2019re going to use a feature called Power Query <\/strong>to point Excel to a watched folder and keep the time report dashboard up to date<\/strong>.<\/p>\n\n\n\n

Sure, you could simply copy and paste your timesheet data into the workbook. But this takes extra time to update and can be cumbersome. Remember, we want to automate this reporting process as much as possible<\/strong>.<\/p>\n\n\n\n

To use Power Query to get your timesheet data, open Excel and choose Data > New Query > From File > From Folder. <\/strong>Then, browse to a folder on your computer.<\/p>\n\n\n\n

\"This
Use Power Query to import data from the selected folder.<\/span><\/em><\/figcaption><\/figure>\n\n\n\n

Now, point Excel to the folder that you created for your timesheet data. You\u2019ll see a preview of the files that Excel uses in the future. Click the Combine and Load to <\/strong>dropdown so that Excel will automatically roll together all timesheet data you keep in the folder.<\/p>\n\n\n\n

\"This
Automatically roll together all timesheet data that you keep in the folder for your time tracking dashboard.<\/em><\/mark><\/figcaption><\/figure>\n\n\n\n

Excel will then show you a preview of the data rows that will be included in your report. Press OK <\/strong>to confirm.<\/p>\n\n\n\n

\"Excel
Revise the preview before clicking OK.<\/span><\/em><\/figcaption><\/figure>\n\n\n\n

Finally, we need to use the Load To <\/strong>option to make Excel \u201caware\u201d of data so that we can work with it.<\/p>\n\n\n\n

\"An
Use the Load To option so Excel is aware of the data it needs to work with.<\/span><\/em><\/figcaption><\/figure>\n\n\n\n

With this approach, we have Excel watching a monitored folder for new timesheet data. All that you have to do is replace the CSV with a new download from Beebole and click Data > Refresh All, <\/strong>and Excel is always up to date. <\/p>\n\n\n\n

3. Use Power Pivot to manage large amounts of data<\/h3>\n\n\n\n

With the previous steps, we\u2019ve laid the groundwork to work with our timesheet data. Excel is now watching a folder for the latest file and is ready for your data.<\/p>\n\n\n\n

Now, we\u2019re going to use a PivotTable to work with this data and put it into an interactive report<\/strong>. Specifically, we\u2019re going to use Power Pivot, <\/strong>which magically works with the data that lives in the watched folder.<\/p>\n\n\n\n

Power Pivot is an Excel add-in<\/strong> which can handle large volumes of data (millions of rows) from various sources and all of this within a single Excel file. According to Microsoft: “Power Query and Power Pivot complement each other. Power Query<\/a> is the recommended experience for discovering, connecting to, and importing data. Power Pivot allows for powerful data analysis and data modeling in Excel<\/a> and is great for modeling the data you\u2019ve imported. Use both to mold your data in Excel so you can explore and visualize it.”<\/em><\/p>\n\n\n\n

To add a pivot, go to the tab labeled Power Pivot > Manage<\/strong>. Next, click on PivotTable<\/strong> to place it in your Excel workbook. This is the gateway to working with our timesheet data.<\/p>\n\n\n\n

\"This
Once the Power Pivot add-in is installed, you can create a collection of tables with relationships.<\/span><\/em><\/figcaption><\/figure>\n\n\n\n

So far, we\u2019ve taken steps that add a table that hooks to our timesheet data<\/strong>. Now, we can start building out our report.<\/p>\n\n\n\n

4. Build the timesheet report with a PivotTable<\/h3>\n\n\n\n

We\u2019re going to build a PivotTable based on our timesheet data. This is one of Excel\u2019s most powerful features to work with data. In short, a Pivot helps you aggregate and summarize data in an easy-to-understand format.<\/p>\n\n\n\n

Click inside the PivotTable box on the left side to show the PivotTable Fields <\/strong>menu. Think of this menu as a report builder. It points to your timesheet data, and you can drag-and-drop it into a report that stays up-to-date.<\/p>\n\n\n\n

You\u2019ll see rows for each of the data in the list, like Date, Person, Company, <\/strong>and Project. <\/strong>You\u2019ll recognize these as the columns in our original dataset.<\/p>\n\n\n\n

Below the field list, you\u2019ll see four important boxes: Filters, Columns, Rows, <\/strong>and Values. <\/strong>Drag-and-drop fields into these boxes to show data in the report. The box that you drag a field to influences how it shows in the report.<\/p>\n\n\n\n

Basically, if you drag a field like \u201cProject\u201d into Rows, <\/strong>it shows each project on its own row. Or, if you dragged it into Columns, <\/strong>each project shows in its own column. You can build a report in the format you need by dragging fields to the corresponding boxes.<\/p>\n\n\n\n

Here\u2019s a suggestion for a sample dashboard.<\/p>\n\n\n\n

Drag:<\/p>\n\n\n\n