Advanced spreadsheet tips and tricks

Like a lot of the things you’re learning in this program, spreadsheets will get easier the more you practice. This reading provides you with a list of resources that may help advance your knowledge and experience with spreadsheet functions and functionality. The goal is to provide you with access to a variety of advanced tips and tricks that will help make you more efficient and effective when working with spreadsheets to perform data analysis. Review the description of each resource below, click the links to learn more, and save or bookmark any links that are useful to you. You can immediately start practicing anything that you learn to increase the chances of your understanding and to build your familiarity with spreadsheets. This reading provides a range of resources, so feel free to explore the ones that are applicable to you and skip the ones that aren’t.

Google Sheets

• Keyboard shortcuts for Google Sheets: This is a great resource for quickly learning a range of keyboard shortcuts that can make regular tasks quicker and easier, like navigating your spreadsheet or accessing formulas and functions. This list contains shortcuts for the desktop and mobile versions of Google Sheets so that you can apply them to your work no matter what device you are using.
• List of Google Sheets functions : This is a comprehensive list of the Google Sheets functions and syntax. Each function is listed with a link to learn more.
• 20 Google Sheets Formulas You Must Know: This blog article summarizes and describes 20 of the most useful Google Sheets formulas.
• 18 Google Sheets Formula Tips and Techniques: These are tips for using Google Sheets shortcuts when working with formulas.

Excel

• Keyboard shortcuts in Excel: Earlier in this list, you were provided with a resource for keyboard shortcuts in Google Sheets. Similarly, this resource provides a list of keyboard shortcuts in Excel that will make performing regular spreadsheet tasks more efficient. This includes keyboard shortcuts for both desktop and mobile versions of Excel, so you can apply them no matter what platform you are working on.
• 222 Excel shortcuts: A compilation of shortcuts includes links to more detailed explanations about how to use them. This is a great way to quickly reference keyboard shortcuts. The list has been organized by functionality, so you can go directly to the sections that are most useful to you.
• List of spreadsheet functions: This is a comprehensive list of Excel spreadsheet functions with links to more detailed explanations. This is a useful resource to save so that you can reference it often; that way, you’ll have access to functions and examples that you can apply to your work.
• List of spreadsheet formulas: Similar to the previous resource, this comprehensive list of Excel spreadsheet formulas with links to more detailed explanations and can be saved and referenced any time you need to check out a formula for your analysis.
• Essential Excel Skills for Analyzing Data: This blog post includes more advanced functionalities of some spreadsheet tools that you have previously learned about, like pivot tables and conditional formatting. These skills have been identified as particularly useful for data analysis. Each section includes a how-to video that will take you through the process of using these functions step-by-step, so that you can apply them to your own analysis.
• Advanced Spreadsheet Skills: Mark Jhon C. Oxillo’s presentation starts with a basic overview of spreadsheet but also includes advanced functions and exercises to help you apply formulas to actual data in Excel. This is a great way to review some basic concepts and practice the skills you have been learning so far.

There are lots of resources online about advanced spreadsheet tips and tricks. You'll probably discover new resources and tools on your own, but this list is a great starting point as you become more familiar with spreadsheets.

Self-Reflection: Stack Overflow

Overview

Now that you are learning how to convert and format data for analysis, you can pause for a moment and think about what you are learning. In this self-reflection, you will consider how you can seek help while you learn, then respond to brief questions.

This self-reflection will help you develop insights into your own learning and prepare you to ask the data analytics community on Stack Overflow about what you’re learning. As you answer questions—and come up with questions of your own—you will consider concepts, practices, and principles to help refine your understanding and reinforce your learning. You’ve done the hard work, so make sure to get the most out of it: This reflection will help your knowledge stick!

Seeking help on Stack Overflow

Stack Overflow is an online platform where programmers ask code-related questions and peers are available to suggest answers. You can ask questions about programming languages such as SQL and R (which you will learn about in Course 7), data tools, and much more. Follow the steps below to get started on Stack Overflow.

Sign up for an account

To sign up for Stack Overflow:

1. Click on the Sign up button in the upper right corner

2. Follow the on-screen prompts to enter your desired login information.
3. Click the Sign up button.

Explore Stack Overflow

From the home page, click the dropdown in the upper left corner and click Questions.

The Questions page provides different categories of questions for you to choose. Some examples include the “Newest” and “Active” categories. Read some of the questions under the different categories.

Tags will help you find questions. On the left pane, click on Tags.

On the Tags page, type in a tag name and then press Enter or Return. Next, you can click on a tag to view questions that have that particular tag.

Use the Search bar at the top of the web page to search for keywords and questions. If you would like to view only questions that have a certain tag, include the tag name in brackets with your search. For example, if you want to only find questions that have the tag “SQL,” then type [SQL] in the search field, along with your keywords or question. See the example below.

To learn more about searching, read these instructions about how to search. For a quick guide on syntax structures, check out this list of search types and search syntax.

Write your own question

When asking a question on Stack Overflow, keep it specific. Don’t use Stack Overflow to ask questions with opinion-based answers.

For example, “Which SQL function can I use to add two numbers together?” is an appropriate question. “Which SQL function is your favorite?” is not.

It is a best practice to search the Stack Overflow website for your question in case someone has already asked it. This reduces redundant questions on the site and saves you the time it would take to wait for an answer.

Write clear and concise questions in complete sentences. Then people are more likely to understand what you ask and give you helpful answers.

To begin asking a question, click the blue Ask Question button on this page.

The form for asking a question has three sections: Title, Body, and Tags.

• Title: This is where you ask your question.
• Body: Summarize your problem and include expected and actual results. Include any error codes. If you think that inserting code into the Body section will help, press Ctrl+K (Windows) or Cmd+K (Mac OS) on your keyboard. Then type your code.
• Tags: Tags include specific keywords, like program names. They help other people find your question. You can add up to five tags. Check out this list of existing tags for examples of what tags to use.

Note: Stack Overflow is a public forum. Do not post any confidential company information or code that could impact the company you work for or yourself. When in doubt, first ask your manager whether you may post your question and code excerpt on Stack Overflow.

Reflection

Consider your experiences learning about data analysis and exploring Stack Overflow:

• During this course, when might you have benefited from asking a question on Stack Overflow?
• How would you write your question?

Now, write 2-3 sentences (40-60 words) in response to each of these questions. Type your response in the text box below.

Explain: Great work reinforcing your learning with a thoughtful self-reflection! A good reflection on this topic would describe a time when you could have used support from the data analytics community and include the question you would have shared on Stack Overflow. A meaningful question would concisely summarize your problem and results. It could also include tags with specific keywords.

As you learn more about SQL in this course (and encounter R in the future), Stack Overflow will serve as a valuable resource and help you succeed. Going forward, you can ask questions and find solutions using Stack Overflow. This helpful tool allows you to further your learning and build your coding knowledge throughout your career.