Learn Any Area of Excel using these 80 Links

Last week I asked, What is one area of Excel you want to learn more?

More than 250 of you responded to this question. Many of you shared your areas of interest thru comments, quite a few of you also emailed me personally.

You told us what you want to learn, the next step is logical. We share some of the best tutorials & examples with you so that you can learn. In this post, we have presented more than 75 links, to help you learn your area of focus.

I have divided this in to 16 areas. In each area, we have identified (upto) 5 best links for you to learn more. I have also recommended 1 or 2 training programs that make you awesome in that area. Plus, if we found any excellent external resources, we have highlighted them as well.

So go ahead and learn Excel.

If you come across any good resource for learning Excel, please share it with us. I am always looking for ways to learn more. So go ahead and drop a comment.

Special thanks to Hui, for compiling the survey results & some of the links.

RSS feed for comments on this post. TrackBack URI

View the original article here

Switch "dynamically scenarios using wemshrohat

Posted on 1 June 2011 in Pivot Tables & charts-3 comments

Wemshrohat is the new favorite in Excel attributes. And in 2010 in Excel, such as optical filters are wemshrohat.

Let us be your sales report (link) to multiple vendors. Because you want to show the report to one person at a time, you can use report filters in the pivot table for this view. But find that switching between areas of pain using report filter.

Enter wemshrohat.

Now, just click the name of the region to show report for that region, such as this:

Now, we use wemshrohat Creative Director to make an interactive scenario in Excel, some thing like this:

This method gives the same result as displayed and select scenarios using VBA article, but easier to implement

You need to specify different scenarios in table, like this:

Select the table that you created in step 1, then insert a PivotTable. Use a variable name and variable value row label in the value field.

Select anywhere inside the axis. And now, on the Options tab, click the button "insert morgue". Click to insert a slicer field scenarios.

I'm beyond interpretation creates a form that is not relevant here.

Once you set up the form, simply refer to the pivot table for all values of variables.

Go to PivotTable worksheet and select in the morgue, click CTRL + X to cut it.

Return to the worksheet form and paste it into a morgue.

Wemshrohat Excel by default shows the option to remove the filtered morgue. You can get rid of this button,

1) right click on the slicer
2) go to the slicer settings
3) UN option "show header check

See aside.

That's it, we transform smart scenario morgue ready. Now, you can extend this in several ways. For example, you can write some clever formulas of handling multiple selection wemshrohat. You can compare one scenario and another when you choose one or more of the morgue. Be much more. But let your imagination run wild.

I have made a simple example to demonstrate this technique.

Please download and open it in Excel 2010.

Screening action "scenario" and "axis" model for understanding how morgue setting, and how to do this.

As I said, is the favorite new attributes wemshrohat in Excel. And may use them as much as possible because it is easy to use and very powerful.

What about you? Do you frequently slide? What was your experience like? Please share ideas and tips with your comments.

1) create a "dashboard" in Excel using wemshrohat
2) create a dynamic chart using PivotTable report filters "
3) remove duplicates and sort the list by using the pivot table
4) more on modeling & pivot tables

RSS feed for comments on this post. TrackBack URI

View the original article here

Format column sparkline charts by using the data and merge cells

Sparklines are great, but there may be times that they need a little visual massaging to maximize their usefulness. Consider this scenario: you have a system for which you want to track downtime over the course of a year and (fortunately) had the events of downtime in only four of the twelve months. Here'sdata: the data, I created this column sparkline.Okay, but it does not highlight the fact that there are no events in January and February and none from June to November. This is because there is no axis Data. I can remedy this axis setting of an option for the sparkline horizontal (or x-) by selecting the option date axis type . Here is the option in Excel (just make sure your sparkline is selected):select date axis type and in the Sparkline Date range dialog box that appears, I'll be sure to select the cells that contain dates (not the data itself). Now, you can see the sparkline exhibition space that reflectsmonths missing:, but I'd like to make a few more things: I want the sparkline largest, which extends through the four columns of data, and I also want my axis labels for the columns under horizontal sparkline.I do this by selecting the cell that contains the cell, and then the sparkline next three cells to the right with it. I'll use the command Merge through lengthen the sparkline on B3: E3. Now it looks likethis: Finally, I'll add text labels for March, April, may and Dec under the sparkline. I type "Mar Apr may" (separated by spaces) in cell B4, which is under the three-column sparkline, and use the command Merge across to merge the cell C4. You type the "Dec" in cell E4, under the last column sparkline. I will make them smaller and bold font (I used 8 pt) and then put the text in the cell by adding or removing spaces between Mar, Apr & may, until they line up nicely under the three-column sparkline. There!For more information about sparklines, see using sparklines to show trends in data. Look for more blog posts about sparklines here via the keyword "sparklines".

View the original article here

Microsoft Excel 2007 using the settings page

It is important to attend to get the right result from a spreadsheet before printing or distributing a worksheet verify some details.

You must first set the size of the worksheet. To initiate, in page layout view, if necessary, zoom to see. All changes this page layout to display properly it is necessary to clarify, no pointless.

Now navigate to the page layout tab, click the margins button. Select the settings for the margins you want. You must click OK to adjust the margins, click Custom margins, click the margins tab in the page Setup dialog box settings checks complete.
After this, overall, or less becomes can also be longer than the landscape portrait by clicking the arrow buttons, long. Orientation in the gallery.

Select the paper size you want then click the size button. In order to change the size of the worksheet to print if you fit a specific number of pages worksheet force scaled specifies or. Make sure you also specify whether to print the view or grid lines or headings.

Go to time now complete your page size is set, to printing and office menu. Preview Gallery that appears, click document how it expect to confirm whether or not it was visible. Once complete, close print preview, you click to return to page layout view, if necessary, adjust the final layout's.

Editor's Note: Daniel Blinman computer training solutions are recommended for IT training firm Centre London, Bristol, Swindon Solihull and reading. PowerPoint course Excel course to provide computer training solutions call 0800 019, 6882 for course details.

By using Microsoft Excel 2007 data graphically.

On how clear and understandable than you may think using a well-designed chart to display data. Comparison of picture clarity and difficult concepts to provide visual effects are apparent immediately. Need to determine the type of chart everything and shows the best information for what to char.

To create a chart, you select the data first, contained in all the graphs you want. You must click the chart to use when you do this, go to Insert tab and type. You must choose a graph Design Gallery displays a list from. Go to design tab of the chart graph tool and selecting chart layout, and click.

Graphs have found how to do is a couple of adjustments that you can. First, change the chart type, you can select in the dialog box displays a different type of chart, and then click the button. Or, to change how you want to plot the data, you can button switch rows and columns.

Does to graphs that appear on its own worksheet, and then move chart, and choose new sheet in the move dialog box, click OK, click. You should now all data graphs are immediately create effect to verify. Easy to evaluate and compare the data by viewing the data in the chart.

Editor's Note: Daniel Blinman computer training solutions are recommended for IT training firm Centre London, Bristol, Swindon Solihull and reading. PowerPoint course Excel course to provide computer training solutions call 0800 019, 6882 for course details.

Things you should know about working with Excel Excel 10

Microsoft Excel is considered the best spreadsheet application. However, self-describing graphical user interface and embedded help documentation by using this application is easy, is that many are known to all users. All information about the range of maximum rows, columns, cells, columns, formula number are known to Excel users.

This article is the same idea in mind are scriptable. After reading this article, didn't hear ago some important facts that may know about MS Excel 2007. This application must know about 10 of these facts are as follows.
On the Excel sheet, 048, 576 lines, 16,384 columns, and 17 includes more than 100 million cells. Column width can enter up to 255 characters or column can be up to 255 characters. 255 Characters for headers and footers Excel 2007. Excel sheet can be split into panes up to four [be able to add a file that you created by using MS Excel 2007 sheets 64,000 in.xls or.xlsx. These sheets are cross references available. Up to 256 users, open the already people working in shared mode shared the.xls or.xlsx files can be shared at the same time. Data form - MS Excel 2007's most commonly used features. Often used for form collects information in a fast and perfect way. In the data form, up to 32 fields, SR., such as name, address 1, address line 2, contact numbers like that, you can add. List of functions that can be used is very large. You can use various features, such as subtraction here, calculations in a spreadsheet application, more than 300 built-in functions is provided. Some features available in total, average, maximum, minimum, and, or, true of the other, such as. Functions can be nested up to 64 levels. You must undo any recent use the spreadsheet entries. You can cancel up to 100 times the MS Excel 2007 action. Excel sheet from 10% to 400% as required expanded can be. For example, if you simply viewing data in big mode, the file to grow. When to create a function in a spreadsheet application, cannot be added more than 8192 characters.

These are 10 MS Excel 2007-must know the facts about a simple spreadsheet application developed by Microsoft Corporation is.

About the author

Hi Deepak Gupta, technical writer, content writer SEO since July 9, 2007 is. To prepare the technical writer, my work profile software user guide, installation guide, SEO friendly article writing, writing blog posts, write summary of simple and effective software software Web site, and so is.

Away from work, I also write articles on a topic my knowledge like that there. If you too, for free for you to write the guptadeepak2353@gmail.commy email I like the blood collection action.

Topics of interest for me, SEO, website contents, health & fitness, disease, software, Internet, social networking is.

See PLS, my blog http://myhealthsol.blogspot.com/.

Slicers control using VBA

Author of today is Jan Karel Pieterse, an Excel MVP, which explains how to use the SlicerCache object and how to customize the buttons or change the properties of the slicer using VBA. For more information about slicing machines, read the PivotTable slicers on the website of Jan Karel: http://www.jkp-ads.com/.

For each slicer that you add to your workbook, Excel adds an object SlicerCache too, which controls which pivot table slicer controls.

So suppose we three sheets, Sheet1, Sheet2, and Sheet3. Each sheet has a pivot table and Pivot tables are all based on a PivotCache. As soon as you add a pivot table slicer every (even if cutting ties to the same field of the PivotTable) you get three objects of SlicerCache:

Hierarchy of cutting and his family

The following code enumerates all slicer cache in the workbook:

Sub MultiplePivotSlicerCaches ()
Dim oSlicer as Slicer
Dim oSlicercache as SlicerCache
Dim opt as a pivot table
Dim SSL as worksheet
For each oSlicercache In ThisWorkbook. SlicerCaches
For each opt-in PivotTables oSlicercache.
oPT.Parent.Activate
MsgBox oSlicercache .name & "" & oPT.Parent.Name
Next
Next
End Sub

As soon as it occurs more than one PivotTable PivotTable dialog box of a cutting connections, slicing machines concerned will share a single object of SlicerCache. The SlicerCache object will be removed from the collection. This explains why can go back by taking away all but a pivot table in the dialog box: all together now slicers will be changed by changing PivotTables checked on them. In turn, each selected PivotTable becomes part of the collection of the SlicerCache rest PivotTables.

If you decide to select Slicer1 and change the pin connections by checking whether Pivottable1, Pivottable2, and a slicercache is deleted (the one that belongs to the PivotTable is controlled to add the current slicer). So the hierarchy changes to:

Hierarchy changed slicers

So that it is Slicer1 that Slicer2 check PivotTables, 1 and 2. Slicing machines 1 and 2 shall be synchronized too, because in reality it is the SlicerCache that has changed with the slicing machine. So the hierarchy in the picture above is not completely true.

It's pretty easy to change the appearance of the button using some VBA:

Sub AdjustSlicerButtonDimensions ()
With ActiveWorkbook. SlicerCaches ("Slicer_City2").Slicers ("City 2")
..NumberOfColumns = 3
.RowHeight = 13
.ColumnWidth = 70
«Note that changing the ColumnWidth property also affects the width of the slicing machine
' So that the next line will change the ColumnWidth!
.Width = 300
Ends with
End Sub

Note that the numbers do not coincide with what is shown in the Ribbon. Apparently, the unit of measure distinguishes between VBA and the Ribbon.

Change certain aspects of your slicer using VBA is not hard to do. In fact, the macro recorder is relatively easy to find out how it works. After changing some settings and doing a bit of restructuring I:

Sub AdjustSlicerSettings ()
With ActiveWorkbook. SlicerCaches ("Slicer_City2").Slicers ("City 2")
.Caption = "City"
.DisplayHeader = True
.Name = "City 2"
Ends with
With ActiveWorkbook. SlicerCaches ("Slicer_City2")
.CrossFilterType = xlSlicerNoCrossFilter
' xlSlicerCrossFilterShowItemsWithDataAtTop:
' Visually indicate items with no data, with data objects are pushed upwards
' xlSlicerCrossFilterShowItemsWithNoData:
' Visually indicate items with no data, items with no data are put
' xlSlicerNoCrossFilter:
' No indication for items with no data.
.SortItems = xlSlicerSortAscending
.SortUsingCustomLists = False
.ShowAllItems = False
This ensures that data is no longer pivot cache are not shown for sectioning
Ends with
End Sub

Well, I hope I got you started with getting your head around how they work and how to resolve them using slicers with VBA. Find a real gem slicers in Excel 2010. A great addition to the product!

--Jan Karel Pieterse

View the original article here

Pulling RSS data in Excel (or: using Excel to search Craigslist)-part 2

This blog post is brought to you by Dan Battagin Lead Program Manager on the Excel team.

Do you remember the last time we had built a spreadsheet that has tried to Craigslist via the RSS feed that is available for search results and pulled the information in Excel by using the XML mapping features little used.

Today, we will continue where we left off, adding the ability to search multiple sites and adding a little progress indicator if the search takes a long time.

Oh and by the end of this post, I've attached a version of the search tool that we invite you to use if you do not want to follow ... even if they do not give no promises as to the quality of the code-I'm sure there are optimizations that people can do, and I would love if you could send them back to me so I can learn from you!

Added the ability to search multiple sites in one fell swoop

Like every other step, this is not too complicated, it just takes some thought and time. Oh, and there I will be using VBA-Excel is great as is easily extended with VBA. To search multiple sites, we're going to create a table that lists all Craigslist sites that want to try, and then we will simply scroll this table whenever the user clicks the search button, grab the RSS results and adding them to our table XML that yesterday we setup. Then, let's start:

1. Switch to Sheet2 and adding a table (named Table2) with the following columns: "baseurl", "Research" and "fullurl"

2. next, add these formulas in the first row of the table:

= "/search/? query = "& rngSearchTerm &" & catAbb = sss & format = rss & areaID = 2 & subAreaID = "

3. in column BaseUrl, enter the base Url for some sites to Craigslist. I use these (make sure the fill down formulas from above in each of these lines:

This is all we have to do something for the table, now we just need to update our VBA RunSearch method to iterate through these different sites when you search using the FullUrl from our table above as the location to get RSS information. We will actually replace all VBA code that we created last time, by following these steps:

Click Developer | Visual Basic to open the VBA editor.If it is not already open, open Module1. Insert the following VBA subroutine to replace the search we created yesterday. Note that this is not much changed-lines that have changed since the last time that I highlighted:

Close the VBA editor, and with this, now you'll see that each time you click search, Excel chugs to seconds (more, depending on how many sites Search) and see all the search results displayed in the grid, added one after another. Regularity. Now if only Excel was more responsive, while research was happening ...

Adding a progress bar so that we know as the search will

We're almost there-we have our data coming through XML maps (with parameters with a search box) and displays the data for multiple sites. Just a couple more small VBA tweaks and there are a fairly functional research tool.

To add a progress indicator, we're just going to write "% done" in a worksheet cell. Sure, we could build a progress bar or something else, but this is Excel j we're talking about here. Since then we have a cycle who knows how many sites, we are searching, and the site in which we are currently in, should be as simple as dividing the two values and update the cell. Let's get started.

On Sheet1 (which shows the results of the search), select the cell to G3Format as a percentageClick formulas | Define the name and call the range "rngStatus" without quotes.Click Developer | Visual Basic and find upgrading subroutine attempts to have the following code. Note that, not much of this has changed-I highlighted lines that have changed since our last step:

And you're done! When you search, you'll see the progress bar update (and your search results update) checks for each site. Pretty cool if you ask me.

Of course, there are a lot of tinkering, you can make that make it even more interesting solution or clean up the code. I did a couple of them in the workbook that is linked to this post (added a stop button, did some additional factoring code, did a bit of formatting), but I'm sure others will have even more pleasant that tweaks can do. If some do, drop me a note with the changes-why use this workbook in real life! J

Cheers!

View the original article here