We just published a course on the freeCodeCamp.org YouTube channel that will teach you the top Excel formulas and functions you need to know. By the end of this course, you'll be able to apply these functions confidently, speed up your work, and eliminate confusion when choosing the right formula. Grant Huang developed this course.

The course starts by covering the fundamentals of cell referencing—an essential concept for writing effective formulas. You'll learn about relative referencing, which allows formulas to adjust dynamically when copied to other cells, and absolute referencing, which locks specific cells to prevent unwanted changes. Understanding these concepts is key to avoiding common mistakes and ensuring accurate calculations in your spreadsheets.

From there, you'll dive into essential Excel formulas such as:

• Basic Arithmetic Operations (addition, subtraction, multiplication, and division)

• SUM and AVERAGE for quick calculations

• IF Statements for decision-making within formulas

• VLOOKUP and HLOOKUP for searching data efficiently

• INDEX and MATCH for more advanced lookups

• TEXT functions to manipulate and clean up data

• DATE and TIME functions for managing schedules and timelines

• LOGICAL functions like AND, OR, and NOT for conditional logic

You'll also learn shortcuts and best practices to make your workflow more efficient. By applying these techniques, you can automate repetitive tasks, analyze data more effectively, and present your findings in a clear and professional manner.

Whether you're new to Excel or looking to refine your skills, this course is designed to be practical and easy to follow. Watch the full course below (75 minutes) or on the freeCodeCamp YouTube channel.

We talk about:

• Eamonn’s love of coffee and how he bought VHS tapes to learn latte art

• How he finds time to expand his skills in between running coffee shops and ultra-marathoning

• How he used spreadsheets to automate the logistics of running coffee shops

• How he balances being a musician and writer with the practical realities of providing for a family of 6

Can you guess what song I'm playing in the intro?

Support comes from the 11,113 kind folks who support freeCodeCamp through a monthly donation. Join these kind folks and get involved in our mission by going to donate.freecodecamp.org

CORRECTION: Vincent van Gogh was supported by his younger brother – not his brother in-law. van Gogh never married so he never had a brother in law. I'm not sure why I thought that. Also, he seems to have sold more than one painting in his life (as many of us were taught in school), but nowhere near enough paintings to support himself as an artist.

Links we talk about during our conversation:

Eamonn's freeCodeCamp articles: https://www.freecodecamp.org/news/author/sieis/

Eamonn's YouTube channel: https://www.youtube.com/@eamonncottrell

Excel-based esports: https://www.youtube.com/watch?v=N2QC6VQXo8U

Ultra Marathons: https://www.youtube.com/@runtired

Got Sheet: https://www.gotsheet.xyz/

Progress and Perfection: https://www.progressandperfection.com/

Eamonn on LinkedIn: https://www.linkedin.com/in/eamonncottrell/

Eamonn on Twitter: https://x.com/EamonnCottrell

It’s a lifesaver for countless financial professionals, data analysts, and teachers. But it’s also one of those programs that virtually everyone in any role can benefit from learning.

A handful of shortcuts can go a long way in increasing your productivity (and enjoyment) while using Excel.

In this article, I’ll detail some of the many shortcuts that I have found helpful throughout my studies and career.

Excel Shortcuts We’ll Cover:

• How to Execute Shortcuts in Excel

• Shortcut to Create a Table

• Shortcut to Create a Pivot Table

• Shortcut to AutoFit Column Sizes

• Shortcut to Open Format Cells

• Shortcut to Center Contents of Cell

• Shortcut to Fill Color

• Shortcut to Fill Contents Down (or Right)

• Shortcut to Show or Hide Gridlines

• Shortcut to Show all Formulas

• Shortcuts for Navigation in Excel

• Shortcut to Open the AutoFilter Menu in Excel

• Shortcut to Create a Slicer in Excel

• Shortcut to Create Checkboxes in Excel

• Shortcut to Create Charts in Excel

• Other Notes

• More Shortcuts

• Got Sheet

Here’s a video walkthrough of everything we’ll cover in this article:

How to Execute Shortcuts in Excel

In Excel, the more you can learn to do without touching your mouse, the better. Often, by keeping your hands on your keyboard, you can save a lot of time in each project.

As such, virtually every command imaginable is available as a keyboard shortcut.

The basic ones like CTRL + S for save and CTRL + C for copy are present. But Excel goes a step further…

The real power comes from the alt shortcuts. By pressing sequences of keys that typically start with alt, almost all of the actions in the Ribbon become available.

By simply pressing the alt key, all of the shortcut sequences available from the current view become highlighted in yellow on the ribbon:

Incidentally, some of the shortcuts’ sequences are separated by commas, while at other times two letters appear next to each other. Their execution is the same. Simply press them in sequence.

Below, you’ll find some of my favorites, but keep in mind that you can easily view all the available shortcuts at any time by pressing alt and then continuing to press the appropriate keys for the corresponding tabs and actions.

Shortcut to Create a Table

Keyboard Shortcut: ctrl + t

Tables in Excel are often the preferred format to begin manipulating and visualizing data. As long as the active cell is inside your data range, pressing ctrl + t will pop up a dialog and automatically select the data range to be used for the table.

It is adjustable if Excel gets a column or row wrong, and you can also toggle table headers on or off from this initial box.

Shortcut to Create a Pivot Table

Keyboard shortcut: alt + n, v, t

Want to appear a lot smarter than you are? Learn the basics of pivot tables in ten minutes. Your coworkers will remain impressed for weeks.

To create a pivot table, simply click somewhere in your data range and press ALT + n, v, t.

Excel is smart enough to recognize the data range you likely want included even if it isn’t already formatted as a table.

A dialog box will pop up for you to confirm the data range and location for the pivot table.

Shortcut to AutoFit Column Sizes

Keyboard Shortcut: alt + h, o, i

Ever get tired of resizing your columns so that the text in the cells doesn’t clip or spill? You can always double click the column boundary headings. This resizes the column to fit the widest cell’s contents.

The keyboard shortcut is much more efficient and allows you to autofit multiple columns in one fell swoop. Simply click and drag the column boundaries to select any number of columns. Execute the alt + h, o, i shortcut and all the columns in your active range will autofit their width.

You can also autofit cells individually by selecting one or more active cells and performing the same shortcut.

Shortcut to Open Format Cells

Keyboard Shortcut: CTRL + 1 or alt + h, fm

Unless you are content with mediocrity, you’ll be formatting the cells in your spreadsheet at some point to create more readable, user-friendly content.

The simplest shortcut to open up the Format Cells window is CTRL + 1, although if you want to flex your dexterity, alt + h, fm will get you there as well.

Shortcut to Center Contents of Cell

Keyboard Shortcut: (horizontal) alt + h, a, c,

(vertical) alt + h, a, m

We have it easy compared to web developers. There seems a never-ending supply of articles and videos reminding developers how to center divs.

In Excel, we need only remember two quick shortcuts.

• For horizontal centering: alt + h, a, c

• For vertical centering: alt + h, a, m

There are other shortcuts for left, right, top and bottom alignment, but most of the time when we change the original alignment, it’s to center it.

Shortcut to Fill Color

Keyboard Shortcut: alt + h, h

For a quick highlight, it takes precious seconds to mouse up to the fill color icon. Pressing alt + h, h quickly toggles the color selection open.

Once it’s open, you can leave your mouse to the side and arrow down to your favorite color

Shortcut to Fill Contents Down (or Right)

Keyboard Shortcut: (down) CTRL + D, (right) CTRL + R

One of the most powerful features of Excel is the ability to drag formulas and functions down or across many cells, effectively reproducing a single calculation many times on different pieces of data.

By typing a formula in cell A8 in the image below, we can then highlight A8 and drag down to our heart’s content. Then, by pressing CTRL + D, the formula will be copied down into every highlighted cell.

By default, it will also preserve the relative reference of the cell. In other words, the next cell will contain the formula A7 + A8 and then A8 + A9, and so on.

Shortcut to Show or Hide Gridlines

Keyboard Shortcut: alt + w, vg

The mark of a real data analyst is not the quality of their reports, but the precision of their workbook. Gridlines have got to go. If you need lines, you can add borders.

Toggle off the gridlines with alt + w, vg.

And if you need those borders immediately, highlight your data range and press alt + h, b to open up the border menu. If you simply need all borders, alt + h, b, a will do the trick

Shortcut to Show all Formulas

Keyboard Shortcut: CTRL + ~

You’ll likely never lose control of a workbook.

But in the event you access one of your less rigorous colleague’s workbooks and need to see what functions they’ve Frankensteined together, press CTRL + ~ to display all the functions instead in the spreadsheet.

Shortcuts for Navigation in Excel

Keyboard Shortcuts: CTRL + arrow keys (and others)

Navigating the grid can be very fast with the keyboard. Pressing CTRL + the arrows, the home and the end buttons will warp you all over the active sheet.

Using the arrows and CTRL, you go to the last nonblank cell in the row or column.

Using CTRL + home or end, you go to the beginning and the end of the workbook, respectively. (When inside a table, home and end take you to the beginning and end of the table only.)

Shortcut to Open the AutoFilter Menu in Excel

Keyboard Shortcut: alt + down arrow

Another superpower in Excel is the ease with which we can filter and sort large pieces of data. To access the AutoFilter Menu quickly, press alt + down arrow while in the header for the column you’d like to filter.

Shortcut to Create a Slicer in Excel

Keyboard Shortcut: alt + n, sf

For an even more user-friendly method of sorting, you can insert a slicer directly onto the spreadsheet by pressing alt + n, sf.

Shortcut to Create Checkboxes in Excel

Keyboard Shortcut: alt + n, cb

If you’re anything like me, you’ll find a way to use checkboxes in almost every spreadsheet you create. They’re extremely useful for toggling selections on and off in a workbook, and as of June 2024, Excel has made them available in production Excel.

Press alt + n, cb to insert a checkbox in a cell.

Shortcut to Create Charts in Excel

Keyboard Shortcut: alt + n, r

There are a ton of chart types in Excel. To quickly open up the recommended charts dialog box, we can press alt + n, r.

Or, if we know we want a specific type of chart, there are multiple options to shortcut straight to them, like alt + n, C1, alt + n, N1, alt + n, SA and so on.

More Shortcuts

There are a zillion shortcuts available in Excel. If you want to check out the full list, you can find a current version maintained by Microsoft here.

Got Sheet

Come join my free newsletter, Got Sheet. I show people how to get good at spreadsheets every week.

You can find me over on YouTube as well.

We just published course on the freeCodeCamp.org YouTube channel that is a comprehensive guide to Excel data visualization and dashboard creation. This course is designed to equip you with the skills needed to transform raw data into insightful visualizations and interactive dashboards using Microsoft Excel. Mihir from Office Tech Skill developed this course.

Course Overview

This tutorial covers a wide range of Excel chart types and customization techniques, providing you with the tools to create engaging and informative dashboards. This course offers valuable insights into making the most of Excel's powerful features.

• Introduction: Get an overview of what you'll learn and how these skills can be applied in real-world scenarios.

• Chart Types: Explore various chart types, including Column, Bar, Line, Pie/Doughnut, XY Scatter Plot, Area, Radar, Stock, Histogram, Pareto, Waterfall, Box & Whisker, Treemap, and Map charts. Each section provides detailed instructions on when and how to use these charts effectively.

• Chart Customization: Learn how to organize data, choose the best chart type, and apply styles to enhance your visualizations. You'll also discover how to adjust chart elements and labels, enhance data labels and gridlines, and apply color themes and palettes.

• Advanced Techniques: Dive into more complex features like utilizing combination charts and secondary axes, incorporating trendlines, and applying various chart elements. This section also covers best practices for chart customization to ensure your visualizations are both effective and aesthetically pleasing.

• Excel Dashboard Creation: Transition from individual charts to comprehensive dashboards. Learn how to plan your dashboard, clean and organize data, build relationships, and write DAX (Data Analysis Expressions) for advanced data manipulation.

• Interactive Dashboards: Discover how to incorporate interactivity into your dashboards, making them more engaging and user-friendly. This includes building pivot tables, charts, KPIs (Key Performance Indicators), and incorporating interactive elements.

• Building Your Dashboard: Put all the pieces together to create a fully functional Excel dashboard. This section guides you through the process of integrating all the elements you've learned into a cohesive and interactive dashboard.

• Free Excel Course: As a bonus, access additional resources and materials to further enhance your Excel skills.

Why Excel?

Excel remains one of the most widely used tools for data analysis and visualization due to its accessibility and powerful features. Mastering Excel's charting and dashboard capabilities can significantly enhance your ability to communicate data insights effectively, whether for business presentations, academic projects, or personal use.

Get Started Today

With step-by-step guidance and practical examples, this course will help you become proficient in one of the most essential skills in today's data-driven world.

Watch the full course on the freeCodeCamp.org YouTube channel (1-hour watch).

Well, new is a bit of an overstatement. Excel has had proper tables for many, many years, and it's been a point of contention in the spreadsheet community.

In this article, I'll break down what exactly tables are, why they're important, and then see how Google Sheet's new tables stack up against Microsoft Excel's.

Here is a video walkthrough of everything we'll cover:

What's a Table?

A table is a way of structuring and formatting data in a spreadsheet. It groups together rows and columns of data so that they can be more easily filtered, grouped, and analyzed.

Many people would look at the following bit of data and wrongly assume that it's already in a table.

Data in Excel

This is merely well organized rows and columns of data in Excel. Each column is a separate category of information, that is ids, names, emails, job titles, and salaries.

Each row represents one entry of that data. So, you'd put your id, name, email, job title and salary going left to right in a row.

Simple, yes?

A table contains all the same data, but by formatting it as a table we can unlock a whole lot of additional functionality.

The first of which is the appearance itself. When we create a table, Excel immediately colors our data with a dark header row and bands of alternating colors.

Table in Excel

Sheets does the same thing, as we can see below.

Table in Google Sheets

So, a table is simply a way of managing and grouping data more easily. But it goes much further than just formatting, as we'll see.

Why are Tables Important?

Tables help reduce errors. When dealing with data, we are always making sure the data is clean and that we don't have errors in our formulas.

Tables help keep things orderly simply by being structured and formatted well. But as we'll see in the formula section in a moment, they also allow us to reduce errors in formulas by dynamically calculating things for us

How to Create a Table in Excel and Sheets

In Microsoft Excel, creating a table is as easy as clicking anywhere in the data range and pressing CTRL + T. Immediately, Excel will predict the data range for the table and ask you to confirm this.

Excel table data range

Alternatively, you can find the same create table option from the Insert Menu in the Ribbon at the top.

Excel insert menu

Over in Sheets, you'll need to either right click in a cell in the data range, or select the option from the Format menu to Convert to Table.

Convert to table options in Google Sheets

One caveat in Sheets: if you right click in a cell, you have to select the whole data range for it to convert to a table. Whereas, if you select Format - Convert to table from the menu, it is (like Excel) smart enough to predict the whole data range.

A small thing. But Excel takes the prize for ease of creation.

Convert to table in Google Sheets

⭐Winner: EXCEL

How to Format Tables in Excel and Sheets

As we saw initially, some formatting is done as soon as we create a table.

From here, though, both programs allow for further customization.

In Sheets, we can select the dropdown in the top left next to the table name to access a few options immediately. For the most part, we can simply change the alternating colors of the table.

Formatting options in Google Sheets

If we select Custom, it opens up the full alternating colors menu that is also accessible through the Format menu. This gives us more control over the colors, but it's all aesthetic.

Alternating colors menu in Google Sheets

Meanwhile in Excel, we have the same options with a few more toggle selections for styling. For instance, we can check the first column to bold the text in the id column or we toggle between banded columns and/or rows.

And on the far right of the Table Design tab in the Ribbon, there are a ton of prebuilt styles that we can toggle on and off.

Table design in Excel

Both programs give plenty of options here, and this is mostly to make the tables look good. But Excel comes out on top with more options.

⭐Winner: EXCEL

How to Sort Tables in Excel and Google Sheets

In both programs, there is a dropdown toggle button in each of the header row's cells. Selecting this in Excel allows us to sort ascending or descending...or even by color.

sort in excel

For instance, if we have some of our data using a blue font color, we can actually sort it by that color:

Sort by color in Excel

What about Google Sheets? Yep, same deal there. It will also detect when different colors are used and allow you to do the same type of sorting.

Sort by color in Sheets

Excel does have a Custom Sorting dialog box that can drill down into more detail. For instance, you can add levels of sorting.

Using our color example, we can first sort by the blue font colors in the email color and then by the red font colors in the job title column.

Multiple column sorting in Excel

Google Sheets can do the same thing, but not from the header drop downs. The header drop down sorting is restricted to one row at a time in Sheets.

But, if you select the entire table's data range and then Data - Sort Range - Advanced range sorting options, you are able to sort by multiple columns in Google Sheets.

Advanced sorting in Google Sheets

Sheets' advanced sorting is not as powerful as Excel's, though. You are only able to sort ascending or descending by value. Excel takes the cake on this one by a hair.

⭐Winner: EXCEL

How to Filter Tables in Excel and Google Sheets

Filtering works exactly the same as sorting. In both programs, click the dropdown selector in the header row to see the options for filtering.

In both programs, we have the same options. We can filter by color just like in our sorting. We can filter by values by either selecting all, none, or individual values. And we can filter by condition.

Here's Google Sheet's menu:

Filtering in Google Sheets

And here's Excel's menu. All the same options are available. Both programs allow for custom filter formulas to be entered as well.

Filtering in Excel

⭐Winner: TIE

How to Use Tables in Formulas in Excel and Google Sheets

One of the big reasons to use tables lies in formulas. Whether you use Excel or Sheets, you are likely taking advantage of built-in functions and the ability to create custom formulas for analyzing your data.

By holding your data in a table, your formulas can reference that data dynamically.

Meaning, if you add rows of data to your table, any formulas referencing those table values will update automatically.

The risk of breaking things by adding data decreases dramatically with the use of tables.

Here's a simple example. If we wanted to combine the first and last names into one cell, we could concatenate them with this formula =Salary[first_name]&" "&Salary[last_name].

In Excel, we reference a table by its name, in this case, Salary. Then within brackets, we reference a column name, [last_name].

Spill formula in Excel

We can do the exact same in Sheets.

Formula referencing in Sheets

There's one powerful difference, though. In Excel, the formula will spill down. Meaning, we only have to write it once at the very top, but because it sees that we're referencing values in a table, it will spill down to every row in the table.

In Google Sheets, we still have to drag the formula down.

Now, sometimes, we may not want things to spill down. In this case we can use different syntax in Excel. Instead of the column name within brackets, we can add an @ sign and another set of brackets. This tells Excel to only make the calculation on one row:

Formula referencing in Excel

Excel flexes on this one. It's much more powerful to use tables in formulas in Excel than in Sheets.

⭐Winner: EXCEL

How to Change Table Range in Excel and Google Sheets

What if we want to extend our table or remove data from it? Both Google Sheets and Excel allow us to do this easily.

Say we want to add a column for the full name of a person. In both programs, if we simply type in full_name in G1 to the right of our last column, that column will become a part of the table's data range.

Anytime we type in an adjacent column or row to our table data, the programs will assume the table needs to extend to include it.

Then, we can use a version of the formula from our previous example to insert the full names. Now that we are inside of the table, though, it's not necessary to include the title of the table in our formula.

Now, all that's needed in Excel is =[@[first_name]]&" "&[@[last_name]].

Reference table columns in Excel

For Google Sheets, it's the same inside the table as outside it: =Table2[first]&" "&Table2[last]. Sheets also requires us to drag the formula down. It does not handle spilling like Excel (yet).

Reference table columns in Sheets

To add columns within a table, we can right click the column name and select insert.

Insert column in Excel

Google has a slight edge here in that you can select whether to insert to the left or the right, whereby Excel only inserts to the left.

Insert column Google Sheets

Inserting rows is exactly the same. Excel allows for inserting rows above, while Sheets allows you to select above or below.

Inserting rows Google Sheets

In both programs, deleting rows and columns is as simple as selecting the row(s) or column(s), right clicking, and selecting delete.

In Excel you have the added benefit of a keyboard shortcut. CTRL + - will delete the selected rows or columns.

Both programs will also allow you to convert a table back to a regular data range. In Excel, it's the Convert to Range button in the Table Design tab of the menu

Convert to Range option in Excel

And in Google Sheets, it's the Revert to unformatted data option from the table dropdown menu.

Revert to unformatted data option in Sheets

⭐Winner: TIE

How to Add a Total Row in a Table

There's a good chance you'll want to total up the amounts in a column. How easy is this to add in a table?

You can do it in both programs, but...

Excel makes it incredibly easy. There's a toggle option for this in the Table Design menu in the Ribbon. Toggle this on, and a Total row is automatically included and calculated at the bottom.

Total row in Excel

Can you do the same in Sheets?

Yes, you've just got to do it yourself.

Total row in Sheets

⭐Winner: EXCEL

Who Wins?

Well, it's no surprise that Excel comes out on top. Sheets users (myself included) have a lot to be excited about with the ability to finally create proper tables. By and large, the functionality is just as powerful as Excel.

And much like many features compared between the two programs, Sheets can probably get the job done for most use cases.

Excel, as per normal, simply does more and does it a little bit better.

I'm Eamonn, and I'll help you get good at spreadsheets. Join my free newsletter, Got Sheet, here.

Microsoft Excel is a very powerful tool that you can use to analyze and visualize data.

In this tutorial, you will learn how to build a simple Excel Dashboard that visualizes important data from a large dataset.

The dataset we'll be working with is the transaction records of a super store for a period of four years. Our goal is to gain important insights from the dataset and visualize those insights graphically with Microsoft Excel.

This tutorial is tailored for those who are already familiar with Excel. In it, you will learn:

• How to format dates in Excel using the TEXT function.
• How to sort the entire data set.
• How to create multiple Pivot Tables on the same worksheet
• How to create charts based on the Pivot Table.
• How to create slicers to filter data, and finally,
• How to use conditional formatting.

In order to make the most of this tutorial, I have provided a data set for you to use. You can download it here.

If you're new to data analysis and visualization with Excel, the freeCodeCamp website contains a lot of tutorials for beginners. Here is a link to some articles on Excel from beginner to advanced if you need to brush up on your skills.

Our Dataset

After downloading the Excel file and opening it, you will notice one thing instantly: The data is huge! It is a data set containing the transactions of a superstore for a period of four years (from 2014 to 2017).

This Excel file is made up of 9995 rows and 21 columns. We can’t gain any useful insights from this data set just by staring at it, which is why we need to analyze and visualize it.

Screenshot of the dataset

The easiest way to do this is by using Pivot Tables. A Pivot Table is one of Microsoft Excel's powerful tools you can use to calculate, analyze and summarize data. It helps you see comparisons, trends, and patterns in your data and you will learn how to use it in this tutorial.

Before we continue, let's take a look at the data set again: column C contains the order dates for each product sold. The order date column is important in this tutorial, because we need to sort the entire dataset based on the dates each customer ordered a product.

How to Format Dates in Excel Using the TEXT Function.

In our dataset, the dates are arranged haphazardly and the date formatting makes it hard to tell exactly when each customer ordered a product. To fix this, we'll use the TEXT function to convert the dates to text.

Before you do this, insert a new column beside column C. This will be your new column D and where the newly formatted dates will reside. You can give it a new name – in my own case, I named it “Formatted Dates.”

After that is done, insert the formula in the screenshot below and press enter:

Formula to convert dates into text

Or just copy and paste the formula below:

=TEXT(C2, "d mmmm yyyy")

Of course, the formula only works for the first cell in the column. To repeat it across the entire column, just double-tap the little node at the right end of the cell when it's highlighted (as you can see in the below screenshot):

Tap on the node to fill the remaining cells in the column automatically

Now, you should have dates in a more readable format across column D.

The dates in a more readable format

For the purpose of this analysis, we need to extract the years from the dates we have just formatted. To do this, create a new column beside column D, this will be your new column E. Name the column as you like, but in my case, I named it “Year”.

Now use the formula in the screenshot below and press enter:

Formula to get the year from the date

Or simply copy and paste the formula below:

=RIGHT(D2,4)

This will extract the year at the end of the date. Double-tap the node at the end of the cell to repeat it across the entire column. You should get a similar result as shown in the screenshot below:

Screenshot showing the extracted year

How to Sort the Entire Dataset

The next step in this tutorial is to sort the entire data set based on the newly extracted Year column.

Highlight the entire Year column and go to the sort tab, as you can see in the screenshot below:

Sorting the dataset

Leave all options at default and press OK.

Now the entire data set should be sorted.

How to Create Multiple Pivot Tables on the Same Worksheet

The next step in this tutorial is to create the pivot tables we'll use to make sense of this data.

Go to the insert tab and click on Pivot Table. A dialogue box should pop up asking you for the range of the data you want the pivot table to be created from – which by default is the entire data set.

Creating a Pivot Table

You will also have options to use the existing worksheet to create the pivot table or a new worksheet. Select the new worksheet option and press OK.

You should have a result like you can see in the screenshot below:

The Pivot Table Worksheet

From here things start to get interesting.

For this exercise, I am only interested in three things:

• The category of products with the highest sales for each of the four years (2014 to 2017)
• Which sub-category has the highest sales
• Which region drives the most sales to this super store.

I also want to visualize this information using graphs and charts. Thankfully, Excel has the tools we need to achieve all these.

In case you don’t know how to create a Pivot Table, here is a comprehensive freeCodeCamp article teaching you how to do that.

So we need to create three Pivot Tables, one for the sales by category, one for the sales by sub-category, and one for the sales by region.

To create the first table, simply select sales and category from the PivotTable Fields at the right hand side of your screen.

After creating the first Pivot Table, to add another table on the same worksheet, simply go to the insert tab and click on Pivot Table, then go to the original worksheet with the dataset and highlight everything, click OK.

Repeat the step to create the last table for the sales by region.

You should have three tables as shown in the screenshot below:

Screenshot showing all three Pivot Tables

How to Create Charts Based on the Pivot Table

The next step is to create the charts to visualize what is represented in the Pivot Tables. This is quite straightforward – just highlight each table and click the insert tab to insert the chart of your choice.

I used a pie chart for the sales by category table, and column charts for the other two tables on the worksheet. But you can use whatever chart(s) you want.

If you follow my steps exactly, you should have a result similar to the screenshot below:

Screenshot showing all the charts

How to Create Slicers to Filter Data

The next step is to use slicers to filter each table by the year so that we can see what the total sales for each category, sub-category, and region are by year.

To do this, highlight each table and click on the analyze tab, then click on the slicer button as shown in this screenshot:

Creating a slicer

You'll see a list of options. Look for Year, and click it.

Now you should have a slicer for the years 2014 to 2017. Clicking on the corresponding button for each year will filter the table and show the sales for the year. Here is what it should look like:

Screenshot showing all the slicers

How to Use Conditional Formatting

Finally, you can use conditional formatting to indicate the highest and lowest values on each table. You can play around with this as you like.

To use conditional formatting, click on the conditional formatting tab, and use this screenshot as a guide:

Using conditional formatting

Highlight the values you would like to use conditional formatting for. I used the color scales option to indicate the highest and lowest values by different colors, then I used the data bars option for the sales by sub-category table.

If you did exactly what I did, you should have something close to the screenshot below:

The completed dashboard

Wrapping Up

This little project we have built together can be expanded to get more insights into the data set, this can prove very useful when making business decisions.

This dashboard we have built has revealed that the best selling category is electronics and the best selling sub-category is phones. I also learned that the most sales came from the western region just by glancing at this dashboard.

I hope this tutorial has been helpful. Here is the link to the completed version of this project – you can compare it with what you have built and see how well you did.

If you have any questions, you can reach out to me on X and LinkedIn. Thanks for reading.

This step-by-step guide will help you grasp the fundamentals of working with databases, data manipulation, and email communication, all while automating these processes with a Python script.

Business Context

Imagine you're a part of an organization where your managers expect a weekly report filled with valuable insights. But creating this report is far from a straightforward task.

To get the information you need, you have to manually run ten different database queries, gather the results, and then meticulously compile them into an Excel spreadsheet. It's a time-consuming and error-prone process that can leave you exhausted.

In this scenario, wouldn't it be a game-changer if Python could take the reins and handle this entire process for you?

Picture this: Every week, without any manual intervention, Python seamlessly extracts the required data, compiles it into a neat Excel sheet, and even sends it off to your managers like clockwork.

This tutorial will help you learn how to do this. I'll walk you through the steps to automate this process, making your weekly or monthly reporting a breeze, and freeing you up to focus on more critical tasks.

Table of Contents

1. Prerequisites
2. How to Set Up Your Virtual Environment
3. How to Set Up Your Sample Database
4. How to Set Up Logging and Environment Variables
5. How to Extract the Data From the Database
6. How to Structure the Booking Data with the BookingInfo Class
7. How to Convert the Data into an Excel Sheet
8. How to Combine the Functionalities
9. How to Send an Email with the Bookings Data Report
10. How to Test the Flow
11. How to Schedule the Application
12. Wrapping Up

Prerequisites

Before you get started, make sure you have the following:

1. Python installed on your computer. You can download Python from Python.org.
2. Basic knowledge of the Python programming language
3. Familiarity with sending emails in Python
4. PostgreSQL installed on your computer. You can download PostgreSQL from here.

How to Set Up Your Virtual Environment

Before you start coding, you'll need to make sure you have all the necessary tools and libraries installed.

To ensure that you have a clean and isolated environment, you'll create a virtual environment using venv.

Create a project directory and navigate to it in the terminal:

mkdir report-automation
cd report-automation

Create a virtual environment named env using the following command:

python -m venv env

Python now ships with the pre-installed venv library to create virtual environments.

Activate the virtual environment like this:

source env/bin/activate

Note: if you're on Windows, you'll need to use source env/Scripts/activate to activate the environment.

You should see (env) in your terminal prompt, indicating that the virtual environment has been activated.

How to Install the Required Libraries

Now that you've created the virtual environment, you can install the following libraries:

• psycopg2: Python adapter for PostgreSQL, enabling Python applications to interact with PostgreSQL databases.
• pandas: A versatile data manipulation and analysis library for Python, ideal for working with structured data.
• xlsxwriter: Python module for creating and formatting Excel (XLSX) files, useful for generating reports and spreadsheets.

To install the libraries, run the following command:

pip install psycopg2 pandas xlsxwriter

How to Set Up Your Sample Database

In this section, I will guide you through setting up a demo database named "airlines" that we'll use throughout this tutorial. The database includes three tables: bookings, flights, and airports_data.

I will provide you with an SQL script file named airlines_db.sql that creates the database and populates it with sample data. To set up the database, you will need PostgreSQL installed on your system.

Download and Install the Database

1. Download the SQL script file "airlines_db.sql" from here.
2. Open your terminal or command prompt.
3. Use the following command to install the database. Make sure you have the PostgreSQL command-line tools installed and that you can access the psql command. Replace postgres with your PostgreSQL username if it's different.

psql -f airlines_db.sql -U postgres

This command will execute the SQL script and create the "airlines" database with the bookings, flights, and airports_data tables.

Schema Description

The main schema in the database is bookings. Let's take a closer look at the tables in the "airlines" database:

Schema Diagram

Table bookings.bookings

The "bookings" table is designed to store crucial information about bookings made for flights. Each booking is uniquely identified by the book_ref, which is a character(6) field. The total_amount field is a numeric(10,2) type and represents the total cost of the booking.

To track the booking date and time, the table includes a book_date field of type bigint. This table serves as the central repository for booking data and is essential for tracking passenger reservations, costs, and booking dates.

Table bookings.flights

The "flights" table is dedicated to capturing comprehensive details about flights, including information about their statuses, scheduled and actual times of departure and arrival, and other important flight-related data.

The primary key for this table is the flight_id, an integer identifier. Each flight is associated with a specific flight number denoted by the flight_no field, a character(6) type.

To understand the flight's origin and destination, the departure_airport and arrival_airport fields store the departure and arrival airport codes as character(3) types, respectively.

The status field is a character varying(20) that records the flight's status, which must be one of 'On Time,' 'Delayed,' 'Departed,' 'Arrived,' 'Scheduled,' or 'Cancelled.' The table also includes fields for scheduled departure and arrival times (scheduled_departure and scheduled_arrival) and actual departure and arrival times (actual_departure and actual_arrival).

Furthermore, this table establishes two essential foreign keys: flights_arrival_airport_fkey and flights_departure_airport_fkey, which link to the airport_code in the "airports_data" table. This establishes connections between flights and their respective departure and arrival airports.

Table bookings.airports_data

The "airports_data" table serves as a repository for data related to airports and their geographic locations. Each airport is identified by a unique character(3) code stored in the airport_code field, which also serves as the primary key.

The timezone field, of type text, records the specific timezone of the airport, providing essential information for scheduling and operational purposes. The airport_name field is a character varying type that holds the name of the airport. Additionally, the table includes the city field as a character varying type, indicating the city in which the airport is situated.

These details enable the "airports_data" table to provide a comprehensive overview of airport locations and information. This serves as a reference for the "flights" table through the flights_arrival_airport_fkey and flights_departure_airport_fkey foreign keys, facilitating the association between flights and their respective departure and arrival airports.

How to Set Up Logging and Environment Variables

In this section, we'll configure logging to provide informative messages and handle errors throughout the code. We'll also set up environment variables to securely store sensitive information and configuration parameters. These practices enhance code readability, maintainability, and security.

Logging Configuration

We will utilize Python's built-in logging module to configure a logging system. Logging is essential for tracking the execution flow of the code and capturing important information or errors.

The logging.basicConfig method is called to define the format of log messages and set the logging level to INFO.

import logging

logging.basicConfig(
    format="%(asctime)s | %(levelname)s : %(message)s", level=logging.INFO
)

• Format: The format parameter specifies the format of log messages. In this case, each log entry includes a timestamp, log level (for example, INFO, ERROR), and the actual log message.
• Log Levels: We set the logging level to INFO, which means the logger will record informational messages. You can also use higher severity levels, such as WARNING or ERROR, for more critical issues.

You can learn more about logging in Python in this tutorial.

How to Manage Environment Variables

We will create a .env file to manage environment variables. Environment variables are used to store sensitive information and configuration settings, allowing us to keep such data separate from the code.

In this case, we set environment variables for email credentials and database connection details.

export EMAIL=
export PASSWORD=
export EMAIL_PORT=587
export SMTP_SERVER=smtp.gmail.com
export DB_HOSTNAME=localhost
export DB_NAME=airlines
export DB_PORT=5432
export DB_USERNAME=postgres
export DB_PASSWORD=postgres

Here's a breakdown of the variables:

• EMAIL: The email address to be used for sending emails.
• PASSWORD: The password associated with the email account.
• EMAIL_PORT: The port for the email server (for example, SMTP server). The default is 587 for secure email transmission (TLS/SSL).
• SMTP_SERVER: The SMTP server address, often specific to the email service provider.
• DB_HOSTNAME: The hostname or IP address of the PostgreSQL database server.
• DB_NAME: The name of the PostgreSQL database.
• DB_PORT: The port number for connecting to the database (default is 5432 for PostgreSQL).
• DB_USERNAME: The username for authenticating with the database.
• DB_PASSWORD: The password for the database user.

Make sure you run source .env to load the environment variables.

By using environment variables, sensitive data like passwords and email credentials can be kept separate from the code, reducing the risk of accidental exposure or unauthorized access. The code can access these variables at runtime, ensuring security and flexibility in configuration.

How to Extract the Data From the Database

Let's start by setting the database configurations.

import logging
import os

logging.basicConfig(
    format="%(asctime)s | %(levelname)s : %(message)s", level=logging.INFO
)

DB_CONFIG = {
    "host": os.environ.get("DB_HOSTNAME"),
    "database": os.environ.get("DB_NAME"),
    "user": os.environ.get("DB_USERNAME"),
    "password": os.environ.get("DB_PASSWORD"),
}

The DB_CONFIG dictionary is used to store the configuration parameters for connecting to the PostgreSQL database. These parameters include the host, database name, username, and password. These values can be set through environment variables.

How to Connect to the Database

Before we extract the data from the database, we need to connect to our database. We will use the psycopg2 library to connect to the PostgreSQL database.

We will start by defining a DataExporter class that will contain methods to extract the database and generate the Excel sheet.

class DataExporter:
    def __init__(self):
        """Initialize the DataExporter with the database configuration."""
        self.db_config = DB_CONFIG

The class constructor initializes the DataExporter with the database configuration stored in the DB_CONFIG dictionary.

Next, let's define a method that connects to the database.

...
import psycopg2

...

class DataExporter:
    def __init__(self):
        """Initialize the DataExporter with the database configuration."""
        self.db_config = DB_CONFIG

    def __connect_to_database(self) -> None:
        """
        Establish a connection to the PostgreSQL database.

        Raises:
            Exception: If a connection to the database cannot be established.
        """
        try:
            self.conn = psycopg2.connect(**self.db_config)
            self.cursor = self.conn.cursor()
            logging.info("Connected to the database")
        except Exception as e:
            logging.error(
                "Failed to connect to the database with error: %s", e)
            raise

The __connect_to_database private method is responsible for establishing a connection to the PostgreSQL database. It uses the psycopg2 library to create a connection and a cursor for executing SQL queries. If the connection fails, it logs an error and raises an exception.

You can learn more about exception handling in Python here.

How to Fetch Data from the Database

Now we'll define another private method that connects to the database and fetches the total number of bookings and the total amount from the database.

from datetime import datetime

class DataExporter:
    ...

    def __fetch_from_database(self, start_timestamp, end_timestamp) -> list | None:
        """
        Fetch booking data from the database for a given time range.

        Args:
            start_timestamp (datetime): The start of the time range.
            end_timestamp (datetime): The end of the time range.

        Returns:
            list: A list containing booking data (num_bookings, total_amount) or None if an error occurs.
        """
        self.__connect_to_database()
        query = f"""
        SELECT COUNT(*) AS num_bookings, SUM(total_amount) AS total_amount
        FROM bookings
        WHERE book_date >= {int(start_timestamp.timestamp()) * 1000} AND book_date <= {int(end_timestamp.timestamp()) * 1000}
        """
        logging.info(
            "Exracting bookings data from database for start timestamp=%s and end_timestamp=%s",
            start_timestamp,
            end_timestamp,
        )
        result = None
        try:
            self.cursor.execute(query)
            result = list(self.cursor.fetchone())
            result.append(
                f'{start_timestamp.strftime("%d %b, %Y")} - {end_timestamp.strftime("%d %b, %Y")}'
            )
            logging.info(
                "Successfully exracted bookings data from database for start timestamp=%s and end_timestamp=%s",
                start_timestamp,
                end_timestamp,
            )
        except Exception as e:
            logging.error(
                "Error occurred while extracting bookings data from database: %s", e
            )
        return result

This private method retrieves booking data from the database for a specified time range.

It takes two datetime objects as arguments, start_timestamp and end_timestamp. It also constructs a SQL query to retrieve the count of bookings and the total booking amount for that time range.

The query is executed, and if it's successful, the method returns the data as a tuple. We convert the tuple into a list and append the timeframe for which data was extracted to the list. If an error occurs during the database interaction, it logs an error and returns None.

Using the above method, you can extract booking data for various timeframes, whether it's for a week, a month, a year, or any custom time range of your choice.

How to Structure the Booking Data with the BookingInfo Class

In this section, we will define a BookingInfo class in booking_info.py, which serves as a structured container for booking data retrieved from the database. The class encapsulates booking-related information, making it easier to work with and present the data.

from decimal import Decimal


class BookingInfo:
    def __init__(self, data_list: list):
        """
        Initialize BookingInfo with data from the database.

        Args:
            data_list (list): A list containing booking data (total_bookings, total_amount, timestamp).

        Note:
            The total_amount is converted to a Decimal type.

        """
        self.__total_bookings, self.__total_amount, self.__timestamp = data_list
        self.__total_amount = Decimal(self.__total_amount) if self.__total_amount else Decimal(0)

    def __str__(self) -> str:
        """
        Return a string representation of BookingInfo.

        Returns:
            str: A string in the format "Total Bookings: X, Total Amount: $Y".

        """
        return f"Total Bookings: {self.__total_bookings}, Total Amount: ${self.__total_amount}"

    def get_total_bookings(self) -> int:
        """
        Get the total number of bookings.

        Returns:
            int: The total number of bookings.

        """
        return self.__total_bookings

    def get_total_amount(self) -> Decimal:
        """
        Get the total booking amount as a Decimal.

        Returns:
            Decimal: The total booking amount.

        """
        return self.__total_amount

    def get_timestamp(self) -> str:
        """
        Get the timestamp associated with the booking data.

        Returns:
            str: The timestamp as a string.

        """
        return self.__timestamp

The BookingInfo class is designed to organize and represent booking data returned from the database. It receives a list of values containing total bookings, total booking amount, and a timestamp as input and converts the total amount to a Decimal type. The class offers methods for accessing and presenting this data in a structured manner.

The constructor of the BookingInfo class takes a data_list as input, which is expected to be a list containing the following elements:

• total_bookings: An integer representing the total number of bookings.
• total_amount: A floating-point value representing the total booking amount.
• timestamp: A timestamp associated with the booking data.

The __init__ method initializes private instance variables (__total_bookings, __total_amount, and __timestamp) with the values from the data_list. It also converts the __total_amount to a decimal type for precise handling of monetary values.

The __str__ method is implemented to provide a string representation of the BookingInfo object. It returns a string in the format "Total Bookings: X, Total Amount: $Y", where X is the total number of bookings and Y is the total booking amount formatted as dollars.

Getter Methods

The class provides three getter methods to access the encapsulated data:

• get_total_bookings(): Returns the total number of bookings as an integer.
• get_total_amount(): Returns the total booking amount as a Decimal type.
• get_timestamp(): Returns the timestamp associated with the booking data as a string.

By encapsulating the booking data within the BookingInfo class, the code is more organized, readable, and reusable. This structured approach simplifies the handling of booking information throughout the application, making it more intuitive to work with and present the data.

How to Convert the Data into an Excel Sheet

Now that you can retrieve data from the database for a specific time range, you can also generate an Excel sheet based on the extracted data.

To do this, let's define another private method to create the Excel sheet.

...
import pandas as pd

from booking_info import BookingInfo


...

class DataExporter:

    ...

    def __convert_to_excelsheet(self, data: list, sheet_name: str):
        """
        Convert the fetched data into an Excel sheet.

        Args:
            data (list): A list containing booking data.
            sheet_name (str): Name of the Excel sheet to be created.

        Raises:
            ValueError: If there is an error in converting data to an Excel sheet.
        """
        try:
            booking_info = BookingInfo(data)
            data = {
                "": ["Total Bookings", "Total Amount ($)"],
                booking_info.get_timestamp(): [
                    booking_info.get_total_bookings(),
                    booking_info.get_total_amount(),
                ],
            }
            logging.info("Converting the data into pandas dataframe")
            df = pd.DataFrame(data)
            logging.info("Inserting the data into the excelsheet")
            with pd.ExcelWriter(sheet_name, engine="xlsxwriter") as writer:
                df.to_excel(writer, sheet_name="Sheet1", index=False)
            logging.info("Successfully inserted data into the excelsheet")
        except ValueError as e:
            logging.error("Error converting data into excel: %s", e)

The __convert_to_excelsheet method within the DataExporter class is responsible for structuring and converting extracted booking data into an Excel sheet.

It accepts two input parameters. The first parameter, data, is expected to be a list containing specific booking data. This data includes the total number of bookings, the total booking amount, and a timestamp for which data was extracted. The second parameter, sheet_name, represents the desired name for the Excel sheet that will contain the formatted data.

A key aspect of the method is the structuring of the data. To achieve this, the method initiates the creation of a BookingInfo object, referred to as booking_info. The BookingInfo object provides a structured representation of the booking data, which simplifies the subsequent formatting and presentation.

Following the creation of the booking_info object, a new dictionary called data is generated. This dictionary is designed to structure the data in a format suitable for conversion into an Excel sheet.

The dictionary consists of two key-value pairs:

• The first pair uses an empty string as the key and contains a list with two header values, "Total Bookings" and "Total Amount ($)".
• The second pair uses the timestamp obtained from booking_info.get_timestamp() as the key and includes a list with two elements: the total number of bookings (booking_info.get_total_bookings()) and the total booking amount (booking_info.get_total_amount()).

This dictionary allows the data to be inserted in the excel sheet as below:

Sample Excel Sheet

Then, the structured data dictionary is converted into a pandas DataFrame, referred to as df. Dataframes are a commonly used data structures for handling tabular data in Python. This step streamlines the manipulation and export of the data for further processing or visualization.

To create the Excel sheet, the code uses the pd.ExcelWriter context manager with the "xlsxwriter" engine. This context manager ensures that the Excel file is appropriately prepared for data insertion. The sheet_name parameter is supplied to specify the name of the sheet within the Excel file.

The data within the DataFrame, df, is then written to the Excel sheet. The to_excel method is used in conjunction with the writer object, and the index parameter is set to False. This specific configuration excludes the default row numbers that are typically included in Excel sheets.

How to Combine the Functionalities

Now let's write a public method that the users can use to extract the data from the database and convert the extracted data into the Excel sheet file.

...


class DataExporter:

    ...

    def generate_excelsheet(
        self,
        start_timestamp: datetime,
        end_timestamp: datetime,
        sheet_name: str = "Bookings Data.xlsx",
    ) -> bool:
        """
        Generate an Excel sheet with booking data for a specified time range.

        Args:
            start_timestamp (datetime): The start of the time range.
            end_timestamp (datetime): The end of the time range.
            sheet_name (str, optional): Name of the Excel sheet to be created. Defaults to "Bookings Data.xlsx".

        Returns:
            bool: True if excelsheet was generated successfully else False

        Note:
            This method logs errors but does not raise exceptions to avoid breaking the workflow.
        """
        data = self.__fetch_from_database(start_timestamp, end_timestamp)
        if data is not None:
            self.__convert_to_excelsheet(data, sheet_name)
            return True
        else:
            logging.error("No data to convert generate excelsheet")
            return False

This method accepts several parameters, including start_timestamp and end_timestamp, which define the beginning and end of the time period for data extraction. There's also an optional sheet_name parameter that allows the user to specify the name of the Excel sheet. By default, the sheet is named "Bookings Data.xlsx" to provide a convenient default option.

Upon execution, the method initiates the data retrieval process by calling the __fetch_from_database method, an internal private method of the class, with the specified time range.

If the data retrieval is successful and data is available, the method proceeds to call the __convert_to_excelsheet method. This structures and formats the data for insertion into the Excel sheet.

If, on the other hand, no data is available for the provided time range, the method logs an error message and returns "False" to indicate that the Excel sheet generation was unsuccessful.

How to Send an Email with the Bookings Data Report

In this section, you will learn how you can use Python to send an email with a bookings data report as an attachment.

Create a mailer.py file and add the following content:

import logging
import os
import smtplib
import ssl

from email import encoders
from email.mime.base import MIMEBase
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText

logging.basicConfig(
    format="%(asctime)s | %(levelname)s : %(message)s", level=logging.INFO
)

SMTP_SERVER = os.environ.get("SMTP_SERVER")
PORT = os.environ.get("EMAIL_PORT")
EMAIL = os.environ.get("EMAIL")
PASSWORD = os.environ.get("PASSWORD")


def send_email(to_email: str, subject: str, attachment_name: str):
    """
    Send an email with an attachment to the specified recipient.

    Args:
        to_email (str): The recipient's email address.
        subject (str): The subject of the email.
        attachment_name (str): The filename of the attachment.

    Note:
        This function assumes that the SMTP server requires TLS encryption.

    Raises:
        smtplib.SMTPException: If there is an issue with sending the email.

    """
    message = MIMEMultipart()
    message["From"] = EMAIL
    message["To"] = to_email
    message["Subject"] = subject
    body = "Hi there\n\nPlease find attached your report.\n\nThanks"

    message.attach(MIMEText(body, "plain"))

    with open(attachment_name, "rb") as file:
        part = MIMEBase(
            "application", "vnd.openxmlformats-officedocument.spreadsheetml.sheet"
        )
        part.set_payload(file.read())

    encoders.encode_base64(part)

    part.add_header(
        "Content-Disposition",
        f"attachment; filename= {attachment_name}",
    )

    logging.info(f"Attaching {attachment_name} to the email")
    message.attach(part)
    text = message.as_string()

    context = ssl.create_default_context()
    with smtplib.SMTP(SMTP_SERVER, PORT) as server:
        logging.info(f"Sending email to {to_email}")
        server.starttls(context=context)
        server.login(EMAIL, PASSWORD)
        server.sendmail(EMAIL, to_email, text)
        logging.info(f"Successfully sent the email to {to_email}")

As usual, we have configured the logger and environment variables in our script.

The core functionality is encapsulated within the send_email function. This function takes three parameters:

1. to_email: The recipient's email address.
2. subject: The subject of the email.
3. attachment_name: The filename of the attachment, which should be the bookings data report in this context.

Within the function, we construct an email message using the MIMEMultipart class. This message includes the sender's email address, recipient's email address, subject, and a plain text body with a simple message.

The script allows attaching the bookings data report as an attachment. It reads the attachment file, encodes it, and adds it to the email message. This ensures that the recipient can easily access and download the data report from the email.

You can learn how you can add attachments while sending emails using Python here.

The create_default_context function from the ssl library creates a secure SSL context for email communication. Finally, the script connects to the SMTP server, logs in using the sender's email address and password, sends the email, and logs a success message upon successful transmission.

How to Test the Flow

Let's finally test the flow of the application.

In this section, we will automate the monthly reports. Create a main.py file and add the following content:

from exporter import DataExporter
from datetime import datetime
from mailer import send_email

start_timestamp = datetime(2023, 5, 28, 00, 00, 00)  # May 28 2023 00:00:00
end_timestamp = datetime(2023, 8, 20, 23, 59, 59)  # Aug 20 2023 23:59:59

exporter = DataExporter()
if exporter.generate_excelsheet(
        start_timestamp, end_timestamp, sheet_name="Bookings Data.xlsx"):
    send_email("myemail@gmail.com", "Your Report", "Bookings Data.xlsx")

In the above code, we create two timestamp objects, start_timestamp and end_timestamp, to specify a time range. We have the start date set to May 28, 2023 at midnight and the end date set to August 20, 2023 just before midnight.

Next, we create an instance of the DataExporter class, which handles the data export and Excel sheet generation. The generate_excelsheet method of this instance is called with the previously defined timestamps to create a report related to bookings.

Finally, the code sends an email with the generated Excel sheet as an attachment using the send_email function.

How to Schedule the Application

Next, our goal is to automate the report scheduling process. We aim to schedule report deliveries for two distinct scenarios: on every Monday for the previous week's data, and on the 1st day of every month for the previous month's information.

To schedule the execution, you will need to install the schedule library:

pip install schedule

Once the library is installed, here's how you can do automate the monthly and weekly reports:

import schedule
from exporter import DataExporter
from datetime import datetime, timedelta
from mailer import send_email


def main():
    today = datetime.now()
    sheet_name = "Bookings Data.xlsx"

    if today.weekday() == 0:  # Check if it's Monday (0 means Monday)
        # It's Monday, fetch data for the previous week (Monday to Sunday)
        start_timestamp = (today - timedelta(days=7)
                           ).replace(hour=0, minute=0, second=0, microsecond=0)
        end_timestamp = (today - timedelta(days=1)
                         ).replace(hour=23, minute=59, second=59, microsecond=0)
        sheet_name = "Weekly Report.xlsx"
    elif today.day == 29:
        # It's the 1st day of the month, fetch data for the last month
        start_timestamp = (today.replace(day=1) - timedelta(days=1)
                           ).replace(day=1, hour=0, minute=0, second=0, microsecond=0)
        end_timestamp = (today.replace(day=1) - timedelta(days=1)
                         ).replace(hour=23, minute=59, second=59, microsecond=0)
        sheet_name = "Monthly Report.xlsx"

    exporter = DataExporter()
    exporter.generate_excelsheet(
        start_timestamp, end_timestamp, sheet_name)

    send_email("youremail@gmail.com",
               "Your Report", sheet_name)


schedule.every().day.at("00:00").do(main)

while True:
    schedule.run_pending()

The above script uses the schedule library to run the main function daily at midnight. The main function calculates the timestamps for data extraction and Excel sheet generation. After generating the Excel sheet, the script sends it via email to a specified recipient.

If the script runs on a Monday, it sets up to generate a weekly report. It calculates the start_timestamp and end_timestamp for the previous week. The start_timestamp is set to the previous Monday at midnight (00:00:00), and the end_timestamp is set to the previous Sunday just before midnight (23:59:59). The Excel sheet is named "Weekly Report.xlsx."

On the 1st day of the month, the script shifts its focus to generating a monthly report. It calculates the start_timestamp and end_timestamp to encompass the entire previous month. The start_timestamp is set to the first day of the previous month at midnight (00:00:00), while the end_timestamp is set to the last day of the previous month just before midnight (23:59:59). The Excel sheet is named "Monthly Report.xlsx."

Wrapping Up

In this tutorial, you learned how you can leverage Python to automate generating a report and sending it to email recipients. I hope you found the tutorial helpful!

Future Scope

• You can add the email recipients in a database and fetch their list from there instead of hardcoding them in the code itself. This will make the application more configurable.
• You can also use Cron Jobs to automate the execution of the script every day at midnight. In that case, you won't need the schedule library.

Here's a link to the Github Code Repository.

You'd be surprised how many different ways there are to do things in Excel. Below, I'll show you 9 ways to add two or more numbers. You can skip to a certain section if you'd like to see that method:

1. Manual
2. References
3. SUM()
4. SUMIF()
5. SUBTOTAL()
6. AGGREGATE()
7. VBA
8. Python
9. Highlight

Video Walkthrough

If you prefer to watch me go through each of these in a demo workbook, here's a video for that:

To write a formula or a function in Excel (which we'll be doing in the examples below), start out by simply typing an equals sign in a cell.

=

This triggers Excel to know that what follows will be a formula or a built-in function.

Manual

This is simplest version of a formula in Excel. Start out with the equals sign, and then type in the numbers and operation you want to do. Just like a calculator:

=6+102

Pressing enter will result in 108 being listed in the cell

screenshot of manual addition in Excel

References

The next step up in Excel is to start using cell references. Notice that on the top and on the left side of the main spreadsheet area, there are columns designated by letters and rows designated by numbers.

screenshot of rows and columns

We can refer to specific cells using "A1" notation. Like a set of (x,y) coordinates on a graph, this simply means that by referring to C4, for instance, we are referring to the cell found in column C, row 4.

To add numbers using references, we start again with the equals sign and refer to the values in specific cells directly.

This has the added advantage of being dynamic. If a value is changed in one of the cells, the result of the sum automatically updates.

=SUM(A1+A3) provides us with the value of the numbers in A1 and A3.

SUM()

The first two methods are examples of using formulas. We manually give Excel a series of instructions that it executes.

Excel also has built-in functions which we can use by starting with the equals sign and then referring to the function by name. Functions also take variables which we pass to them by using a set of parenthesis after the name of the function.

The SUM() function takes either a range or a comma-separated list of cell references. It then returns the sum of all the numbers in the range.

You can select a range by either clicking and dragging or by declaring one by typing in the A1 notation with a colon in between the top left cell and the bottom right cell of the range.

SUM(A5:A11) adds all the numbers in cells A5, A6, A7, A8, A9, A10, A11

screenshot of the SUM() function

SUMIF()

A more powerful version of SUM() is the SUMIF() function. This adds conditional logic. It needs at least two variables: a range and a condition. We could give it the same range as above and have a condition be that it only adds up numbers that are greater than zero.

A third, optional variable is a sum_range. This allows for us to match a condition in one range with the sum of values in another range.

In the example sheet, I have inserted checkboxes in column C. Checkboxes in Excel are a new feature. This is the range that I'm checking for a condition. The condition is TRUE. Now I enter the range that I want to sum if the condition in the corresponding row of the first range is indeed TRUE.

When using a range and a sum_range separately like this, they do have to be of the same size or it will not behave as you want it to.

screenshot of SUMIF() function in Excel

SUBTOTAL()

Ok, here's where things start to get interesting.

The SUBTOTAL() function allows us to do a ton of different things. Ultimately, it returns a subtotal of a list or database. But inside of SUBTOTAL() there are other functions. The first argument that we give SUBTOTAL() is a number corresponding to one of these functions:

screenshot of functions within SUBOTAL from Microsoft Excel

Looking at the function list, we see that 9 or 109 both correspond to the SUM() function which we want to use. If we have hidden rows in our range that we don't want to include in the sum, we use 109 to ignore those – if not, simply 9.

So the function looks like this: SUBTOTAL(9,B3:B12). This sums B3:B12 even if one or more of those rows are hidden.

screenshot of SUBTOTAL in Excel

AGGREGATE()

We can think of AGGREGATE() as a souped-up version of SUBTOTAL(). It works in the same way but has a lot more built-in functions (19 of them) and allows for detailed specificity on what values, if any, to ignore in the calculation.

AGGREGATE(function_num, options, ref1, [ref2], …) is the full reference formula. Again, we pass it a number corresponding to one of the 19 built-in functions, then an optional argument for what type of values to ignore, followed by the reference array and an optional second reference array.

screenshot of options for Aggregate function from Microsoft Excel

For our example, we again use 9 as our function number, but we can use option 5 to explicitly exclude hidden rows:

screenshot of Aggregate function in Excel

VBA

Now we're warmed up. Let's get overly complicated.

gif of complicated nonsense

Visual Basic for Applications is Microsoft's baked-in programming language in Microsoft Office applications.

Open it up by selecting Visual Basic from the Developer tab.

screenshot of VBA in Developer tab in Excel

If you don't see the developer tab, go to File - Options - Customize Ribbon and add it.

screenshot of Customizing Ribbon in Excel

Also, Alt +11 is the keyboard shortcut to open up VBA.

Once here, we can write code to do all sorts of things. Our example isn't very practical since a function will do it quicker, but the following code will Sum the range A1:A11, put the result in F11 and display a message pop-up with the result:

Sub AssignSumVariable()
   Dim result As Double
   'Assign the variable
   result = WorksheetFunction.Sum(Range("B1:B11"))
   'Show the result
   MsgBox "The total of the ranges is " & result
   'Put the result in cell F9
  Range("F9") = result
End Sub

screenshot of VBA in Excel

Python

Yes, this is now ridiculous. But, it's good to know what Excel can do when you have more complicated tasks that require tools like VBA or Python. At the time of this writing, Python is available in Excel for people using the Beta Channel of Excel.

gif of woman saying "seems like overkill"

You can check your eligibility and join Microsoft 365 Insider if you want to test out new features like this in the future.

Go to File - Account, and then select the 365 Insider channel button for more info.

screenshot of Microsoft 365 Insider options

Once Python is usable in Excel, you activate it by typing =py and then the tab key. This turns the cell into a Python command line.

screenshot of Python command line in Excel

From here, we can write Python code directly in the cell. The following code uses the custom xl() function for Python to use a range. We hold the range in the numbers variable and then using dot notation, we sum that range with the numbers.sum() line:

numbers = xl("'Sum➕'!$B$3:$B$12")
numbers.sum()

Now to execute the Python code, click CTRL + ENTER.

What we now see is that we've got a Python Series in the cell:

In order to just display the answer, we can click the Python Output selector just to the left of the formula bar and select Excel Value:

screenshot of Python Output in Excel

Now, our cell is updated with the correct value.

screenshot of cells in Excel

The real value of Python in Excel comes with manipulating dataframes using built-in libraries like Matplotlib, NumPy, or Pandas.

Okay, take a breath, we'll finish with something simple and easy...👇

Highlight

Bonus time. If you highlight cells in Excel by either clicking and dragging mouse over a range, or by CTRL+ Left-Click individual cells, some automatic calculations are visible in the bottom right of the window, including the Average, Count and Sum:

If the sum isn't immediately visible, right clicking will pull up auto-calculations that you can toggle on and off:

screenshot of auto-calculation options in Excel

Thanks for reading!

Hope this is helpful for you!

Follow me on LinkedIn: https://www.linkedin.com/in/eamonncottrell/

And YouTube: https://www.youtube.com/@eamonncottrell

Have a great one! 👋

Data validation is a feature in Excel used in restricting data entry in specific cells. It can also prompt the user to enter valid data into the cells based on the rules and restrictions provided by the creator of the Excel worksheet.

When setting up a workbook, you might want to make sure users input a specific type of data. For example, you might want to allow only dates, numbers, or letters in a specific range to be imputed in a cell. This is crucial if you want to eliminate mistakes as much as possible in your data.

Types of Data Validation Rules in Excel

Here are a few data validation rules you can set up in Excel:

• Only allow text or numeric values in a cell.
• Only allow numbers within a specific range.
• Display a warning message when a user inputs the wrong data.
• Only allow dates and times outside a given range.
• Validation rule based on criteria from another cell.

Steps to Create Data Validation Rules in Excel.

To create a data validation rule in Excel, do the following:

First, select the row, column, or specific cell you want to apply a data validation rule to.

Then open the data pane and click on the data validation. Alternatively, you can go directly to the data validation dialogue box by pressing the following keys on your keyboard separately. ALT > D > L. Do not hold the keys together, press the keys separately and you will be taken to the dialogue box as well.

Create the data validation based on what data you want to be supplied in the cell or row.

You can supply the following validation criteria:

Allow: Make a rule based on the type of data you want to allow. You can choose one from the drop-down menu. You can uncheck the “Ignore blank” button if you do not want blank spaces.

Data: From the drop-down menu you can choose the criteria and also input the minimum and maximum values you want the user to input.

With the validation criteria set, click OK to close the window or click on the Input Message or Error Alert tab to give the user more information on the data validation rule.

Input message: While this is optional, you can input a message to be displayed when a user clicks on a cell that has a data validation rule defined on it.

Next, give your input message a title, and under the input message, make sure the message you provide is clear to the user. Click on OK to close the dialogue box or navigate to the Error Alert tab.

Then display an error message. This is optional, but it is good practice to display an error message to users when they enter data that is outside the validation rule you set.

There are three types of error alerts:

1. Stop: This is the default and is very strict, as it stops users from entering invalid data. You can only click on “Retry” or “Cancel”

2. Warning: This will only warn the user but is not as strict as the stop warning. A user can ignore the message by clicking “YES” the invalid data will be inputted.

Here’s an example of the warning message a user will get:

3. Information: This is a permissive type of error alert as it only informs the user about invalid data inputted.

If the user clicks OK, the invalid gets inserted into the worksheet. If the user clicks on Cancel, the data gets deleted.

Give a title to the error alert and also provide a message for your users to see. When you’re done, click on OK, and your data validation rule has been set.

Conclusion

Data validation in Excel is one powerful feature you should utilize when creating an Excel spreadsheet.

You can use the data validation feature in Excel to make rules that will ensure the data inputted meets certain criteria or follows predefined rules. Setting a data validation rule helps to maintain data accuracy, consistency, and integrity within your Excel worksheet.

In this article, we'll look at two ways to validate and/or apply conditional formatting to a sample order form to prevent errors and speed up fulfillment.

gif of man saying, "there is no room for error"

You can find the Excel sheet we're using for this tutorial here.

You may download a local copy to tinker with by selecting File, Save As, Download a Copy:

Download a copy of Excel Workbook

You can find a Google Sheets version of the same thing here.

You may download or make a copy online by selecting File, Download or File, Make a copy.

Download or make a copy of Google Sheet

I'll discuss the Excel version from here on, making reference when something differs in Google Sheets.

Video Walkthrough

Oh, and here's an enjoyable video walkthrough should you feel so inclined. 😁😁

Setup

I've created a three column order form where a store may inventory their product and enter an amount to order. The third column is used by the warehouse to enter how many were actually delivered. This is a real world setup that we'll use in simplified form for this tutorial.

Sample order form in Excel

It can be difficult for fulfillment if there are zeros entered into the order column. Instead of allowing this, we'll use a couple tools to show how to control values in a cell. No matter how clear the directions are, someone will always forget and enter a zero.

Conditional Formatting

By applying conditional formatting, we can effectively white-out the cells that contain zeros (or any negative values).

From the Home Ribbon in Excel and the Format menu in Google Sheets, select Conditional Formatting.

If you don't see the conditional formatting as an option, it'll be over in the styles dropdown or in the far right in a three-dotted dropdown, depending on whether you've got the classic or the new style of ribbon displayed.

Conditional formatting menu in Excel Ribbons

If you want to change your ribbon's layout, select this dropdown arrow at the far right of the ribbon:

Change Excel Ribbon layout

Once you're in the conditional format menu, click Manage Rules. This will let you specify the formatting depending on a ton of options.

Manage conditional formatting rules in Excel

This is where Microsoft Excel does have a leg up on Google Sheets. Excel has more options laid out in a more intuitive way. You can do the same things in each program, but Excel has organized theirs a little better in my opinion.

Conditional formatting menu organization in Excel

We are going to select the Order column as our range and then highlight cells with cell values of less than or equals to zero.

At other times, you'll be using conditional formatting to make data visualization using colors and color scales, but in our case, we want to blot out the zero value.

To do this, I've simply selected a white fill color and a white text color. 🤔

Conditional formatting menu

And now, voilà! If a zero amount is entered, it will simply white out to not distract from the fulfillment center:

Zero value entered and conditional formatting applied

Data Validation

The second option at our disposal is data validation. You can find this on the data tab in the ribbon, and if you're not seeing it, you can find it by exploring the same ribbon options I detailed above.

Data validation menu in ribbon

This will give us a host of options to select to validate the data going into a specified range. There are many options to choose from for our data.

In our case, we want to make sure they are whole numbers greater than zero. Sort of the opposite of the conditional formatting we did above.

Data validation

Another nice Excel feature that's missing at the time of this writing in Google Sheets is the ability to put an Input Message into the data validation.

Input message in data validation

Now, whenever you are on cell in the data validation range, a friendly box will pop up with directions reminding you to not order a zero amount. 😀

Example of input text in spreadsheet

Data validation in Excel defaults to blocking any input that doesn't adhere to the defined conditions.

So, you'll receive an ugly pop up preventing you from entering a zero.

data validation warning

We can improve upon this by setting a custom message here too, though. And we can select whether to block it outright or to allow a zero to be entered after the warning pops up. Effectively allowing the warning to be ignored in the event that there's a reason to do this.

data validation custom warning message

And finally, we can couple any of these options with our conditional formatting so that if we do only warn against the entry, we still blot it out with the white text and white fill color.

The accompanying Excel Sheet and Google Sheet contain four columns of each of the above examples for you to see in action.

I hope this has been helpful for you!

Please come see my video tutorials on YouTube. I'd appreciate a like and subscribe as I'm growing my tech education channel there!

Have a great one!

Data analysts can readily modify, examine, and display huge amounts of data with Excel, which makes it simpler to gain insights and make wise choices.

Excel's versatility lets users carry out a variety of data analysis activities, from straightforward math operations to intricate statistical analysis. Also, Excel offers automation through the use of third-party programs like Python or the built-in programming language VBA.

Excel is frequently used for data analysis across a range of industries, including banking, healthcare, and marketing, thanks to its versatility and usability.

But as a data analyst, you might often find yourself repeating mundane tasks on a daily basis when working with Excel.

These tasks may include copying and pasting data, formatting cells, and creating charts, among others. Over time, this can become monotonous and time-consuming, leaving you with less time to focus on more important aspects of data analysis, such as identifying trends, outliers, and insights.

This is why automating Excel using Python can be a game-changer, helping you streamline your workflows and free up time for more meaningful analysis.

In this tutorial, I'll show you some helpful ways to create, update, and analyze Excel spreadsheets using Python programming. Let's dive in.

How to Merge Two Separate Spreadsheets with Python

Data analysts often have to work on many spreadsheets, which can become hectic when you have to merge those files together.

The code below helps you merge two separate files together.

import pandas as pd

# Read in the two Excel files

file1 = pd.read_excel('file1.xlsx')file2 = pd.read_excel('file2.xlsx')

# Merge the two files using the concat() method
merged_file = pd.concat([file1, file2], ignore_index=True)

# Write the merged file to a new Excel file
merged_file.to_excel('merged_file.xlsx', index=False)

In this code, we first import the Pandas library, which we'll use to read in and manipulate the Excel files.

We then use the read_excel() method to read in both file1.xlsx and file2.xlsx. Next, we use the concat() method to merge the two files together. The ignore_index=True argument ensures that the index values from both files are reset, so we don't end up with duplicate index values in the merged file.

Finally, we use the to_excel() method to write the merged file to a new Excel file named merged_file.xlsx. We also set index=False to ensure that the index column is not included in the output file.

How to Import and Export Data with Python

This task involves using Python libraries such as Pandas to read Excel files into a DataFrame object. You can then manipulate it and analyze it using Python.

You can also export data from Python back into an Excel file using the same libraries.

import pandas as pd
# Import Excel file

df = pd.read_excel('filename.xlsx', sheet_name='Sheet1')

# Export to Excel file
df.to_excel('new_filename.xlsx', index=False)

The given code imports the Pandas library and reads an Excel file named "filename.xlsx" from Sheet1 of the workbook, storing the data in a Pandas dataframe named "df". The dataframe is then exported to a new Excel file named "new_filename.xlsx" using the "to_excel" method. The "index=False" parameter is used to exclude row indexing in the output file.

Essentially, the code copies the contents of the original Excel file to a new file using Pandas.

How to Clean and Transform Data using Python

This task involves using Python libraries such as Pandas to clean and transform data in Excel.

This may include removing duplicates, filtering data based on specific criteria, and performing calculations on the data.

import pandas as pd

# Remove duplicates
df = df.drop_duplicates()

# Filter data
df = df[df['column_name'] > 10]

# Perform calculations
df['new_column'] = df['column1'] + df['column2']

The code snippet above performs data cleaning and manipulation tasks on a Pandas dataframe named 'df' using the Pandas library.

Firstly, it removes duplicate rows from 'df' using the "drop_duplicates" method. Secondly, it filters the 'df' dataframe by selecting rows where the value in the 'column_name' column is greater than 10 and assigns the filtered result to a new dataframe called 'data_df'.

Lastly, a new column named 'new_column' is added to 'df' which contains the sum of values from 'column1' and 'column2'.

Overall, the code effectively cleans and manipulates the data by removing duplicates, filtering specific rows, and adding a new calculated column to the original dataframe.

How to Perform Data Analysis with Python

This task involves using Python libraries such as Pandas and NumPy to perform data analysis on Excel data.

This may include calculating summary statistics, such as mean and standard deviation, or creating custom reports by grouping data based on specific criteria.

import pandas as pd
import numpy as np

# Calculate summary statistics
df.describe()
# Create custom reports
df.pivot_table(values='column_name', index='category_name', columns='date')

The code utilizes the Pandas and NumPy libraries and performs data analysis and reporting tasks on a Pandas dataframe named "df".

Firstly, it calculates summary statistics for the numerical data in the dataframe using the "describe" method. This method generates valuable insights on the data's distribution, central tendency, and dispersion.

Secondly, the code uses the "pivot_table" method to create customized reports from the dataframe. This method summarizes and aggregates the data in the dataframe and can produce tables in various formats.

In this code, it generates a new dataframe where the 'column_name' values are grouped by the 'category_name' and 'date' columns.

Overall, the code performs statistical analysis and reporting tasks on the dataframe to gain insights from the data.

How to Create Charts with Python

This task involves using Python libraries such as matplotlib or seaborn to create charts and graphs from Excel data.

You can customize these charts to display specific data and format them to meet specific requirements.

import pandas as pd
import matplotlib.pyplot as plt
# Create a bar chart
df.plot(kind='bar', x='category_name', y='sales')
plt.show()
# Create a scatter plot
df.plot(kind='scatter', x='column1', y='column2')plt.show()

The code imports two libraries, Pandas and matplotlib.pyplot using the aliases 'pd' and 'plt', respectively.

The Pandas "plot" method is then used to create two types of plots. The first type of plot is a bar chart that shows the relationship between the 'category_name' and 'sales' columns in the "df" dataframe.

The second type of plot is a scatter plot that shows the relationship between the 'column1' and 'column2' columns in the same dataframe. The code uses the parameters "kind='bar'" for the bar chart and "kind='scatter'" for the scatter plot to create the respective plots.

Lastly, the "show" method is called to display the plots on the screen. In summary, the code utilizes Pandas and matplotlib to create a bar chart and a scatter plot to visualize the data in the "df" dataframe.

How to Do Data Visualization in Python

This task involves using Python libraries such as Plotly and bokeh to create interactive data visualizations from Excel data.

These visualizations allow users to explore data in new ways, such as by zooming in on specific data points or filtering data based on specific criteria.

import pandas as pd
import plotly.express as px
# Create a heatmap
fig = px.imshow(df.corr())
fig.show()
# Create a line chart
fig = px.line(df, x='date', y='sales', color='category')
fig.show()

The code uses the Pandas and plotly.express libraries to create two types of visualizations. First, a heatmap plot is created using plotly.express's "imshow" method that visualizes the correlation between columns in the "df" dataframe.

Second, a line chart is created using plotly.express's "line" method that displays the relationship between the 'date' and 'sales' columns while differentiating between categories based on the 'category' column of the dataframe. Both plots are displayed using the "show" method.

How to Automate Report Generation with Python

This task involves using Python scripts to automate the process of generating reports from Excel data.

You can set up these scripts to run on a regular schedule, such as daily or weekly. They can also automatically update as new data becomes available.

import pandas as pd
# Create daily report
df_daily = df[df['date'] == '2022-01-01']
df_daily.to_excel('daily_report.xlsx', index=False)
# Create weekly report
df_weekly = df.groupby('category').sum()
df_weekly.to_excel('weekly_report.xlsx', index=False)

The code creates a daily report by creating a new dataframe "df_daily" that includes only the rows where the 'date' column equals '2022-01-01'. This is achieved by using Pandas' boolean indexing feature.

Afterward, the "to_excel" method is used to export the filtered data to an Excel file named "daily_report.xlsx", without including the index column.

Next, the code creates a weekly report by grouping the "df" dataframe by the 'category' column and summing the values of all other columns. This is accomplished using the Pandas "groupby" and "sum" methods.

The result is saved in a new dataframe named "df_weekly". Lastly, the "to_excel" method is used to export the aggregated data to an Excel file named "weekly_report.xlsx", without including the index column.

In summary, the code creates two reports using the Pandas library. The first report is a daily report that includes only data from a specific date, and the second report is a weekly report that aggregates data by category. Both reports are exported to Excel files using the "to_excel" method.

How to Automate Repetitive Tasks with Macros and Scripts in Python

This task involves using Python to automate repetitive tasks in Excel, such as data entry or formatting. You can do this by creating macros or scripts that can execute automatically, or by using Python to interact with the Excel application directly.

import win32com.client as win32
# Open Excel file
excel = win32.gencache.EnsureDispatch('Excel.Application')
workbook = excel.Workbooks.Open(r'filename.xlsx')
# Run macro
excel.Application.Run('macro_name')
# Save and close Excel
 fileworkbook.Save()workbook.Close()excel.Quit()

The code uses the win32com.client module to interact with Microsoft Excel via the Windows API.

First, an instance of the Excel application is opened using the EnsureDispatch() method, and the specified Excel file is opened using the Workbooks.Open() method.

Next, a macro is executed using the Application.Run() method, passing the name of the macro as an argument.

Finally, the changes made to the Excel file are saved using the Save() method, the workbook is closed using the Close() method, and the Excel application is terminated using the Quit() method

How to Scrape Data with Python

This task involves using Python libraries such as requests and Beautiful Soup to scrape data from web pages or other sources and import it into Excel.

You can then analyze and manipulate this data using Python libraries such as Pandas.

import pandas as pd
import requests
from bs4 import BeautifulSoup
# Scrape data from web page
url = 'https://www.website.com/data'
response = requests.get(url)
soup = BeautifulSoup(response.text, 'html.parser')
table = soup.find('table')
df = pd.read_html(str(table))[0]
# Export to Excel file
df.to_excel('scraped_data.xlsx', index=False)

This code uses the requests library to send an HTTP GET request to the URL 'https://www.example.com'. It then uses the BeautifulSoup library to parse the HTML content of the response into a BeautifulSoup object named 'soup'.

You can then use BeautifulSoup methods such as find_all() to extract specific data from the HTML:

links = []for link in soup.find_all('a'): href = link.get('href') links.append(href)

This code finds all the anchor tags in the HTML and extracts the value of the 'href' attribute for each one, adding them to a list named 'links'.

How to Use Python to Integrate Excel with Other Applications

This task involves using Python to integrate Excel with other applications, such as databases or web services.

You can do this using Python libraries such as pyodbc to connect to databases or by using APIs to connect to web services. This allows for seamless data transfer and analysis between different applications.

import pandas as pd
import pyodbc
# Connect to database
cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=server_name;DATABASE=db_name;UID=user_id;PWD=password')
# Read data from database
query = 'SELECT * FROM table_name'
df = pd.read_sql(query, cnxn)
# Export to Excel file
df.to_excel('database_data.xlsx', index=False)

The code establishes a connection to a SQL Server database using pyodbc.connect() method, where the driver, server name, database name, user ID, and password are provided as arguments.

Then, a SQL query is defined and executed to retrieve data from a table in the database using the pd.read_sql() method, where the SQL query and the connection object are provided as arguments. The retrieved data is then stored in a pandas DataFrame.

Finally, the data in the DataFrame is exported to an Excel file named "database_data.xlsx" using the to_excel() method, with the index column excluded from the export by setting the index parameter to False.

Conclusion

Python is a versatile language that you can use to automate many Excel tasks. You can also use various libraries such as Pandas, openpyxl, xlwings, and pyautogui to manipulate data, extract information, generate reports, and automate repetitive tasks.

Automation can save time and effort, reduce errors, and boost productivity. Python proficiency can be a valuable skill for any professional working with Excel, whether you're a data or financial analyst. By learning Python, you can elevate your work to new heights.

Let’s connect on Twitter and LinkedIn. Thanks for reading!

In these cases, you might need to unhide those hidden rows and columns before you start updating the spreadsheet examine the data it contains.

In this article, you will learn how to unhide rows and columns in Excel. You will also learn how to unhide all rows and columns in case they are all hidden.

This tutorial is applicable to Excel 2007 to 2016, but it can also guide you through how to do it in other versions of Excel.

What We'll Cover

• How to Find Hidden Rows and Columns
• How to Unhide a Column in Excel
• How to Unhide a Row in Excel
• How to Unhide all Hidden Rows and Columns in the Home Tab
• How to Unhide all Rows and Columns at Once with VBA Micro
• Conclusion

Before we dive into how to unhide rows and columns in Excel, let’s look at how to find hidden rows and columns.

How to Find Hidden Rows and Columns

You can detect a hidden row or column by looking at the lines that separate each row and each column. If the lines are double or green, then it means there's a hidden row or column.

Also, taking a look at the spreadsheet, if there are missing letters for the columns or missing numbers in the rows, then it’s possible there are hidden rows and columns in the spreadsheet.

How to Unhide a Column in Excel

You can unhide a certain column by right-clicking on the double line that indicates the hidden column and selecting “unhide”:

How to Unhide a Row in Excel

If you want to unhide a row, right-click on the double line indicated in green (or if possible, another color based on the user settings), then select “unhide”:

How to Unhide all Hidden Rows and Columns in the Home Tab

To unhide all rows, select all rows and columns by pressing CTRL + A, go to the Home tab, locate cells, and click the arrow on "Format":

Under visibility, hover on “Hide and Unhide”, then select “Unhide Rows”:

To unhide all hidden columns, you have to follow the same process, but this time around, you have to select “Unhide Columns”.

How to Unhide all Rows and Columns at Once with VBA Macro

If you’re dealing with a large spreadsheet, the processes we just discussed might be tedious for you.

Instead of unhiding for the rows and columns one by one or unhiding rows and unhiding columns, you can run some VBA (Visual Basic for Applications) code to do it.

Here’s how to run VBA code in Excel:

Press ALT + F11 (or Option + F11 on Mac) to open the VBE (Visual Basic Editor):

Right-click on your current workbook, hover on “Insert” and select “Module”:

Paste this code into the editor:

Sub UnhideAllRowsAndColumns()
  Cells.EntireColumn.Hidden = False
  Cells.EntireRow.Hidden = False
End Sub

Close the dialogue and go back to your worksheet.

Next, you need to run the VBA Micro. Press ALT + F8 and you’ll see something like this:

Expand it and click “Run”:

Every row and column should now show up:

Conclusion

This article showed you how to unhide a row or column in Excel.

The examples we looked at showed you how to unhide individual rows and columns, unhide all columns, and unhide all rows.

We also looked at how you can unhide rows and columns altogether with Excel VBE (Visual Basic Editor).

If you find this article helpful, share it with your friends and family.

The =XLOOKUP() function is my new favorite way to lookup data. It's discussed in the last section 👇.

We'll look at four of the built in lookup functions in both Excel and Google Sheets:

1. =LOOKUP()
2. =VLOOKUP()
3. =HLOOKUP()
4. =XLOOKUP()

Now, if you've spent any time in spreadsheets, you've probably heard mention of or are already using =VLOOKUP(). It is one of the most popular functions out there, but is also a bit mystifying if you don't use it on a regular basis.

🏆 I'll walkthrough each of these to give you a full understanding of how to use the functions properly. And I'll highlight my new favorite, =XLOOKUP(), that Microsoft released in 2019 and Google Sheets added in 2022.

☕ I've also built a coffee themed Google Sheet that you can open and follow along with. Here it is.

📽️ And, I've got you covered...at the end of the article there's a video walkthrough too. 💥

Gif of athlete pointing and saying, I Got You!

Coffee Data

In our coffee data spreadsheet, I have made two sheets with the same data on it. One, the coffee-data tab, is for LOOKUP and VLOOKUP. The horizontal-data tab is for HLOOKUP.

Here's what the coffee-data tab looks like. There are columns for the coffee name, price, popularity, roast level and taste.

Coffee Data Spreadsheet Screenshot

Here's what the horizontal-data tab looks like. Same info, just transposed so we can walk through the HLOOKUP function in a bit.

Coffee Data Horizontal Spreadsheet Screenshot

And then we have our main tab, lookup-functions, where we'll examine the different functions below.

If you haven't pulled up the Google Sheet yet, go ahead so you can follow along: https://docs.google.com/spreadsheets/d/1rNAJKwGQzdq8F2zMrAwHMQvY_z3FlBs9BIy_4MIOXn4/edit#gid=1137792422

Everything works the same in Excel, but it's very easy to share Google Sheets. You can make your own copy to work in by clicking File -> Make a copy.

I've created several named ranges in this sheet to make it easier as we fill out the functions. You can examine those by clicking Data -> Named ranges. I'll reference these in the function definitions below.

Screenshot of named ranges in Google Sheets

How to Use the LOOKUP Function

No surprise here – =LOOKUP() lets you look up a value in your data. Same as everything else here.

Here's our LOOKUP function to return the taste: =LOOKUP(A2,coffees,taste).

We're using named ranges (coffees & taste) which define the coffees column and the tasting notes column in our coffee-data.

If you pull up the sample Google Sheet, you'll see that we're giving LOOKUP three arguments: the search_key, search_range|search_result_array, and the optional [result_range].

Screenshot of Lookup function

The search_key is the thing we're searching for. In our example, it's the name of the coffee we want information about. All of the functions have a search_key argument.

The search_range|search_result_array is the range where =LOOKUP() needs to find the search_key. It can be used as both search and result ranges too.

Say you have search keys in your coffee column (A) and the result you want displayed is the taste column (E). If you specify A:E as the search_range|search_result_array then you'll get the tasting notes on whatever coffee you're searching for.

Screenshot of table of data

When you do this, the result range value will come from the last column (or row) in the range.

The alternative is to simply specify the search_range column on the coffee column and then enter another range for the result_range.

This is what I've done in our sample spreadsheet since I wanted to pull data from each of the columns about the coffee.

Shortcomings of =LOOKUP():

• Data must be sorted. It will not work properly if it's not.
• You must specify the single return column or row for the result.

How to Use the VLOOKUP Function

This is a very popular function because it lets you lookup data with a little more power than the regular LOOKUP function.

Here's our VLOOKUP function to return the taste: =VLOOKUP(E2,All,5,FALSE).

We're using another named range. This time, just one is needed. We have All which is the entire table of coffee-data.

Screenshot of table of data

With =VLOOKUP() we enter the search_key as before but then we give it a range to search. The first column in the range will be used to find the search_key. Then we give it an index which tells how many columns to the right we want to look for our returned value.

We then type in FALSE to indicate that our data isn't sorted. (This is actually unnecessary here since we sorted it so that the regular LOOKUP function would work properly).

Screenshot of VLookup function

Shortcomings of =VLOOKUP():

• Must lookup data from left to right.
• Must specify a number for the index. If you add or move columns in your data, you risk breaking the formula.
• Defaults to sorted data. In many cases, you'll need to set that argument as FALSE so the function will work properly.

How to User the HLOOKUP Function

HLOOKUP works basically the same as VLOOKUP except that instead of searching through columns, it searches through rows.

Here's our HLOOKUP function to return the taste: =HLOOKUP(I2,hAll,5,FALSE)

Screenshot of table of horizontal data

Check out the horizontal-data tab to see that I've transposed the same data set so that our search_key now is spread across a row instead of down a column. Then when we give an index value, it returns that value by counting from top to bottom:

Screenshot of HLookup function

So if we wanted the price, we'd have index of 2 since it's the second row down. And in our example, we return taste with an index of 5.

Shortcomings are the same as VLOOKUP.

How to Use the XLOOKUP Function

Enter: =XLOOKUP()! Microsoft released this as a successor to VLOOKUP and HLOOKUP in 2019, and Google Sheets finally added it in August of 2022.

What's different?

Well, to start with, you can use it vertically or horizontally. You don't have to specify one or the other as long as you have the proper ranges as arguments.

Check out both of the XLOOKUP blocks on the sample spreadsheet. One is using it as a vertical lookup and the second as a horizontal lookup. All that is necessary are that the ranges are correct: The search_key must be a single row or column, and the lookup_range must be of the same size depending on which is used.

Screenshot of XLookup function used vertically

Screenshot of XLookup function used horizontally

Some more features/advantages:

• =XLOOKUP() defaults to an exact match so you don't have to specify a match_mode if this is what you're after.
• You're able to define a custom string to display as missing_value instead of #N/A in the event that nothing is returned.
• You're able to define search_mode to search from the last entry to the first if you desire. In VLOOKUP and HLOOKUP, it is only possible to go first to last.
• Instead of declaring new functions for each desired value ( price, popularity, roast level, and taste ), =XLOOKUP() will return each value in the given lookup_range.

If you want don't want all the values returned, you'd need to reduce the size of the range.

In the spreadsheet example, I've defined my ranges All and hAll to include all the columns and rows, respectively. If I wanted to leave out taste, for instance, we'd need to leave that column/row out of the lookup_range.

=XLOOKUP() was introduced to be the successor of =VLOOKUP() and =HLOOKUP. I think I'll be using it going forward, what about you?

Conclusion

As with most things in computer programming and spreadsheets, there are many ways to solve the same problem.

We've explored four ways to lookup data in a spreadsheet: =LOOKUP(), =VLOOKUP(), =HLOOKUP() and =XLOOKUP(). Each is powerful, but =XLOOKUP(), the newest function, is particularly useful in combining and expanding many features of its predecessors.

Here is a video walkthrough of everything we've discussed:

Thanks🙏for reading and watching!

Subscribe🔔to my YouTube channel here: https://www.youtube.com/@EamonnCottrell/

And say hey👋on LinkedIn here: https://www.linkedin.com/in/eamonncottrell/

When dealing with data sets, especially large ones and/or those that you didn't create, it is likely that you will have to clean the data in large or small ways to get it fully functional.

Both of the built-in functions we'll discuss – =TRIM() and =CLEAN() – are available in Microsoft Excel as well as Google Sheets. And both of them have the potential to save you a lot of head scratching.

Let's examine:

1. What do we mean by clean data?
2. Why isn't it already clean?
3. How do we clean it fast and thoroughly?

a man flipping his messy desk and it becoming magically organized

pssst: I have a video walkthrough at the bottom 👇 of the article too 😉

What is Clean Data?

In Excel and Google Sheets, the data that we work with is located in cells. In a perfect world, these cells contain properly formatted data like numbers, amounts, names, and other pieces of information.

However, we often encounter things in the cells which don't belong and which will prevent us from using that data in the way we need.

Things like non-printable characters, extra white spaces and letters in cells that should contain numbers are a few examples of unclean data which will negatively affect our work.

What are non-printable characters, you ask? They are the first 32 control characters in the ASCII table.

Check out the table below of the ASCII characters. The first 32 are non-printable control codes. These can cause issues if they somehow make it into your data set.

screenshot of ascii table in Excel

You can generate this table by using the character function for all the numbers from 0 to 255: =CHAR(<number>).

The other common offenders are spaces that shouldn't be there – leading or trailing spaces in a cell. Or simply spaces in the middle that shouldn't be there.

Compound these finicky cells throughout a spreadsheet that contains thousands or millions of cells, and we've got quite a mess on our hands.

a woman waving hands and saying, "this is a mess"

Why Isn't the Data Clean?

Because we live in a fallen world.

🤣 Well, it's not that dramatic. But there can be many reasons the data isn't already clean. Many times human error is the culprit.

Whoever or wherever you're getting your data from simply made some mistakes with it before you got your hands on it.

Or maybe you messed it up when you started to manipulate the data.

As we'll see in the example below, the data could be perfectly fine wherever you're getting it from on the internet. But then when you import it into your spreadsheet, the conversion from HTML to Spreadsheet brings in a bunch of non-printable characters and spaces.

And, of course, because we're dealing with computers and people and data, we may never figure out why the data we've received isn't clean. It simply isn't. And despite our confusion, we have to clean it up to use it and extract meaning from it.

Obi Wan visibly confused

How to Clean Data in Excel and Google Sheets

Let's first get some data. In both Excel and Google Sheets, we can import data from the web. I want to bring in a table of Recipes and Ingredients from a video game website that looks like this online.

Here's Link...I mean, here's the link. ⚔️

Zelda recipe table

To address the obvious question first: yes, I could just copy and paste the table. And, yes, it will in this case paste the table straight into Excel.

But it'll also bring in the pictures which I don't need, the links which I don't want, some formatting that I'll have to reset, and potentially some of the non-printable characters and/or spaces that we'll discuss below.

Table of Recipe ingredients copied straight off website

Above is what it will look like after copying and pasting. But we are interested in preserving data and cleaning it, so we'll import it another way for the sake of the rest of our discussion.

If you're using Excel, it has some pretty robust cleaning features out of the box. When importing data from IGN, we enter the address where it lives, and it will pull down any data that it detects as available for import.

screenshot of data import from web in Excel

Let's get the table of Poultry and Meat Entrée receipts from Zelda: Breath of the Wild.

The Navigator window shows us a handy list of data tables detected on the page as well as a preview pane on the right that can be toggled between table and website views.

Excel data import navigator window screenshot

Excel Power Query

Once we've selected our data and loaded it, we'll have imported a table into our spreadsheet just fine. Excel's Power Query features allow us to then go into this particular table and extract the values in the Ingredients column into a list of items delimited by a selector of our choice.

In other words, Excel is smart enough to extract the individual list items in the Ingredients column and put them in a cell one by one and separated by commas (or whatever we select to separate them).

The following three screenshots show this process:

Screenshot of Excel Power Query menu

Screenshot of delimiter for expanded values

Screenshot of Expanded list values in Ingredient column

The end result is a comma separated list of values that is seemingly pretty clean. We can then split it up by using the =SPLIT() function into separate cells if we choose, or simply use it as is.

This is an ideal situation. But what if we encounter those spaces and non-printable characters?

Clean and Trim Functions

Here's a screenshot of the same table when imported into Google Sheets with =IMPORTHTML(). It pulls the data correctly, but you can see the extra spaces that were also brought into the sheet.

Imported data from webpage in Google Sheets

By using =CLEAN() on the ingredients cells, we can get rid of some non-printable carriage returns causing the line breaks.

Table after Cleaning cells

By using =TRIM() on the cells, we can get rid of all the leading spaces.

Table after Trimming white spaces

And by nesting the two with =CLEAN(TRIM()), we can do both. The result is a list of values separated by dashes. Similarly to our resultant table in Excel where we were left with a comma-separated list, we can then go and =SPLIT() these values further if need be.

Table after cleaning and trimming at once

These functions work the same in Excel, but for illustrative purposes we used Google Sheets since it wasn't able to import the data as neatly there.

Sample Spreadsheet & Video Walkthrough

Here is a link to the sample Google Sheet that I made for this tutorial.

The first page is a table of the non-printable characters.

And the second page is the Zelda example we talked about. Feel free to make a copy of this spreadsheet if you'd like to mess around with it further (File -> Make a copy).

We'll be building a really cool project with some of this Zelda data soon...follow me on YouTube to keep an eye out for that.

Thanks

Thanks for reading, and I hope this has been useful for you. Come say hey 👋 on LinkedIn and YouTube, and I'll talk to you in the next article!

At the end of the article I'll give you two bonuses: a short video walkthrough of the methods we go over, as well as a breakdown of the differences between the Accounting format and the Currency format.

Excel screenshot with differently formatted columns of numbers

We'll go over these three primary ways to format cells in Excel, and how to use each one to add the Accounting format to cells.

1. Point and click on Home Tab in Ribbon
2. Ctrl + 1 opens format cells window
3. Keyboard Combos

Easy Mode: Point and Click

As with most things in Excel, you can indeed simply select the cell(s) you'd like to apply the Accounting format to and click the appropriate selection.

Select the Home tab from the toolbar at the top. The formatting options are found in the Number section on the Ribbon.

The Accounting format actually has its own shortcut button marked by the dollar sign.

Excel screenshot of Ribbon

You may also select the drop-down menu to see the list of options:

Excel screenshot of number format options

easy as that gif

How to Use the Format Cell Menu

The selections above included the most common number formats, but you can also open the full Format Cells window by clicking the arrow in the bottom right corner of the Number section on the Ribbon.

Excel screenshot of format cell menu selection

As with all good things in computer life, there is also a shortcut to open the Format cell menu.

Press CTRL + 1 to open the full menu.

Excel screenshot of Format Cells menu

From here you can again select the Accounting format and also adjust additional options like what type of money symbol and how many decimal places to use.

How to Use the Keyboard Combo

Excel includes keyboard combos for every imaginable action. In many cases this can save time spent searching for and clicking the buttons on the Ribbon.

Keyboard combos start by pressing the ALT key. As soon as you press it, you'll see a ton of letters pop up on the toolbar and Ribbon.

Excel screenshot of ALT keyboard combo letters in Ribbon

The Keyboard Combo for Accounting Format is ALT, H,AN:

And after you press those buttons (one after the other), you'll have selected the drop-down for Accounting format. The most commonly used currency symbols are displayed. ENTER will select the English dollar sign, or you can arrow down to another selection.

You can see from the screenshot above that you can also press the combo ALT,H,FM to open the full Format Cells window.

The keyboard combos offer the most nimble way to access operations and menus in Excel. Learning the ones you'll use most often can greatly improve your productivity.

As promised, here's an overview video walking through the topics I've discussed in this article:

Video Walkthrough

Accounting vs Currency Format

So, why use Accounting format? Isn't it just formatting the numbers with dollar signs?

That's a half truth. Yes, it adds a dollar sign, but so does the Currency format. If we open up the Format Cells window again by pressing CTRL + 1, we'll see that the main difference is one of alignment.

The Currency format is used for "general monetary values" and puts the dollar sign directly in front of the left-most number. The Accounting format, on the other hand, lines up both the dollar sign as well as the decimal in the column of numbers.

In terms of formatting, Currency will also give you options for how to display negative numbers. But in the Accounting format, the negative numbers will always be displayed within parenthesis.

Format cells menu in Excel

Thanks for reading!

I hope this has been helpful for you.

Come find me on YouTube where I'm growing my coding and spreadsheet tutorial channel. And come follow and say hey over on LinkedIn. 👋

Have a great one!