I want to share with you 4 power user tricks to use while formatting Word documents. Styles make documents look orderly and save you a lot of time. If you are not using Styles for long documents, then you are probably fighting with Word. Styles can also make the document save faster when working with large amounts of copy and images.
1. Turn on the Apply Styles pane by clicking Control + Shift + S.
This will confirm the style you have selected and allows for applying a new style quickly.
2. View your styles using the draft view.
The default draft view does not show styles in the left side bar. To apply this you need to go to Word Options. (File–Options) Then click the Advanced option and scroll down about half way and make 1 inch or so of space next to the “Style area pane width in Draft and Outline Views. 1 inch or .5 inches is probably good enough.
3. Create a shortcut key to apply your style.
Right click the style and select modify style from either the Styles gallery or Style pane. After you see the Modify Style dialog box, click Format then click Shortcut key.
After the shortcut key is entered it will let you know if it conflicts with an existing default shortcut key. Click “Assign” to confirm.
4. Use the navigation pane to easily reflow content.
Turn on the navigation pane from the view tab check box. You can view all the heading styles and drag a heading up or down. All of the subheadings and body text will follow the leader heading style.
Download my Word keyboard shortcuts here. If you would like to have my future blog posts emailed to you then sign up from the desktop version of this site with entering your email address on the right side bar.
Posted in Word
North San Antonio Chamber is excited to present the Optimizing Business Series presentation…BEYOND EMAIL…Connecting People on Sept 9th from 11:30 to 1 PM at the Oak Hills Country Club.
Interactive Demos will focus on a variety of tools showcasing Web Conferencing and Chat, Video Creations, Building Digital Notebooks for your team, and collaboration in Real – Time among multiple device platforms.
Visit northsachamber.com to register online.
The Advanced Filter is a command in Excel that allows you to filter your data. It is useful when you have a list of criteria that you would like to use in your filtered record set. The Advanced Filter command is found on the Data Tab. You can either filter your data in place (default) or you can copy your filtered records to another location on the same worksheet.
The list range is your data set that you are starting with. You will want to make sure that your list range includes your header row. The criteria range will include at least one of your header labels and then the criteria below it. Optionally, you can check the box for unique records only.
This example I have a data set with the following header list: Region, Date, Category, Specs, and Number. I want to see the following records that contain the numbers 1101, 1102, 1103, 1104, and 1105. Yes, I could click the filter button and select the values from the checkboxes but that can be a lot of extra time spent scrolling up and down to get the correct ones. So instead just copy down the values you want to filter under the header. This will be someplace other than the dataset. It can be a list on the active sheet or another. In my example I have it in a different tab.
The best practice is to start by clicking your data set. Then click the advanced filter button. It only says Advanced but after you click it the advanced filter box will pop up. Verify that the list range is correct with the header row included. Click in the criteria range and then select the range. Make sure your header is typed exactly the same as the data set header. If you have one extra space after the header it can mess up the results. Click Ok if you are ready to filter the results in the original data. Click copy to another location then select a range on the active worksheet as the data set in which you want the top left cell to begin your extraction of the data filter.
The final result yields a filter where the row numbers turn blue if filtered in place. If I had filtered to another location then it would essential be an extra output. You would select that option when you want to leave the original data set alone.
Click here if you want to download my practice training file.
Click hereif you want to download my Excel keyboard shortcuts cheat sheet.
If you like this post and want my future posts to deliver to your email you can sign up for that on the right side bar.
Posted in Excel
This post will explain all the different count functions in Excel. See graphic below that has a brief description of what each formula does.
For this demo I will use the following data from the screen shot below.
COUNT(A2:C5) would look at the range in blue and return the value 7 since it counts cells that contain numbers. COUNTA(A2:C5) would return 11 because it counts anything with text or numbers. COUNTBLANK(A2:C5) would return 1 because there is a blank cell in the range.
If I wanted to count how many times the word “excellent” is listed I would use the COUNTIF function. This has 2 arguments. The first is the range and the second is the criteria. We would get the value 2 answer.
The last example in this post will look at multiple conditions for the criteria using the COUNTIFS. You use COUNTIFS when you have 2 or more conditions. My example will share counting how many rows of data do we have that have an “excellent” status and the units is greater than or equal to 90. You put “>=90” inside of quotes.There will be 4 arguments. Make sure that each range is the same amount of cells. The answer value returned is 1.
This blog post will explore the Index and Match functions in Excel as part of the lookup and reference function categories. There is nothing wrong with the Vlookup unless you need to find a reference value that is further left than what you fetched (the lookup value). Vlookup only works if your data table has the lookup value in the leftmost column. In my example the country code is to the right of the Country field. Check out my blog post on Vlookup and another video post here if you are not familiar with how to write a Vlookup formula. Below is a caption of the data this post will use. At the end of my blog post, you can download the Excel file yourself to practice along on your own.
In this example we will have Excel tell us what position down is 500 among the Code field. The Match function returns the relative position of an item in an array that matches a specified value in a specified order. Once we have that number, then we can nest the Match function inside of the Index function. Lookup Value: 500 Lookup Array: B2:B18. Use absolute referencing ($B$2:$B$22) or range names when you need to copy the formula. Match Type: Almost always 0 or false because you are looking up an exact match. This argument is similar to the match type argument of the Vlookup.
The answer is 5 because it is the 5th position in the range of codes. Knowing 5 will be important because we can use it (Match value return) nested inside the Index function. The Index function returns a value or a reference of the cell at the intersection of a particular row and column, in a given range. The Index function has two types of function arguments. This post will explore the first type which uses only 2 arguments.
Index Arguments: Array: The range of cells where the return value will be found. Row_Num: A number that you want to go down to find. Col_Num: optional – alternative to the row number.
Use the Match function in the Row_Num argument. Example Using Index and Match together:
Download this practice file here. View my PowerPoint slide deck with other examples of Match and Index here.
If you would like to be notified when I post a new blog entry, you can enter your email in the subscribe box on the right side bar.
I’m excited to share with you a video demo on how I back up and view my pictures using my Microsoft OneDrive account.
Click the Portfolio Keyboard shortcuts link to download my entire collection of keyboard shortcuts.
Please note that you will need an updated version of Adobe flash to view the content. If you can’t get the link to download, then you can alternatively click “shortcuts” on the right side bar category list. I have the same shortcuts listed by separate posts.