Tag Archive: Microsoft Excel

Google Develops Native Editing for Microsoft Word and Excel

This is probably a while away for average users, but it is a very helpful step towards increasing the ease of use and cross-platform compatibility. Some folks, by necessity or preference, live in Microsoft Office and this would make doing that possible with Google’s tools. Chromebooks are looking better everyday.

From The Next Web: Google adds native Microsoft Word and Excel file editing to latest Chrome OS build

Google has added native Microsoft Office file editing to the dev channel for Chrome OS. The addition means Chrome OS users on the latest build of the company’s browser-based operating system can now experiment with editing Microsoft Word and Excel files.

The addition was first noted by developer and Google open-source Chromium evangelist François Beaufort. He points to a Chromium code review that merely states Improved Quickoffice editing about:flag.

Here is a Word document being edited on Chrome OS:

Word in Chrome

Here is an Excel document:

Excel in Chrome

It’s unclear why PowerPoint has not received the same treatment; we can only speculate that Google simply didn’t focus its resources on it as much. Given that this technology is based on the company’s QuickOffice acquisition in June 2012, however, it’s fair to say Word, Excel, and PowerPoint will all be supported in due time.

If you have a Chromebook or other Chrome OS device on the dev channel, you can try this out by doing the following:

1. Navigate to chrome://flags.

2. Click on Enable below the Enable document editing entry.

3. You’ll be prompted to Restart Now after which you will get access to the feature.

In April, Google rolled out a beta release of its new Chrome Office Viewer extension, which lets users view Microsoft Office files directly in the browser on Windows and OS X. The Office Viewer extension was ported straight from Chrome OS, suggesting Google could end up merging it into Chrome altogether one day.

The dev channel for Chrome OS is updated once or twice weekly. Since the feature has made it in there, it’s likely to show up in the beta channel, and then eventually the stable channel.

Yet today’s news that Google is already working on editing Microsoft Office documents in Chrome OS is also very interesting. Maybe by the end of year, it will make it into the Chrome browser too.

Excel: Filter, Sort, and Remove Duplicates

This post will cover three data management techniques of Excel: filter data, sort data, and remove duplicates.

Filter Your Data

Filtering your data will essentially hide all values except the chosen filter criteria. For example, if you want to see all students with the last name as Smith, you can filter for Text Equals Smith.

  1. Select the data that you want to filter. Or press Ctrl+A to select the whole worksheet.
  2. On the Data tab, in the Sort & Filter group, click Filter.

excel1

  1. Click the arrow in the column header to display a list in which you can make filter choices.

excel2To select by values, in the list, clear the (Select All) check box. This removes the check marks from all the check boxes. Then, select only the values you want to see, and click OK to see the results.

Sort Your Data

To Sort or rearrange your data for organizational purposes, do the following:

  1. Select a range of data. Or press Ctrl+A to select the whole worksheet.
  2. On the Data tab, in the Sort & Filter group, click to perform an ascending sort (A to Z or smallest number to largest). Or click to perform a descending sort (Z to A or largest number to smallest).

excel1

Sort Warning- you will want to select Expand the Selection in order to keep the data in the adjacent columns paired with the first column during the sort.

MORE OPTIONS-

To sort by specific criteria, do the following:

  1. Select a single cell anywhere in the range that you want to sort.
  2. On the Data tab, in the Sort & Filter group, click Sort. (The Sort dialog box appears.)

excel3

  1. In the Sort by list, select the first column on which you want to sort.
  2. In the Sort On list, select either Values, Cell Color, Font Color, or Cell Icon.
  3. In the Order list, select the order that you want to apply to the sort operation.
  4. To conduct a Multiple Sort (for example, first you Sort Last Names by Alphabetical Order, but then you want to sort GPAs by ranking number) Click Add Level and repeat steps 3-5.
  5. Click OK

Remove Duplicate Entries is handy if you know you have duplicate data entries, but don’t want to remove them by hand.

Note- Data entries must match completely for the Remove Duplicates function to work properly. Any different spaces or punctuation between duplicate data entries will give the appearance that the entries are unique entries and therefore, will not be removed.

  1. Select the data that you want to work with. Or press Ctrl+A to select the whole worksheet.
  2. On the Data tab, in the Data Tools group, click Remove Duplicates.excel4
  3. In the Remove Duplicates dialog box, make sure all columns are selected and check mark My data has headers. Click OK.excel5
  4. A confirmation will appear, alerting you to how many duplicates were removed.

View information on how to create header rows, format with every other row shadowed, and other duplicate formatted cells in Excel.

Excel: Shading Every Other Row

If you have a lot of data in your Excel workbook and would like to create a visually appealing way to read the data, try shading every other row with Format as Table.

To use the Format as Table Styles option to shade rows in a worksheet, follow these steps:

  1. Select the cell range that you want to shade, or press Ctrl+A to select the whole worksheet.
  2. Click the Home tab.In the Styles group, click Format as Table
  3. Select your preferred style.

excel3

  1. In the Format as Table dialogue box, verify the cells you want changed (this will be automatically filled when you complete step 1). Check mark My table has headers if you created a Header Row. Click OK.

excel4

  1. If you have a large amount of data selected, Excel will ask you if you want to continue with the Large Operation. Click OK.excel5

The result:

excel6

View information on how to create Header Rows in Excel or Filter, Sort, and Remove Duplicate Data.

Excel Header Row

If using Excel to maintain large amounts of data, it is recommended that you use a Header Row to label each column of data. You can then freeze the pane to keep this Header visible no matter how much scrolling you do in the Excel Worksheet.

  1. Create a new Row with your heading information. Or identify the portion of the spreadsheet that will be used as a head row.
  2. Select the cells that make up your header row by clicking on the Row Number. It will highlight blue.
  3. excel1Optional- Create a visual contrast for this row by formatting the font in the Home Tab.
  4. Click the Row Number for the next row AFTER your Header Row. It will highlight blue.
  5. Click tab View, in Window, click Freeze Panes. Select Freeze Panes.excel2 View information on how to create Shade Every Other Row in Excel or Filter, Sort, and Remove Duplicate Data.

Excel: The Basics

MS Excel IconToday, I taught a Staff Learning Session on the topic of Excel: The Basics at Pepperdine University School of Law. In one hour we covered the following topics:

  • Introduction to the Worksheets/Workbook Area
  • Adding Content
  • Formatting Content
  • Moving/Deleting Content
  • Creating/Using Formulas

It really was just the basics, but definitely a great foundation for anyone interested in learning more about Excel. Next Friday, at our next Staff Learning Session, we hope to cover more advanced Excel concepts and tips/tricks that I have been posting about in this blog as part of the Excel Cheats: What You Didn’t Know! series. We will cover

  • Creating/Formatting Charts & Graphs
  • Sorting Data
  • Printing
  • Managing Data and more!

For those of you that were unable to attend the learning session, or are simply interested in learning a thing or two about Excel, please download the Excel Handout (PDF) below. It includes step-by-step instructions (with pictures) on how to accomplish a variety of tasks listed above.

Excel Basics Handout (PDF)

Are You Missing Out?

It’s hard to keep up with all the new bells and whistles of MS Office, since Microsoft comes out with a new version every two years or so. In this post I’m going to share with you some new updates to MS Office 2010 that you may have overlooked. I hope these will be useful to you in your personal and professional endeavors. Don’t miss out!!

Instant & Easy Screenshots
Did you know that MS Office tools now provide you with a faster way to take a screen shot and paste it into a document, worksheet, or presentation? All three applications (Word, Excel, and PowerPoint) are equipped with the ability to instantly capture screenshots. Simply click on the Insert tab – then select Screenshot (see image below). You have the option to either take a screenshot of your entire screen (an available window) or choose a selection (screen clipping). Once you make the selection, it is automatically added to your document, worksheet, or presentation.

Screenshot in MS OfficeCustomize the Ribbon
Now you can customize each application in order to make them more relevant and appropriate for your unique needs.The ribbon is the menubar that you see across the top of the screen in Word, Excel, and PowerPoint. See image below.

Ribbon in MS Word

There may be items in this ribbon that you use more than others. Or perhaps there are elements that you use that are not easily accessible on the ribbon. To customize click on the File button in the top left-hand corner of the program. Next, select Options, then select Customize Ribbon. Your options will vary depending on what program you are using. Below is an image of the customizable options for Word.

Customizable Ribbon in MS Word

From this area, you can add items on the left side (commands) to the space on the right side (Main Tabs). You can also un-check certain elements on the right side to remove from the current ribbon. And if you make a mistake and un-check something that you do use, you can always come back and check it later.  One thing I would recommend doing is taking a look at the commands that are not on the ribbon. To do so, where it says Choose commands from (on the top left-side of the window), change Popular Commands to Commands Not in the Ribbon.  This will show you some options that you don’t typically see, but may be useful. If you see something you like, click on the Command and then click on the Add button to add it to the Main Tabs. After you are done making changes, click OK at the bottom of the window and you now have your own custom Ribbon. Enjoy!

Excel Cheats: Part Five

As part of our series, Excel Cheats: What You Didn’t Know!, I will now proceed to share a few more tips and tricks about Excel 2010. Specifically, how to display formulas instead of results in your worksheet and how to instantly add the SUM function.

Display Formulas Instead of Results
Have you ever wanted to see what formulas you have used/created in your worksheet but didn’t want to click on each individual cell to view? Now there is a way to instantly view all formulas on your worksheet. Simply hit CTRL then ~ (this tilde symbol should be located in the top-left corner of your keyboard). This will display all formulas. If you want to go back to your results, simply hit CTRL ~ again. See the differences below. Note: Since some cells did not have formulas, but dates and times, those values were converted as well. You can ignore those columns. In the second image below you can see the Total column displays all formulas.

Image of Excel Worksheet with NumbersExcel 2010 Formulas Displayed

You can also accomplish this same task of alternating between results and formulas by going to the Formula tab, and selecting Show Formulas.See below.

How to Show Formulas in Excel 2010

SUM Formula Shortcut
This last trick will show you how to add the SUM formula quickly and easily without having to type in the SUM function in the cell or formula bar. Simply bring your cursor to the cell in which you want to add the SUM function. Then hit ALT +

The SUM function is now added to that cell and you can select the appropriate cells to sum.

Thanks for reading! Hopefully you have learned a thing or two after reading through Part One-Part Five of the Excel Cheats: What You Didn’t Know! Good Luck with Excel!

 

Excel Cheats: Part Four

In this post, Part Four of the series, Excel Cheats: What You Didn’t Know! I will venture into the world of organizing your data. Specifically, changing the background color of alternating rows in your document. This will help you identify important information quickly and easily!

Alternating Shading using Conditional Formatting
You may already know that with Excel, you can change the style of your worksheet to apply shading to alternate rows. This is often the quickest way to do so and can be accessed by highlighting the appropriate rows of content and clicking on the Format as Table option in the menu bar. See image below.

Excel MenubarHowever, in this post I will focus on applying shading to alternate rows when you use conditional formatting. This will only allow you to select one color for shading, not both colors.

  1. First, highlight/select the cells that you wish to format, or if you want to apply shading to the whole worksheet click the Select All button in the top-left corner.
  2. Select All Button in ExcelNext, click on Conditional Formatting from the menu bar, and then click New Rule. Excel Menu Bar: Conditional Formatting
  3. In the New Formatting Rule window, under Select A Rule Type, click Use a formula to determine which cells to format.
  4. In the Format values where this formula is true box, enter =MOD(ROW(),2)=0
  5. If you want to apply shading to alternate columns, instead enter =MOD(COLUMN(),2)=0
  6. Click Format. New Formatting Rule Box in Excel
  7. In the Format Cells box, click the Fill tab
  8. Select the background color that you want to use for the shaded rows, and then click OK.   Select Background Color in Excel
  9. You should now be able to see the color you selected show up in the Preview window in the New Formatting Rule box.
  10. Click OK.

Your Excel worksheet should now be formatted appropriately!
Alternate Shading in ExcelNote: If you want to make changes at some point, click one of the cells that contains the shading, click Conditional Formatting on the menu bar and then click Manage Rules.

Thanks for learning more about Excel in Part Four of the Excel series. I hope you found this cheat useful for your present and future endeavors!

Excel Cheats: Part Three

Welcome to Part Three of the series Excel Cheats: What You Didn’t Know! In this post I will show you how to quickly and effortlessly eliminate duplicate items from your worksheet. Let’s Begin!

Removing Duplicate Items
Let’s say you have a class roster. This class roster contains both students’ first and last names in separate columns. You know that there are duplicate items in your list but it is too long for you to manually go through and remove duplicates. Excel 2010 makes this process quick and easy! Go to the Data tab, and select Remove Duplicates from the Data Tools section.

Remove Duplicates in Excel 2010Next, a window will pop with a variety of options. In my example you can see that the box next to My data has headers is checked (see image below). This is because I have a row of headers: Last Name, First Name, Gender, and Age, that should not be removed. By letting Excel know that your first row is headers, it is also easy to identify which columns you want to check/remove duplicates.If your worksheet does not have headers, then you can remove the check mark from this box and column letters will be used instead for identification purposes.

Remove Duplicates OptionsIn this example, I will click on the boxes next to Last Name and First Name. This is because I am only looking for duplicates among those items. Also, since it is possible that more than one student may have the same last name or the same first name, I DON’T want to just search for duplicates by last name or just by first name. Checking off both will ensure that you only remove duplicates if BOTH the first and last name are the same. Click OK when you are done selecting the appropriate columns.

Remove Duplicates MessageYou should receive a message letting you know how many duplicate items were removed. You now have a list without duplicate items! A Final Thought: It is always a good idea to save both versions of your document. The first version (without duplicate items removed) and the second version (with duplicate items removed). This way you don’t lose any important data and can always go back to the original file at any time. Good Luck!

Excel Cheats: Part Two

This post is Part Two of a series on Excel Cheats: What You Didn’t Know! Today I will show you how to use the AutoFilter feature, which will allow you to easily search and organize information (columns) in your spreadsheet. First, what is an AutoFilter? In the image below, you can see that an AutoFilter inserts a drop-down arrow in the cell and allows you to organize the contents of that column in a variety of ways (i.e., Ascending, Descending, Top 10, etc.). This is a great feature if you want to easily sort or identify data in a specific column. The AutoFilter function also allows you to specify which rows you want displayed, the other rows in the worksheet will be hidden.

Example of AutoFilter in Excel 2010

To Add a Filter to a Column Header
Highlight the column that you would like to filter. Click on the Data tab, then in the Sort & Filter section, click Filter. If your worksheet has multiple columns and you would like all header columns (on the same row) to include an AutoFilter, then bring your cursor to one of the header cells before you click Filter. Excel should automatically find and recognize all of your header columns and add an AutoFilter for you.

Data Tab in Excel 2010

Once you use the drop-down arrow on a particular column to filter that selection, you will notice that the box including the drop-down arrow changes to a drop-down arrow and a symbol of the type of filter that you created. See below: the boxes that are highlighted black have been filtered and you can see that something else is included besides the drop-down arrow. This lets you know that you have filtered these specific columns.

AutoFilter On

To Turn AutoFilter Off: Simply go back to the Data tab and click Filter again in the Sort & Filter section. This will turn off and remove all AutoFilters for that worksheet. Check back again soon for the next Excel Cheat tip!