Five Excel Tips You'll Actually Use

you can use these five little-known tips to make the time you spend in Excel more efficient and productive.

Excel-tips-you-need-to-know.png

TRACK DOWN CELL DEPENDENCIES

In complex worksheets, where the value in one cell impacts several other cells and formulas, it’s helpful to understand the relationship between those cells—especially if it’s a spreadsheet you didn’t personally create. This tip will help you troubleshoot and understand what’s going on.

Select a cell in your worksheet and then press Ctrl-Shift-] (that’s Control-Shift-Right Bracket) and all of the cells that are dependent on the selected cell will be highlighted.

Bonus tip: With a cell selected, click on Trace Dependencies from the Formula ribbon and you’ll see lines from the cell to the dependent cells. Click Trace Dependencies a second time and you’ll see the dependencies of those cells.

You can even click on the dotted lines and see if the cell refers to a different worksheet or workbook.

USE FORMULATEXT AND THE N FUNCTION

Adding documentation to an Excel spreadsheet probably isn’t very high on your list of things to do at work today. However, spending a few seconds documenting the formulas in your worksheet can save you hours of combing through them a month (or longer) from now to remember how the spreadsheet was set up.

You can quickly document your worksheet using FORMULATEXT and the N function.
In the cell next to your formula (or any cell for that matter), use the function

=FORMULATEXT(cell number goes here)

This will display your formula, making it easy to know how you calculated that value.

Bonus tip: If you don’t want to display the formula on the worksheet but would prefer to describe the formula instead, tack on the N function at the end of your formula.

=(YOUR SUPER LONG FORMULA GOES HERE)+N(your formula description goes here)

The resulting value of the formula won’t change, but you have now described the formula for future reference!

CTRL AND THE SCROLL WHEEL

When you want to quickly zoom in on a section of your worksheet, skip the Zoom buttons on the View Ribbon.

Instead, hold down the Ctrl key on your keyboard and use the Scroll Wheel on your mouse to zoom in and out of your worksheet!

ONLY COPY VISIBLE DATA

When you only want to copy the visible data from a table that has hidden rows and or columns, press Alt-; (alt-semi colon) before you press Ctrl-C to copy.

When you press Alt-; first, it will tell Excel to only copy the visible data and exclude any data or values in hidden rows or columns.

USE DATE & TIME IN YOUR PIVOT TABLES

Using date and time as a metric is one of the most powerful ways to analyze data in a pivot table–except when your data is listed by day instead of Year, Month or Quarter. Here’s how to convert your dates.

In your pivot table, select one of the cells that contains a date in the day format (as seen above) and then Right Click and choose Group.

You can then select how you want to group your dates including by Months, Quarters and Years. Depending how granular your data is, you can choose other options such as Seconds, Minutes, Hours or Days.

Once you click OK, Excel will group your data in a more meaningful way so you can analyze it better and in turn, provide better insights to your business.

Move Every Other Row to a Column in Excel

When you need to move data from rows to columns, you need these two formulas.


Moving Every Other Row to a Column in Excel

I spend a lot of time in Google Analytics and a lot of time building presentations with charts using the data from GA.

Of course, there are times when we need to compare YoY or MoM and GA makes that very easy to do. However, when you export that data, it’s not in the best format to be turned into a chart or graph.

Here’s an example of what a Google Analytics data export comparing two periods of data looks like.

01-Original-Data-Export-600x498.jpg

The Problem

We don’t want two rows of data representing week one. We’d prefer to have two columns of data representing this.

Here’s an example of where we want this to go. We want to move every other row from Column C to Columns D and E.

The Solution

To solve this, we’ll use the Excel formulas ISODD and ISEVEN combined with the ROW function. Here we go.

We’ll assume that our data runs down multiple rows in Column C. Column B is listing our time interval. Our new data will live in Columns D and E.

We want to move every other row to it’s own column.

In the cell D2, write the following Excel formula.

=IF(ISEVEN(ROW(C2)),C2,””)

What this is saying in plain English is “If the row that cell C2 is in is Even, then place the value of cell C2 here. Otherwise, leave it blank.”

Next, in cell E2, write the following Excel formula.

=IF(ISODD(ROW(C3)),C3,””)

What this is saying in plain English is “If the row that cell C3 is in is Odd, then place the value of cell C3 here. Otherwise, leave it blank.”

Next, copy these formulas all the way down your data set in columns C and D. You can do this by dragging the corner of the first cell all the way down. Or you can use this Excel keyboard shortcut to select the cells first and then write the formula.

Removing the Blank Rows

This method will leave you with every other row being blank in columns D and E. To remove them, it’s very simple.

Insert a new row at the top of your list and give each column a heading name.

Click once on cell E1 and then click on the Filter button in the Excel toolbar.

In the filter settings for cell E1, uncheck the box that says “blanks”.

Just like that, all of the blank rows disappear. If you select your resulting list and copy it somewhere else, you won’t get the blank rows.

How to share a custom report in Google Analytics

It's not as simple as just copying and pasting the URL from your browser's address bar. But it's almost that simple. Here's how to share a custom report in Google Analytics.


Custom reports in Google Analytics are an incredibly powerful tool that allows for great flexibility to slice and dice your web site data in ways that are unique to your business goals and needs. If you’re looking how to share a custom report in Google Analytics, read on.

Once you’ve set up your custom report, you’ll probably want (or need) to share it with others within your organization. However, sharing a custom report in Google Analytics isn’t quite like sharing other content in your web browser.

At first, you might be tempted to just copy the URL from the address bar and then paste it into an email, Skype, Slack, etc. But when the person you sent this to tries to click on the link, they won’t get the report.

Instead, go to the Customization tab and find the report that you want to share.

Next, look in the far right column and click on the button labeled Actions and then click Share.

In the modal dialog that appears, select “Share template link” and click Share. This will give you a shareable URL that you can copy and paste to share with your colleagues.

Keep in mind that if your company has more than one profile in Google Analytics, you will want to make sure that anybody who uses your custom report, applies it to the same profile that you used to create it. If not, the data you get may not match the data others with the same report get.