Office

You are currently browsing the archive for the Office category.

Connecting Shapes In Word

February 16, 2013 in Office | No comments

The other day I needed to draw a quick flow chart while I was writing some documentation. I didn’t want to go through the trouble of opening up Visio to do that – in fact, the computer on which I was doing this didn’t even have Visio installed – and decided to give the shapes in Word a try. And it wasn’t even a real flow chart which I needed, I just wanted some boxes with text that could be connected using arrows.

The screenshots in this post are created using Microsoft Word 2010.

So I used the Shapes dropdown from the Insert menu to add two Rounded Rectangles to my document.

Adding shapes to a Word document

From that same menu, I used the arrow (second item in the Lines items) and tried to connect the two rectangles, ending up with this:

Arrow doesn't stick to the rectangles

Okay, I dragged the rectangles around a bit for demonstration purposes, but I’m sure you can all see that the arrow is not connected to the boxes. I couldn’t find any way to get these items to connect with each other!

I almost gave up but then I thought:

Hang on, it can’t be that they’ve implemented this functionality to make it as useless as this, right??

So I decided to have a quick internet search and was glad to have found a solution. It turned out that I skipped a step. Apparently, to be able to connect shapes with each other, you first need to add a new Drawing Canvas to the document! That can be done using that same Shapes menu:

Adding a new Drawing Canvas to the Word document

After adding the Canvas, I repeated the process of adding rectangles and connecting them using an arrow, this time with more success!

Use a drawing canvas to connect shapes with each other

Many thanks to Nicholas Hebb for writing this How to Create a Flowchart in Word 2007 and Word 2010. Check out his article for more tips on flowcharting in Word!

Have fun!

Valentino.

Tags: Word

Office Tip: Show All Windows You Silly Thing!

February 27, 2012 in Office | No comments

Here’s a quick one about my favorite setting of the week. And for once it’s not related to SQL Server but a bit more general. It’s meant to improve the everyday use of a PC. Yet a bit more precisely, it’s got to do with Microsoft Office (2007 or 2010, either will do).

Ever since I got a PC at my client, I was annoyed by the fact that Word would open all documents in the same window. As a result of that, when I had two documents open, I couldn’t use ALT-TAB to switch between them. (I prefer using my apps maximized.) That really annoyed me. Furthermore, each time when I clicked the closing X at the top right of the screen, it would close down all open documents instead of the one in front of me. Aaarghh!!

And today I finally found the setting to make it behave decently. It’s not so complicated once you know where to find it. (In fact, it’s a simple checkbox.)

Curious? I’ll explain how to change the setting in Word, but Excel will work just as fine.

Open the Word Options by going to File > Options. Then switch to the Advanced page. Locate the Display section. In that section you’ll find a checkbox labeled Show all windows in the Taskbar. Guess what that does?!

Options: Advanced - Show all windows in the Taskbar

So that one more aggravation gone!

Have fun!

Valentino.

Tags: Excel, Office, Word

A Developer’s Excel: A Couple Of Tips

June 16, 2010 in Office, SQL Server | No comments

As a BI developer I regularly use Excel to analyze my data on correctness. In Excel it’s easy to filter out unrelated records, as you’ll probably already know.

How do I get my data in Excel?

With a simple copy/paste. Since SQL Server 2008, you can just right-click on the results in the Management Studio and select Copy with Headers. This will put the selected data into memory, including the column headers.

SSMS 2008: right-click on results to Copy with Headers

You can even make a selection in the Results pane and only copy that, as shown in the screenshot above. Here’s what it looks like when pasted into Excel:

Data with headers pasted into Excel

How do I make the columns wider?

Excel will not expand the column widths by default, shown in that last screenshot. You could manually enlarge them one by one, but that’s not convenient when you last filled column is called “DI”. Instead, I use the following trick. First I select the whole sheet by clicking on the grey square in the top-left corner:

Select the whole sheet using the grey square

Then I double-click on the vertical divider in between columns A and B. This will adjust all column widths so that the data fits nicely.

Double-click the divider in between two columns to adjust width automatically

Does Excel know SELECT DISTINCT?

The other day I needed to filter out all duplicate records out of a really large dataset. I first tried by activating the regular filter. This is an Excel feature that I use a lot. Just clicking the Filter button on the Data ribbon will add the small dropdowns next to each column’s header:

Excel's Filter functionality

But in those dropdowns I couldn’t find an option which would get me to the required result.

(Note that I added a couple of duplicate records in my dataset.)

After a little search, I found the magic checkbox! I didn’t know that it could be so easy.

To find it, click the Advanced button in the Data ribbon.

The Advanced button on the Data ribbon

In the Advanced Filter window that appears you see a checkbox called Unique records only.

Advanced Filter: Unique records only checkbox

There’s your SELECT DISTINCT!

And here’s the sheet with the duplicates filtered out:

Excel sheet after filtering out duplicate records with the "Unique records only" option

Do you also see that the filter dropdowns are gone, although I did not remove them? The Advanced Filter cannot be used in combination with the regular filter. Once you activate the regular filter by clicking the Filter button, your duplicates will re-appear.

If you want to further filter your data after filtering out all duplicate records, you should select the Copy to another location option in the Advanced Filter window. Copy it below your data on the same sheet and apply the regular filter on that new set of data.

Filtered set of data below full dataset on same sheet

Copying to another sheet is not supported. Apparently you can only copy to the active sheet. Should you try it out anyway, you’ll get this message:

Warning: You can only copy filtered data to the active sheet

Happy filtering!

Valentino.

Tags: Excel, SQL Server

The Excel Auto Fill Feature

February 20, 2010 in Office | 2 comments

A couple of days ago I discovered a very interesting double-click feature in Excel. One that probably already exists for ages – the oldest version that I was able to try it out on is Excel 2002 and it worked – but due to being used to other habits I just never found out about it. Until now.

When a cell (or a range of cells) is selected, you see a thicker black border around the selection and the bottom-right corner has got a small square attached.

One cell selected in Excel

You can drag that squared corner down to get copy-like behaviour. More precisely, when one cell is selected and you drag it down then the value of the selected cell gets copied over into the cells further down, up until the cell where you stop dragging.

The effect of dragging the squared corner with one cell selected

Note: the same effect also works horizontally but in real-life circumstances you probably won’t need it much.

When multiple cells are selected and you drag the bottom-right corner down, Excel will apply some logic to continue the series that it possibly detects.

For instance if you have two cells selected with the values 2 and 4, the next cells will get 6, 8, and so on. Or when the first cell contains 2010/10/30 and the second 2010/10/25, the next cells will get date values going down by 5 days per cell.

Excel's Auto Fill with two rows and two cells per row selected

If you don’t like the way the series gets applied, there’s a dropdown poping up at the bottom-right of the new selection at the moment that you stop dragging. In that list you get several different Auto Fill Options, depending on the data type of your selected cells.

The Auto Fill Options

I’m sure this is no news to you so far.

But do you know what happens when you just double-click that small bottom-right corner instead of dragging it?

I didn’t, until I just tried it out this past week. The reason that I tried this was because I needed a formula copied down in about 20,000 rows (hey, I’m a data guy, remember?) and I didn’t want to waste my time waiting for Excel to scroll down just to the bottom of the list while dragging the corner. So I double-clicked and there came the discovery of the week! It applied the same function as what you get when you drag the corner down, all the way down to the last row of data! Isn’t that great? From now on I think I’ll always just double-click instead of drag, much faster!

The Auto Fill effect after double-clicking the small squared corner (I still wonder what name they've given this feature - the Auto Fill Corner possibly?)