Quickly sum non-contiguous cells
Sometimes you may want to calculate the sum of a group of non-continuous or non-contiguous cells. Do you enter in each cell address like =A2+C2+G2+I2? That can take a lot of time, if you have to add many cells.
Click the AutoSum button ?. Then click on each of the cells which you want to add, and ensure that you type in a comma to separate each cell in the formula. So this turns out to be fast as you are clicking the cells with the mouse and pressing the comma with your other hand. So an example is =Sum(A2,C2,G2,I2)
Alternatively, you can hold down the CTRL key as you are selecting the individual cells. You do not have to press the comma in this case.
If there are some contiguous cells in your formula, you can also drag to select them. For e.g =sum(A2,B2:D2,H2).
Picture below shows the Autosum option in 2007 under the Home Menu.
Resize a Comment
Now let’s talk about a couple of things you can do with the comments once they have been added. Firstly, let’s just simply resize the comment. To do that, click and drag one of the handles on the corners or sides of the comment box.
Copy Comments to Different Cells
If you want to copy a comment from one cell to another, it’s also fairly-straight-forward. Just select the cell and then press CTRL + C to copy the contents. Next, go to the other cell, right-click and choose Paste Special.
Select Comments from the list and only the comments will be inserted into the new cell. Anything else currently residing in the cell will remain the same.
Change Windows User to Your Own Name
You may have noticed in the screenshots above that some of the comments start out with “Windows User” and that’s because the copy of Office is registered to that name by default. You can change this by going to File, clicking on Options and then clicking on General.
You can leave it blank if you want nothing to appear in the comment or change it to whatever text you like. This will appear at the top of all comments by default.
Remove Comment Indicator From Cells
Lastly, what if you want to hide those little red triangles from the top of the cells blocks even if a comment does exist? Well, that’s easy also. Go to File, Options and then Advanced.
Scroll down to Display and then you’ll see a section called For cells with comments, show: and here you can choose from three options: No comment or indicators, Indicators only, and comments on hover or Comments and indicators.
That’s about all there is you can do with comments in Excel so hopefully this will make you look like an Excel pro at your office. If you have any qestions, post a comment. Enjoy
Link Chart Titles to a Worksheet Cell
It’s not hard to link worksheet cells to a chart title. Here’s how to do it,
1. Create chart from data in B7:D13 and deleted background lines for clarity.
2. Add a title by choosing Chart Tools Layout tab > Labels > Chart Title > Above Chart.
3. Select the Chart title object by clicking on it. Do not type a new chart title.
4. While the title object is selected, click in the formula bar and type an equal sign, = , then click on cell F2 containing the title in the worksheet and press Enter.
5. The worksheet title will update to show the cell content.
Try changing the cell content and you’ll see the title change.
But, wait! There’s more…
Using what you’ve just learned you can link the chart title to any cell content. In some older versions of Excel you can even enter complex formulas in the title object’s formula bar. However, some versions will not accept a complex formula for the title. But, there’s a way around that shortcoming.
To create fancy dynamic titles that work with any version of Excel follow these steps,
6. In cell F4, enter the following formula,
=$F$2&CHAR(10)&TEXT($F$3,”mmm d, yyyy”)
The result in cell F4 from this formula should look like,
Regional RevenueApr 20, 2014
There should be no space between Regional Revenue and the date. That “no space” is where the CHAR(10) is and this hidden space actually contains a little magical that does show up in the chart.
7. Select the chart’s title again and in the formula bar, type an “=”, and click on cell F4. Again, this will link the chart title to the cell F4. The formula should look similar to this,
=’Dynamic Chart Titles – Dates’!$F$4
8. Press Enter and you’ll see a dynamic title that changes as cell F4 contents change. This title should also contain a line break because of the CHAR(10) in the formula.
Click the picture to expand it to see more detail.
Here’s the formula in F4,
=$F$2&CHAR(10)&TEXT($F$3,”mmm d, yyyy”)
The & (used as a shortcut for the CONCATENATE function) joins together cell contents and turns the result into text. CHAR(10) returns the Line Feed character from your computer’s standard character set. This is a character used by printers, but here it works to create a line break.
The TEXT function used in the formula converts the numeric date stored in the cell into a text date with a format like that shown within quotes. The TEXT function is very handy whenever you’re creating reports in Excel that need to combine text with a currency or date in a special format. There are a bazillion different formats you can create with TEXT. If you want some examples of valid ways to specify formats in TEXT, right click on a cell and choose Format Cells, then look in Custom Category of the Format Cells dialog box for examples.
Notice that within the TEXT function the custom format must be inside quotes. You can create many custom date, currency, and numeric formats using these special characters with the Format Cells dialog or within the TEXT function; however, that is a topic for another article.
Share the power…