Insights

Excel: The Power of a Grid

Excel: The Power of a Grid

Excel. Some people love it, some despise it, and many fall somewhere in the middle. Often, users build bad habits in working with Excel that lead to an inefficient use of the tool or dissatisfaction with it. Yet by changing a few habits and unlocking your imagination about everything it can accomplish, you will discover that Excel can fully be the software you need it to be.

This article provides ways to stop those bad habits and start unleashing the power of Excel.

Stop using the .xls format. Start using the .xlsx and .xlsm formats.

Many Excel users continually reuse documents from year to year, project to project. However, Microsoft has upgraded the file extension of its Office Suite, transitioning Excel files from the .xls format to the .xlsx and .xlsm formats. Why is this important? In short, for the ability to use all of the features in Excel, since the .xls format may limit you. So how do you know when to use the two updated formats?

  • The .xlsm format is for the more advanced users who have created macros. Macros can be useful, but many people will never create them or even use them in their professional lives. If you are curious about what they are and what they can do, click this link for an Excel macros guide.
  • The .xlsx format is the more commonly used extension. 

If you have workbooks that are still using the .xls format, it might be best to upgrade your file extension to the .xlsx format to take advantage of all the tools within Excel. Click here for more information.

Stop applying filters and freezes to rows. Start using formatted Excel tables.

One of the best features in Excel is how you can quickly format your data table so it is easier to read and manipulate. Before there were Excel tables, users would freeze panes and apply filtering to the top row. All of this now happens automatically by using an Excel table.

To use the new feature, highlight your data (easily done by pressing CTRL+A) and then click the “format as table” button. This new format will allow Excel to easily format your data and add items. For instance:

  1. It will auto-name the data range. The first name it will use is “table1”; the second time you format as a table in the same workbook, it will call it “table2.” This allows you to easily reference the data in a lookup formula or easily add data to your table.
  2. You can color the header in one color, then all the odd-numbered rows in another color and all the even-numbered rows in yet another color. This makes it easy to follow from left to right in large tables. 
  3. It also automatically inserts the filter buttons at the top of each column for quickly sorting your data.

Click this link for more information.

Stop manually totaling data. Start using pivot tables.

Some users will use the “sum” formula on a column or the “COUNTIF” formula to count the number of times a value shows in a column. What they are missing is that pivot tables allow you to subtotal, count, and more with minimal keystrokes. From my perspective, it’s the most powerful component of Excel. Click here for information on using pivot tables.

I always start by formatting my source data as a table (see previous section), and then I create my pivot table. This can be done by clicking in your source data and then going to InsertPivotTable. (Select “insert the pivot table into a new sheet” at the prompt.) Once you have your pivot table, you can easily drop in the rows, columns, and data from the PivotTable Fields list that you want to get a closer look at.

Another benefit of setting up your source data as a “formatted Excel table” is the ease of adding a column to the source data. For example, you might want a data point be both a “report filter” and a “row label” in a pivot table. In this case, you would need to add a column to your source data and copy and paste the column you want to use twice, since Excel allows you to use a column only once within a pivot table.

Stop slowing down. Start using shortcuts to complete tasks faster.

Switching between your mouse and keyboard can be time consuming when you are constantly bouncing between the two. The good news is there are many shortcuts within applications these days. From quick access bars to keyboard shortcuts, there are multiple ways to put the features you use most right at your fingertips. The quick access toolbar is found at the very top of the Excel window. You can right-click on most of the buttons in Excel and choose “add to quick access toolbar.” However, my favorite time-saving method is learning the keyboard shortcuts. For instance, you can press these keys to perform the following functions:

  • F2 to edit the cell formula you are in 
  • CNTRL+Home to jump to cell A1
  • CNTRL+A to select all the data in your table
  • CNTRL+SHIFT+Down Arrow to select all the data in your column
  • End+Right Arrow to jump to the last column of your table

Try hovering over the command in Excel, and the hover-over menu might tell you the shortcut key for it. However, not all buttons have shortcuts, and not all tasks have buttons. (For example, the ones listed above are not found as a button in Excel.) Click this link to learn more about keyboard shortcuts.

Want more help? 

There are many avenues for obtaining more information. Try starting with a Google search using keywords of subjects you’re trying to tackle. There are numerous online community discussions about working in Excel. I recently conducted a Google search on how to apply conditional formatting on data in an adjacent column by typing in, “conditional format Excel based on other column.” I found this link, where someone replied to this same question, posted in an online community. In my spreadsheet, I can now have cells highlighted based on a value in another column. Sounds simple, and the method certainly was, but I just needed that little bit of guidance.

You can also search YouTube, which offers videos on all the buttons and mouse clicks you might need to make you more efficient in and more in love with Excel and all its powers.

Author(s)

Richard McClure
Richard McClure
Manager
View Profile