|
|
If you have similar data on two or more worksheets, you might want to combine that data in a pivot table, to show the summarized results. Unfortunately, the pivot table from data on multiple sheets can be a disappointment.
Create a Pivot Table with Programming
A couple of years ago, Excel MVP, Kirill Lapin (KL), shared a sample file that he created, with amendments by Hector Miguel Orozco Diaz. It uses code to automatically create a pivot table from multiple sheets in a workbook.
You can read the details here: Create a Pivot Table from Multiple Sheets.
Revised Solution
Kirill's sample file was created as a conceptual prototype, and targeted advanced VBA users. The code has minimal error handling and compatibility checks.
However, the sample file was extremely popular, and Excel users at all skill levels wanted to adopt this solution in their own applications. To make things easier,
Continue reading Excel Pivot Table from Multiple Sheets Update
When you insert a comment in Excel, a rather boring yellow rectangle appears, where you can add your text.
That's all very proper and dignified, but sometimes you want something a bit more attention-getting.
In the good old days of Excel 2003, it was easy to change the comment shape, with a simple right-click. In Excel 2007 and Excel 2010, you need to add a command to the QAT, so you can change the comment shape.
Add the Change Shape Command to the QAT At the right end of the QAT, click the drop down arrow Click More Commands In the Choose Commands From drop down, click All Commands In the list of commands, click Change Shape, and click Add, to move it to the Quick Access Toolbar Close the Excel Options window. Change the Comment Shape Right-click the cell which contains the comment. Choose Edit Comment
Continue reading Change Excel Comment Shape
Uh-oh! It's almost Christmas and you haven't mailed any greeting cards yet. Don't worry, you can create a personalized Excel message box instead! That will warm your co-workers' hearts, and it saves paper and postage costs too.
Get the Application UserName
In Excel VBA, you can use Application.UserName to get the registration name for the Microsoft Office applications.
However, some people don't update that setting, and it might show a company name, instead of the user's name.
Get the Network UserName
Another option is to get the network user name, with a Windows API call. There is sample code in the Microsoft Knowledgebase: Visual Basic Procedure to Get Current User Name, and I've used a variation on that code in my sample file.
You can find other API code examples on the VB.Net site, and API code on The Access Web, to pull more information, such as
Continue reading Show Personalized Excel Message Box
I'm just getting back from a vacation in South Carolina, where I didn't use Excel too often, except to add up all my Christmas shopping expenses. If you're on my shopping list, you might be getting a basket from the Charleston market.
Or maybe you'd like some of the local barbeque sauce or a bag of grits?
Christmas Planner
There are only 10 days until Christmas, and I'm almost ready. How about you? If you're still planning and shopping, there is an Excel Christmas planner on the Contextures website, that you can use to help you stay organized.
The Excel Christmas planner has a budget sheet, where you can plan and track your spending.
Christmas Gift List
If you're lucky, you don't have too many gifts to buy. But even for a few gifts, it helps to make a list to keep track
Continue reading Excel Christmas Planner
If other people will be using the Excel files that you build, it might help them if you add some instructions in a Text Box. After you add the text, drag the handles to adjust the Text Box size, so all the instructions are visible.
If you plan to edit the text occasionally, or change the text formatting, you can set the Text Box to resize automatically.
Right-click on the Text Box, and click Format Shape, Then, click the Text Box category, and add a check mark to Resize Shape to Fit Text.
In Excel 2007, the Text Box loses its Wrap Text setting, if you turn on the auto resize. Instead of a narrow text box, you'll often end up with a really wide one.
Tip: Add a few manual line breaks, if you want the Text Box to be narrower.
Excel 2010 Auto Resize With
Continue reading Auto Resize Excel Text Boxes
To create a chart in Excel, you can select the data on the worksheet, then use the Ribbon commands to insert the chart.
Use the Keyboard Shortcuts
If you'd rather use the keyboard to insert a chart, there are a couple of shortcut keys that you can tap.
To insert a new sheet in the workbook, with a chart for the selected data, you can press the F11 key.
To insert the chart on the active worksheet, use the Alt + F1 shortcut keys
Change the Default Chart Type
When you create a chart using the keyboard shortcuts, the default chart type is used. To select a different default chart type:
On the Excel Ribbon, click the Insert tab
In the Charts group, click the Dialog Launcher button, at the bottom right
Select one of the chart types, and a Sub-type, then
Continue reading Create Excel Chart With Shortcut Keys
Do you ever right-click on something in Excel, and the command that you wanted to use isn't on that pop-up menu? For example, if right-click on a cell, there is no command to turn off the gridlines.
If you can't find the command on the right-click menu, you have to go to the Excel Ribbon or Quick Access Toolbar (QAT) instead, and try to find the command on one of the tabs there.
What commands would you add to a right-click menu? Are there commands that you added to the QAT, that would be even better in a popup menu?
Customize the Right-Click Menus
Fortunately, Doug Glancy has created a solution to the right-click menu problem, with his MenuRighter Add-in. With Doug's free add-in, you can add commands to the pop-up menus, so the items that you need are easy to find.
The add-in is not for sissies! You
Continue reading Customize Excel Right-Click Menus
|
|
Recent Comments